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

Question

To start this off, I am well aware that parameterized queries are the best option, but I am asking what makes the strategy I present below vulnerable. People insist the below solution doesn't work, so I am look for an example of why it wouldn't.

If dynamic SQL is built in code using the following escaping before being sent to a SQL Server, what kind of injection can defeat this?

string userInput= "N'" + userInput.Replace("'", "''") + "'" 

A similar question was answered here, but I don't believe any of the answers are applicable here.

Escaping the single quote with a "\" isn't possible in SQL Server.

I believe SQL Smuggling with Unicode (outlined here) would be thwarted by the fact that the string being produced is marked as Unicode by the N preceding the single quote. As far as I know, there are no other character sets that SQL Server would automatically translate to a single quote. Without an unescaped single quote, I don't believe injection is possible.

I don't believe String Truncation is a viable vector either. SQL Server certainly won't be doing the truncating since the max size for an nvarchar is 2GB according to microsoft. A 2 GB string is unfeasible in most situations, and impossible in mine.

Second Order Injection could be possible, but is it possible if:

1. All data going into the database is sanitized using the above method
2. Values from the database are never appended into dynamic SQL (why would you ever do that anyways, when you can just reference the table value in the static part of any dynamic SQL string?).

I'm not suggesting that this is better than or an alternative to using parameterized queries, but I want to know how what I outlined is vulnerable. Any ideas?

Answer 1

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.