How can sanitation that escapes single quotes be defeated by SQL injection in SQL Server?

by

There are a few cases where this escape function will fail. The most obvious is when a single quote isn’t used:

string table= "\"" + table.Replace("'", "''") + "\""
string var= "`" + var.Replace("'", "''") + "`"
string index= " " + index.Replace("'", "''") + " "
string query = "select * from `"+table+"` where name=\""+var+"\" or id="+index

In this case, you can “break out” using a double-quote, a back-tick. In the last case there is nothing to “break out” of, so you can just write 1 union select password from users-- or whatever sql payload the attacker desires.

The next condition where this escape function will fail is if a sub-string is taken after the string is escaped (and yes I have found vulnerabilities like this in the wild):

string userPassword= userPassword.Replace("'", "''")
string userName= userInput.Replace("'", "''")
userName = substr(userName,0,10)
string query = "select * from users where name=""+userName+"" and password='"+userPassword+"'";

In this case a username of abcdefgji' will be turned into abcdefgji'' by the escape function and then turned back into abcdefgji' by taking the sub-string. This can be exploited by setting the password value to any sql statement, in this case or 1=1-- would be interpreted as sql and the username would be interpreted as abcdefgji'' and password=. The resulting query is as follows:

select * from users where name="abcdefgji"' and password=' or 1=1--

T-SQL and other advanced sql injection techniques where already mentioned. Advanced SQL Injection In SQL Server Applications is a great paper and you should read it if you haven’t already.

The final issue is unicode attacks. This class of vulnerabilities arises because the escape function is not aware of multi-byte encoding, and this can be used by an attacker to “consume” the escape character. Prepending an “N” to the string will not help, as this doesn’t affect the value of multi-byte chars later in the string. However, this type of attack is very uncommon because the database must be configured to accept GBK unicode strings (and I’m not sure that MS-SQL can do this).

Second-Order code injection is still possible, this attack pattern is created by trusting attacker-controlled data sources. Escaping is used to represent control characters as their character literal. If the developer forgets to escape a value obtained from a select and then uses this value in another query then bam the attacker will have a character literal single quote at their disposal.

Test everything, trust nothing.

More Related Contents:

  1. Comma separated results in SQL
  2. T-SQL stored procedure that accepts multiple Id values
  3. Understanding PIVOT function in T-SQL
  4. When should I use semicolons in SQL Server?
  5. How do I escape a percentage sign in T-SQL?
  6. Using RegEx in SQL Server
  7. How should I pass a table name into a stored proc?
  8. Preferred Method of Storing Passwords In Database
  9. How do you count the number of occurrences of a certain substring in a SQL varchar?
  10. Find index of last occurrence of a sub-string using T-SQL
  11. Hidden Features of SQL Server
  12. SQL Server 2008 Empty String vs. Space
  13. How do I exclude Weekend days in a SQL Server query?
  14. EXEC sp_executesql with multiple parameters
  15. SQL Server – Dynamic PIVOT Table – SQL Injection
  16. Preserving ORDER BY in SELECT INTO
  17. Select / Insert version of an Upsert: is there a design pattern for high concurrency?
  18. What is the Oracle equivalent of SQL Server’s IsNull() function?
  19. Can you call a webservice from TSQL code?
  20. How to add custom attributes to SQL connection string?
  21. SQL Recursive CTE: Finding objects linked by property
  22. Multiple Column Pivot in T-SQL
  23. Joining multiple tables returns NULL value
  24. Which join syntax is better?
  25. Compute MD5 hash of a UTF8 string
  26. LPAD in SQL Server 2008
  27. How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
  28. How do I remove non-breaking spaces from a column in SQL server?
  29. What are row, page and table locks? And when they are acquired?
  30. How to cleanse (prevent SQL injection) dynamic SQL in SQL Server?

Leave a Comment