How to Protect Against SQL Injection by Escaping Single-Quote and Surrounding User Input With Single-Quotes

Can I protect against SQL injection by escaping single-quote and surrounding user input with single-quotes?

First of all, it's just bad practice. Input validation is always necessary, but it's also always iffy.

Worse yet, blacklist validation is always problematic, it's much better to explicitly and strictly define what values/formats you accept. Admittedly, this is not always possible - but to some extent it must always be done.

Some research papers on the subject:

• http://www.imperva.com/docs/WP_SQL_Injection_Protection_LK.pdf
• http://www.it-docs.net/ddata/4954.pdf (Disclosure, this last one was mine ;) )
• https://www.owasp.org/images/d/d4/OWASP_IL_2007_SQL_Smuggling.pdf (based on the previous paper, which is no longer available)

Point is, any blacklist you do (and too-permissive whitelists) can be bypassed. The last link to my paper shows situations where even quote escaping can be bypassed.

Even if these situations do not apply to you, it's still a bad idea. Moreover, unless your app is trivially small, you're going to have to deal with maintenance, and maybe a certain amount of governance: how do you ensure that its done right, everywhere all the time?

The proper way to do it:

• Whitelist validation: type, length, format or accepted values
• If you want to blacklist, go right ahead. Quote escaping is good, but within context of the other mitigations.
• Use Command and Parameter objects, to preparse and validate
• Call parameterized queries only.
• Better yet, use Stored Procedures exclusively.
• Avoid using dynamic SQL, and dont use string concatenation to build queries.
• If using SPs, you can also limit permissions in the database to executing the needed SPs only, and not access tables directly.
• you can also easily verify that the entire codebase only accesses the DB through SPs...

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

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.

SQL Injection even when escaping quote

Since it sounds like you're already aware of the benefits of prepared/parameterized statements, I won't preach. You seem to just be curious how your existing application could have been breached.

A simple \' ; drop table users -- could beat your quote-doubling. Your CleanSql() function would turn it into:

\'' ; drop table users --

Your SQL statement would become:

... WHERE [Username] = '\'' ; drop table users --'

And since '\'' is a valid value (an escaped single quote), your where clause is effectively ended. ; starts a new command and -- effectively comments out the closing quote. drop table could be anything... update users set password=... or insert into users values () or anything the attacker wants to run.

Is it enough to forbid single quotes in input to avoid SQL injection?

Nope, it is not enough.

Yes, you have to take immediate action and change it to parameter usage where applicable.

Just a few guidelines for you to get it straight:

1. Never take care of any injections. But make sure you have formatted your SQL literals properly. A properly formatted literal is error-proof and - just as a side effect - also invulnerable.
2. Discover the fact that SQL query consists of literals of several different types. Each require distinct formatting, incompatible and useless for all others.
3. Make sure such a formatting applied unconditionally. A prepared statement is the only way to be sure of.

---

Related Topics