PHP Security – Escape proof SQL injection in ORDER BY clause

[Nytro]

[h=2]PHP Security – Escape proof SQL injection in ORDER BY clause[/h]

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.

[h=2]How this can be exploited[/h] 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.

[h=2]Why This Works[/h] 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.

[h=2]How to Secure[/h] 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.

Sursa: Joseph Keeler