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