# How do I prevent this type of SQL injection attack?

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?

• parametrized quires, like every other sql injection ever. – rook Dec 11 '12 at 17:59
• 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

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.

• 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
• This is called URL encoding (or apparently 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]](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
• @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

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

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