While teaching the defensive web app security classes with SANS, I often hear "I have been filtering/escaping quote character for years to prevent SQL injection, it had worked flawlessly." That's one of the common statement I get when I sell the idea of parameterized queries. We know by now that filtering single quote does not prevent all SQL injection, but how big is the risk?

I have been doing some SQL injection research with the fine folks from Security Compass on MS SQL server. Depending on your setup, you might be more vulnerable than you think. What characters do people normally filter or escape for preventing SQL injection?  Maybe quote and semi-colon?  Bad news, depending on your setup, you maybe very vulnerable even after filtering those characters.

Semicolon Not needed

Let's get some background information first. MS SQL server support query piggybacking by default, so you can execute multiple SQL statements in one single communication with the SQL server. While it is widely believed that you need a semi-colon between different logical statement while piggybacking, it is really not necessary.

Here is an example,

select * from product; select * from employee

But actually, the following version without semi-colon works too

select * from product  select * from employee

Or  even this

select 1 select 2

No semi-colon is required to delimit each statement. With Microsoft documentation, I have found that up to MS SQL 2008, semi-colon is not necessary but this will change in future version of MS SQL server

So, filtering semi-colon may not provide the protection you wanted.

Quote Not needed

Now, what about quote? This one is easy and is well publicized by other researchers such as "Advanced SQL Injection" paper by Chris Anley (back in 2002).

You simply do not need quote to SQL inject a numeric type of input, because quotes are not used for numeric type in SQL statement.

Let's look at an example of a textbook SQL injection vulnerability, assuming qty is a numeric type in the database

select * from product where qty = <USER INPUT>

An attacker can simply put in 1 or 1 = 1 and return all data. Quotes needed? Nope.

To leverage both techniques mentioned about, what an attacker might be able to do is

select * from product where qty = 1 shutdown

An instant DoS attack with the DB server shutdown, if the web app user is running as "sa".

select * from product where qty = 1 delete from product where qty = 1

Did I use quote? No....  Did I use semi-colon? No...

For some of the advanced reader, you must be saying, "Hey! There are tons more evil attacks possible with this" Sure, but ISC isn't trying to teach you how to hack (SANS has 538 and 542 on how to pentest web apps). The basics that everyone need to understand is - if you can run a full SQL statement, the possible damage is a lot higher. An attacker does not always need quote and semi-colon to run a separate SQL statement.

Now, the best fix for SQL injection is still parameterizing your SQL queries. If you still want to filter SQL characters or keyword, that's up to you, but remember that there's a good chance you will get hacked.
 

------------------------------------------
Jason Lam, author of SANS web app courses - 319, 422, 538