How to make MS SQL stored procedure that is protected from SQL Injection

Hello everybody,

today I want to write a simple note on how to make stored procedures in MS SQL server which are protected from SQL injections. 

Below goes example of SQL stored procedure, that is vulnerable to SQL injection attacks:

-- Bad code, don't use it ever
CREATE PROCEDURE SearchCustomers
	@searchCust varcahr(50)
AS
BEGIN
	DECLARE @query VARCHAR(100)
	SET @query = 'SELECT * FROM Customer WHERE NAME LIKE ''%' + @searchCust + '%'''
	EXEC (@query)
END

Why it is bad? Because if somebody passes into @searchCust value or 1=1 --- then SQL will return all customers. If to add a bit more creativity it's possible to get from that database plenty of information.

If you wonder, what are safe ways of usage dynamic queries, take a look at another example:

-- Much more protected, you can use it
CREATE PROCEDURE SearchCustomers
	@searchCust varcahr(50)
AS
BEGIN
	DECLARE @query NVARCHAR(100)
	SET @query = 'SELECT * FROM Customer WHERE NAME LIKE ''%'' + @searchCust + '%''''
	EXEC sp_executesql @query, N'@searchCust varchar(50)', @searchCust = @searchCust
END

In this case passed parameter will be executed as part of stored procedure.

Also for those who are curious about tools that can automate SQL injection inspection, consider tool Havij SQL Injection

No Comments

Add a Comment