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

Question

To get started, I am well aware that parameterized queries are the best option, but I ask what makes the strategy presented below vulnerable. People insist that the following solution does not work, so I am looking for an example of why this is not so.

If dynamic SQL is built in code using the following escaping before shipping to SQL Server, what injection can defeat this?

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

A similar question was answered here , but I do not believe that any of the answers are applicable here.

In SQL Server, it is not possible to execute a single quote using "\".

I believe that Unicode SQL smuggling (described here ) would be thwarted by the fact that the string being created is marked as Unicode N, preceding a single quote. As far as I know, there are no other character sets that SQL Server would automatically translate into a single quote. Without an uninsulated single quote, I don’t think injection is possible.

I do not think String Truncation is also a viable vector. SQL Server, of course, will not truncate since the maximum size for nvarchar is 2 GB according to Microsoft . A 2 GB line is unsafe in most situations and impossible in mine.

An invasion of the second order may be possible, but is it possible if:

1. All data entering the database is sanitized using the above method.
2. Values ​​from the database are never added to dynamic SQL (why would you even do this if you can just refer to the table value in the static part of any dynamic SQL row?).

I do not suggest that this be a better or alternative to using parameterized queries, but I want to know how what I have outlined is vulnerable. Any ideas?

Answer 1

There are several cases where this escape function does not work. The most obvious is that a single quote is not 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 the double quotation mark, the reverse mark. In the latter case, there is nothing to "break out", so you can simply write 1 union select password from users-- or any sql payload that the attacker wants.

