Home > PHP Security > PHP Security – Escape proof SQL injection in ORDER BY clause

PHP Security – Escape proof SQL injection in ORDER BY clause

http://xkcd.com/327/

http://xkcd.com/327/

It’s a well known, well documented, and well abused fact that SQL injection attacks can take place in the WHERE clause of a SQL statement. The commonly applied practice among professionals is to run user input through mysql(i)_real_escape_string(). However, this only protects against user variables within quoted values, and does not protect against SQL injection attacks elsewhere in the query.

One place that is commonly vulnerable is in the ORDER BY clause. Many developers either do not understand that mysql(i)_real_escape_string does not protect them from these types of attacks, or do not think that meaningful SQL injection can be done at this point in the query on a single statement engine like MySQL. As a result, this vulnerability can be found and exploited in many applications and websites, both commercial and open source, personal and corporate.

Vulnerable code and SQL queries is basically:

<php
 
$sortColumn = mysqli_real_escape_string($_GET['sort_column']);
$query ="SELECT * from some_table WHERE active = true ORDER BY $sortColumn DESC";
 
?>

This is vulnerable to a SQL injection attack that will allow a hacker to get information from any table in the database, whether it’s usernames, passwords, credit card account numbers, etc.
 

How this can be exploited
 

The core theory behind the exploit is that this vulnerable query allow you to test a tiny piece of information from anywhere in the database in a boolean query that doesn’t rely on any unescaped characters, then use the value of that boolean to visibly change the output of the query.

Assume that the vulnerable site is a news site and lets you sort the article listings by the date or title column.  When you click on the column header you want to sort by, it sends a ‘sort_column’ parameter to the above script of either ‘date’ or ‘title’.  

If instead of sending ‘date’ or ‘title’, you sent something like the following string, you would be able to start reading information from anywhere in the database.  In this particular case, we’ll try the users table.

(CASE WHEN (SELECT ASCII(SUBSTRING(password, 1, 1)) FROM users where username = 0x61646D696E) = 65 THEN date ELSE title END)

Assuming that this is the correct table and column names, this injection will allow you to tell whether or not the first character of the admin user’s password is ‘A’. If it is, the article list will be returned sorted by date.  If not, it will be returned sorted by title.

If it isn’t a match, then the 65 in the query just needs to be incremented/decremented until the match is made to try other various letters/symbols. Once the match is made and the first character is discovered, the substring offset just needs to be incremented to get the second character, but this time starting with null to see if the end of the string has already been reached. If not, start back at 65, and repeat the process until null matches.

This does require some knowledge about the database schema, which can be guessed, looked up on open source applications, or can be learned by first querying against a known table like the information schema.

A script can be written to do automate this process very quickly, as an 8 character password with upper and lowercase letters and numbers can be discovered with a maximum of 500 queries. MD5 encoded passwords will have the hashes revealed in less than 512 queries, which can then be brute force decoded (at over 500 million attempts/second, thanks to GPU computing), or directly looked up if the password is a common word or phrase.
 

Why This Works

Because each of these queries puts user input in a place in the query that is not enclosed with ‘, there is no need to use any of the characters that would be escaped by mysql(i)_real_escape_string(). Instead, SQL can be directly passed directly into the query. In places that strings are normally used when making a query, Hex notation, ASCII or other character conversion can be used to convert strings to or from their numeric values. As demonstrated in these examples, anywhere that SQL can be injected into a query is a major security vulnerability.
 

How to Secure

Securing this type of query is a rather simple process.  If a column name is expected, the user input should be validated against a whitelist array. 

Applying this on the example query:

<php
$columns = array(
'title',
'date'
);
if (in_array($_GET['sort_column'], $columns)) {
$sortColumn = $_GET['sort_column'];
} else {
$sortColumn = 'title';
}

?>

As you can see, the above code will ensure that only expected/allowed values make it through to the database. So remember, trust no one, and sanitize everything, regardless of how harmless you may think invalid input will be.

PHP Security

  1. mol
    July 17th, 2009 at 08:05 | #1

    this is really interesting, especially the fact that people think a mysql_real_escape_string() is enough to protect them against any attack. The exploit isnt trivial though.

  2. Henry
    November 16th, 2009 at 10:53 | #2

    Thank you for this. It really gave something to think about.

  3. Jacco van Tuijl
    May 29th, 2010 at 22:56 | #3

    eXploiting SQL injection in ORDER BY clause (MySQL 5)
    by Jacco van Tuijl

    This URL will show a list orderd by column 1 :

    http://www.test.com/list.php?orderby=1

    This is what the SQL query that is executed on the database might look like:
    SELECT id,name,price FROM list ORDER BY 1

    If it would be vulnerable to SQL injection we could try :

    http://www.test.com/list.php?orderby=if(true,id,price)

    and

    http://www.test.com/list.php?orderby=if(false,id,price)

    to see if they give a different result

    or

    http://www.test.com/list.php?orderby=(select case when (true) then id else price end)
    and
    http://www.test.com/list.php?orderby=(select case when (true) then id else price end)
    to see if they give a different result.

    If they do give a different result you might be able to enumerate the first char of the table_name in information_schema.tables like this:
    http://www.test.com/list.php?orderby=if((select char(substring(table_name,1,1)) from information_schema.tables limit 1)<=128),id,price)
    and this:
    http://www.test.com/list.php?orderby=(select case when ((select char(substring(table_name,1,1)) from information_schema.tables limit 1)<=128) then id else price end)

    The backside of these methods is that they require knowlage of the column names.
    So I worked out some different method that doesn't require knowlage about column names.

    ORDER BY rand()

    We can make a request like this:

    http://www.test.com/list.php?orderby=rand(true)

    returns a different result then this request:

    http://www.test.com/list.php?orderby=rand(false)

    We can use it to enumerate the first char of the table_name in information_schema.tables like this:
    http://www.test.com/list.php?orderby=rand((select char(substring(table_name,1,1)) from information_schema.tables limit 1)<=128))

    And it is all quoteless!

    Greetingz,
    Jacco van Tuijl

  4. June 12th, 2010 at 03:01 | #4

    I love it!

  1. No trackbacks yet.