Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to cleanse (prevent SQL injection) dynamic SQL in SQL Server?

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?

like image 598
frankadelic Avatar asked Nov 04 '10 23:11

frankadelic


People also ask

How can SQL injection attacks be prevented?

The only sure way to prevent SQL Injection attacks is input validation and parametrized queries including prepared statements. The application code should never use the input directly. The developer must sanitize all input, not only web form inputs such as login forms.

What are three ways to mitigate SQL injection threats?

There are several methods to mitigate the threat of SQL injection such as the use of parameterized database queries, database firewalls, and whitelist input validation.

What are the three ways that dynamic SQL can be executed?

What are the three ways that Dynamic SQL can be executed? Writing a query with parameters. Using EXEC. Using sp_executesql.


2 Answers

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.

like image 199
Gabe Avatar answered Sep 19 '22 13:09

Gabe


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

NO

SQL injection is not called a "Certain Set Of Characters Injection", and for a reason. Filtering out certain character could complicate the particular exploit, but does not prevent SQL injection itself. To exploit an SQL injection one have to write SQL. And SQL is not limited to few special characters.

like image 20
Your Common Sense Avatar answered Sep 16 '22 13:09

Your Common Sense