The following condition, in which this escape function fails, is if the substring is taken after the string has been escaped (and yes, I discovered 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, the username abcdefgji' will be passed to the function abcdefgji'' by the escape function, and then return to abcdefgji' , taking the substring. This can be used by setting a password value for any sql statement, in which case or 1=1-- will be interpreted as sql, and the username will be interpreted as abcdefgji'' and password= . The final query is as follows:

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

T-SQL and other advanced sql injection methods that have already been mentioned. Extended SQL injection in SQL Server applications is a great article, and you should read it if you haven't already.

The final problem is Unicode attacks. This class of vulnerabilities arises because the escape function does not know about encoding with a mulit byte, and it can be used by an attacker to "destroy" the escape character . Turning "N" into a string will not help, since it does not affect the value of multibyte characters later in the string. However, this type of attack is very unusual because the database must be configured to accept unicode GBK strings (and I'm not sure if MS-SQL can do this).

Second-order code entry is still possible; this attack pattern is created by checking the data sources controlled by the attacker. Escaping is used to represent control characters as a character literal. If the developer forgets to avoid the value obtained with select and then uses this value in another request, then bam , the attacker will have a personal letter with the letter at their disposal.

Check everything, do not believe anything.

Answer 2

With some additional conditions, your approach above is not vulnerable to SQL injection. The main attack vector to consider is SQL smuggling. SQL smuggling occurs when similar Unicode characters are translated in unexpected ways (for example, "go to"). There are several places where the application stack may be vulnerable to SQL smuggling.

• Does the programming language match Unicode characters? If the language is not supported in Unicode, it may incorrectly identify the byte in the Unicode character as a single quote and avoid it.

• Does the client database library (like ODBC, etc.) support unicode strings? System.Data.SqlClient in the .Net framework, but what about old libraries from windows of the 95th era? Third-party ODBC libraries do exist. What happens if the ODBC driver does not support unicode in the query string?

• Is the database processed correctly? Modern versions of SQL are immune to the assumption that you are using N '', but what about SQL 6.5? SQL 7.0? I do not know about any special vulnerabilities, however this was not on the radar for developers in the 1990s.

• Buffer overflow? Another problem is that the quoted string is longer than the original string. In which version of Sql Server was a 2 GB limit for input introduced? Was there a limit to this? In older versions of SQL, what happened when the query exceeded the limit? Are there any restrictions on the length of the request from the point of view of the network library? Or the length of the string in a programming language?

• Are there any language settings that affect the comparison used in the Replace () function? .Net always does a binary comparison for the Replace () function. It'll be this way forever? What happens if a future version of .NET supports overriding this behavior at the app.config level? What if we used regexp instead of Replace () to insert a single quote? Are the computer locale settings configured for this comparison? If a change in behavior really happened, it may not be vulnerable to SQL injection, however, it may accidentally change the string by changing the Unicode character, which looked like one quote in one quote before it ever reaches the database.

So, suppose you use the System.String.Replace () function in C # in the current .Net version with the built-in SqlClient library against the current (SQL Server) version of SQL Server, then your approach is not vulnerable. When you start to change things, then promises cannot be made. A parameterized query approach is the right approach to efficiency, performance, and (in some cases) security.

WARNING The above comments do not confirm this technique. There are several other very good reasons why this is the wrong approach to SQL generation. However, detailing them is beyond the scope of this question.

DO NOT USE THIS TECHNIQUE FOR NEW DEVELOPMENT.

DO NOT USE THIS TECHNIQUE FOR NEW DEVELOPMENT.

DO NOT USE THIS TECHNIQUE FOR NEW DEVELOPMENT.

Answer 3

Using query parameters is better, easier, and faster than escaping quotes.

---

Repeat your comment, I see that you have confirmed the parameterization, but it deserves attention. Why do you want to use escaping when you can parameterize?

In Extended SQL Injection In SQL Server applications, search for the word “replace” in the text and from now on read a few examples where developers inadvertently allowed SQL injection attacks even after exiting user input.

---

There is an extreme case where escaping quotes with \ leads to vulnerability because \ becomes half the legal multibyte character in some character sets. But this does not apply to your case, since \ not a escape character.

As others have pointed out, you can also add dynamic content to your SQL for something other than a string literal or date literal. Table or column identifiers are separated by " in SQL or [ ] in Microsoft / Sybase. SQL words, of course, have no delimiters. For these cases, I recommend assigning values ​​for interpolation.

The bottom line is that shielding is an effective defense if you can guarantee that you do it consistently. What’s the risk: one of the developers who develop your application may skip the step and do some line interpolation.

Of course, the same applies to other methods, such as parameterization. They are effective only if you execute them sequentially. But it’s easier for me to use the parameters faster than figuring out the correct type of shielding. And developers are more likely to use a convenient and non-retarding method.

Answer 4

SQL injection occurs if user inputs are interpreted as commands. Here, a command means everything that is not interpreted as a recognized data type .

Now, if you use users that are entered only in data literals, in particular only in string literals, user input will only be interpreted as something other than string data if it can leave a text literal. For a Unicode character string or string literals, its single quote, which contains literal data, and the embedded single quote must be represented by two single quotes.

So, to leave the string literal of the context, you will need to specify a single quotation mark (sic), since two single quotes are interpreted as string literals, and not as a line terminator.

So, if you replace any single quotation mark in the data provided by the user with two single quotation marks, then the user will not be able to leave the context of the string literal.

Answer 5

SQL injection can be done through unicode. If the web application has this URL:

http: // mywebapp / widgets /? Code = ABC

which generates SQL as select * from widgets where Code = 'ABC'

but the hacker introduces this:

http: // mywebapp / widgets /? Code = ABC% CA% BC; drop table widgets -

SQL will look like this: select * from widgets where Code = 'ABC; widget widgets - '

and SQL Server will run two SQL statements. One to make a choice, and one to make a fall. Your code will probably translate the URL% CA% BC with the URLs to unicode U02BC, which is the "apostrophe of modifier letters". The Replace function in .Net will NOT consider this as a single quote. However, Microsoft SQL Server treats it as a separate quote. Here is an example that SQL Injection is likely to enable:

 string badValue = ((char)0x02BC).ToString(); badValue = badValue + ";delete from widgets--"; string sql = "SELECT * FROM WIDGETS WHERE ID=" + badValue.Replace("'","''"); TestTheSQL(sql); 

Answer 6

There is probably no 100% safe way if you are performing string concatenation. What you can do is try to check the data type for each parameter, and if all parameters pass this check, then continue. For example, if your parameter must be of type int, and you get something that cannot be converted to int, then simply reject it.

This does not work if you accept nvarchar options.

As others have already pointed out. The safest way is to use a parameterized query.