Home > security > Not the average SQL Injection

Not the average SQL Injection

July 19th, 2009

SQL Injections are one of the most common and most critical Web application vulnerabilities that can be identified during a Web Application Penetration Test. SQL injections can occur in any part of a SQL query, but they usually occur in the “where_definition” section, to clarify what I’m talking about, here’s the syntax definition for the SELECT statement for MySQL:

SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr, ...
[INTO OUTFILE 'file_name' export_options
| INTO DUMPFILE 'file_name']
[FROM table_references
[WHERE where_definition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_definition]
[ORDER BY {col_name | expr | position}
[ASC | DESC] , ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[FOR UPDATE | LOCK IN SHARE MODE]]

While performing some vulnerability research in e-commerce Web applications, Ryan Dewhurst ([email protected]) found a rather uncommon SQL Injection vector in the “col_name” section of the SELECT query, which at first looks like the average SQL Injection, but when actually trying to exploit it we discovered that it was more difficult than expected. This is the code snippet for the vulnerability, were we only control the $sort_order value:

$userlog = db_get_array("SELECT change_id, action, timestamp,
                         amount, reason FROM points_table
                         WHERE user_id = ?i ORDER BY $sort_by
                         $sort_order $limit", $user_id);

First of all, no SQL Injection tool works with this type of SQL injection. The reason is that they all assume that they are going to be injecting in the where_definition part of the query, and in this case that assumption is false. So we went back to the SELECT syntax, and we found that we had not much space to play:

[ORDER BY {col_name | expr | position}
[ASC | DESC] , ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[FOR UPDATE | LOCK IN SHARE MODE]]

The first idea was to use a UNION clause in order to join two different queries, the one controlled by the web application, and the other controlled by us; which seemed to be a good idea, but a flawed one also:

  • select 1,2,3 union select 4,5,6 Works perfectly.
  • select 1,2,3 order by 1 ASC union select 4,5,6 Throws an "Incorrect usage of UNION and ORDER BY error."
  • (select 1,2,3 order by 1 ASC) union (select 4,5,6) Works perfectly, but we can't add the "(" at the beginning of the query.

UNION was out of the picture.

Without even noticing that I was testing something syntactically incorrect, I tried to write a file to disk using “INTO OUTFILE”, and surprisingly enough, it worked. But the problem is that we can’t control the contents of the file, because in this particular Web application we had no control of the contents of the points_table, so once again we’re were we started because we can’t write a PHP shell to disk.

So the next step was to find a way to execute a SELECT statement after an ORDER BY, the best thing that we could find was to inject a sub-SELECT statement in the col_name section of the order by. Finally the SQL injection ended up like this:

,(SELECT BENCHMARK(1000000,MD5(1)) FROM points_table where
CURRENT_USER() like '[email protected]' limit 1)

Which makes,

SELECT change_id, action, timestamp, amount, reason FROM
       points_table WHERE user_id = i ORDER BY timestamp,
       (SELECT BENCHMARK(1000000,MD5(1)) FROM points_table
        where CURRENT_USER() like '[email protected]' limit 1)

The only problem is that this injection only works if the first SELECT statement actually has more than one row to order (this is because of MySQL performance enhancements). So to be able to exploit this particular SQL injection, we had to buy two items from the e-commerce store in order to add a couple of rows to the points_table, which would then trigger the sub-select in the order by section of the query.

The above injection can be modified to perform almost any query to the database, which leads to total e-commerce web application compromise.

The conclusion is simple: while automated tools can help is in many cases, an experienced security professional can never be replaced.

andres.riancho security ,

  1. July 20th, 2009 at 11:05 | #1

    I described this back in March ‘09, http://www.slideshare.net/inquis/sql-injection-not-only-and-11 ;)

  2. July 20th, 2009 at 11:32 | #2

    @Bernardo
    I totally forgot about those slides! You’re right, the slides 25 and 26 point this subject.

    I did all the testing to find a way to inject into ORDER BY without knowing about that previous research. I just hope my post helps somebody that finds it through Google while trying to do the same thing.

  3. July 23rd, 2009 at 01:17 | #3

    I would have loved to have known this as well when I worked through a similar injection technique in March ‘09 when working with OpenCart. I learned that subqueries are your friend. Great information both of you.

    Reference:
    http://www.ngenuity.org/wordpress/2009/05/12/ngenuity-2009-005-opencart-re-visited-exploit-included/
    http://www.ngenuity.org/wordpress/2009/03/10/ngenuity-2009-005-opencart-order-by-blind-sql-injection/

  1. No trackbacks yet.