How do I prevent this type of SQL injection attack?

Question

Our company has been using several tool (Veracode, Appscan, etc) to verify that our applications meet minimum security requirements. I have inherited a legacy application which has several flaws. I've managed to mitigate most of the vulnerabilities, but am having troubles fixing this next one because I simply do not know what it is doing. In our report, it seems that it is changing one of our POST variables to

25%27+having+1%3D1--+

What is this and what is it effectively doing? What can I do to prevented by this type of attack?

Normally, I would, but this is a very old application that we do not plan on having around much longer. Time I spend on this application is basically a waste of time at this point, so the bare minimum work possible is desired. — Jeff, Dec 11 '12 at 18:22
If you want the least amount of work to resolve this (or any) vulnerability, toggle the system's oh-enn/oh-eff-eff discriminator. — Iszi, Dec 11 '12 at 18:36

score 26 · Accepted Answer · edited Dec 12 '12 at 01:46

26

When this string is decoded from its url-encoded form it becomes the following:

25' having 1=1--

This string, when placed as is into, for example, the following (PHP) database query function:

mysql_query("SELECT * FROM users WHERE username = '$username'");

Becomes this:

mysql_query("SELECT * FROM users WHERE username = '25' having 1=1--'");

Note here that the %27 (') breaks out of the argument in the WHERE clause and continues the executable part of the statement. The -- after 1=1 makes the rest of the statement a comment which is not executed.

The HAVING statement in SQL is supposed to be used within queries which use the GROUP BY operator, and should fail in queries which do not. My guess here is that this string is being used to check simply for the presence of an unsanitised variable which gets placed into an executed query.

To prevent this type of attack I would suggest using a good input sanitation function, or parameterised queries. The implementation of this depends on the programming environment in question.

Addition: The normal use of 1=1 in SQL injection queries is to cause all rows to be returned, voiding any other WHERE conditions. An example input could be:

a' OR 1=1 --

When inserted as the $password parameter in a query such as:

SELECT * FROM users WHERE username = '$username' AND password = '$password'

The statement becomes:

SELECT * FROM users WHERE username = 'mark' AND password = 'a' OR 1=1 --

The resulting dataset will include all entries in the 'users' table, as 1=1 is always true.

edited Dec 12 '12 at 01:46

zzzzBov

135
8

answered Dec 11 '12 at 17:08

mckiethanks

556
3
12

Great, so what causes `%27` to be evaluated to `'`? What encoding is this and are there other encodings that I should be aware of? – Jeff Dec 11 '12 at 17:18
3

This is called URL encoding (or apparently [Percent Encoding](http://en.wikipedia.org/wiki/Percent-encoding)). It is used so that characters which would normally interfere with the address bar (?, /, etc) can be placed into URLs without being interpreted for their normal meaning. – mckiethanks Dec 11 '12 at 17:27
@Jeff, the issue isn't really related to the encoding, rather it has to do with the fact the (decoded) parameter contains a quote, and isn't escaped. – Bruno Dec 11 '12 at 18:43
Bound parameters aka parameterized query is the optimal solution. Its risky to trust any input sanitation you wrote yourself, as that's rolling your own security which is a bad idea and a clever attacker may be able to get through. Also bound parameters generally run faster, unless some queries benefit greatly from a different execution plan that a smart DB can figure out without bound parameters [[1]](http://use-the-index-luke.com/sql/where-clause/bind-parameters). If thats the case, make sure your input sanitation just whitelists safe input; e.g., filters out chars not in `[A-Za-z0-9 ]`. – dr jimbob Dec 11 '12 at 19:31
3

@drjimbob Hrrrrrrk, I wouldn't ever trust that regex. For a start, what about cases where you _want_ to store content that might have apostrophes and other punctuation? Even if you're just sticking to alphanumeric content, you can still alter the behaviour of a query via injection of unexpected content, causing subtle potentially-exploitable bugs. Parameterised queries are the **only** proper solution, since they treat data as data and query language as query language; they're separate entitites entirely. If your DBMS or driver can't handle them, switch to one that can immediately. – Polynomial Dec 11 '12 at 19:54
@Polynomial - I recommended bound parameters and in practice never encountered the situation where the pre-planned execution plan create a bottleneck that had to be fixed. Yes, my regex wouldn't let you search for `J.J.`, `Green-Ellis`, `O'Neill` or `Erdős` and coupled with bad SQL strings could fail (`SELECT * from mytable where name={user_input};` with no quotes around `'{user_input}'`). But a simple let's replace `'` with `\'` may fail for complicated reasons (e.g., unicode characters got mapped to `'` or you didn't map `\` to `\\`) or they found some way to bypass your input sanitation. – dr jimbob Dec 11 '12 at 21:22

score 6 · Answer 2 · answered Dec 11 '12 at 17:08

what you're seeing there is a fairly standard SQL Injection attack vector. The code it's adding can modify SQL statements if the input isn't handled correctly by the application (but I guess you worked that out from the title). There's a good description of the problem which mentions this vector in this paper from NGS/NCC.

In terms of mitigating the problem you'll need to ensure that input to the application is appropriately validated or escaped so that it can't modify the underlying SQL queries to the database.

A good place to start with this would be the OWASP SQL Injection Cheetsheet

score 1 · Answer 3 · edited Mar 17 '17 at 13:14

The alternative to a parameterized query is to escape the input. How you do this correctly will depend on your platform and database. For example, on PHP and with the mysql database use mysql_real_escape_string(). Also, if you do this you must always use single quotes in the SQL, even for numbers.

You might think that you can just replace single quotes in a string to make it safe, but that is not sufficient. Single quotes can be represented in other ways. More detail here: Multibyte character exploits - PHP/MySQL

How do I prevent this type of SQL injection attack?

3 Answers3