How to cleanse dynamic SQL in SQL Server -- prevent SQL injection

Question

We have a ton of SQL Server stored procedures which rely on dynamic SQL.

The parameters to the stored procedure are used in a dynamic SQL statement.

We need a standard validation function inside these stored procedures to validate these parameters and prevent SQL injection.

Assume we have these constraints:

1. We can't rewrite the procedures to not use Dynamic SQL

2. We can't use sp_OACreate etc., to use regular expressions for validation.

3. We can't modify the application which calls the stored procedure to validate the parameters before they are passed to the stored procedure.

Is there a set of characters we can filter out to ensure we are not susceptible to SQL injection?

Answer 1

I believe there are three different cases that you have to worry about:

• strings (anything that requires quotes): '''' + replace(@string, '''', '''''') + ''''
• names (anything where quotes aren't allowed): quotename(@string)
• things that cannot be quoted: this requires whitelisting

Note: Everything in a string variable (char, varchar, nchar, nvarchar, etc.) that comes from user-controlled sources must use one of the above methods. That means that even things you expect to be numbers get quoted if they're stored in string variables.

For more details, see the Microsoft Magazine _{(Obsolete link: 2016-10-19)}.

Here's an example using all three methods:

EXEC 'SELECT * FROM Employee WHERE Salary > ''' +
     REPLACE(@salary, '''', '''''') +   -- replacing quotes even for numeric data
     ''' ORDER BY ' + QUOTENAME(@sort_col) + ' ' +  -- quoting a name
     CASE @sort_dir WHEN 'DESC' THEN 'DESC' END     -- whitelisting

Also note that by doing all the string operations inline in the EXEC statement there is no concern with truncation problems. If you assign the intermediate results to variables, you must make sure that the variables are big enough to hold the results. If you do SET @result = QUOTENAME(@name) you should define @result to hold at least 258 (2 * 128 + 2) characters. If you do SET @result = REPLACE(@str, '''', '''''') you should define @result to be twice the size of @str (assume every character in @str could be a quote). And of course, the string variable holding the final SQL statement must be large enough to hold all the static SQL plus all of the result variables.