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

by

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.

More Related Contents:

  1. T-SQL Dynamic SQL and Temp Tables
  2. T-SQL stored procedure that accepts multiple Id values
  3. In SQL Server, how do I generate a CREATE TABLE statement for a given table?
  4. Select columns from result set of stored procedure
  5. Escape a string in SQL Server so that it is safe to use in LIKE expression
  6. Retrieve column definition for stored procedure result set
  7. Dapper.NET and stored proc with multiple result sets
  8. How can sanitation that escapes single quotes be defeated by SQL injection in SQL Server?
  9. How to list all dates between two dates [duplicate]
  10. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  11. EXEC sp_executesql with multiple parameters
  12. SQL Server – Dynamic PIVOT Table – SQL Injection
  13. Dynamic SQL results into temp table in SQL Stored procedure
  14. How do I search an SQL Server database for a string?
  15. What’s the longest possible worldwide phone number I should consider in SQL varchar(length) for phone
  16. Calling an API from SQL Server stored procedure
  17. Handling date in SQL Server
  18. SQL Server history table – populate through SP or Trigger?
  19. Retrieve data from stored procedure which has multiple result sets
  20. Programmatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?
  21. sql use statement with variable
  22. Stored Procedure and Permissions – Is EXECUTE enough?
  23. Avoid Naming User Stored Procedures SP% or SP_%
  24. Execute stored procedure with an Output parameter?
  25. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  26. Stored procedure – return identity as output parameter or scalar
  27. How to find a text inside SQL Server procedures / triggers?
  28. Python – pyodbc call stored procedure with parameter name
  29. Populate Dataset With Table Names From Stored Procedure
  30. ODBC Call Failed with stored procedure – Pass through query

Leave a Comment