Error Based SQL Injection - Tricks In The Trade

Trigger an error

In this article I am going to describe some simple tips and tricks, which are useful to find and/or exploit error based on SQL injection. The tips/tricks will be for MySQL and PHP, because these are the most common systems you will encounter.

Detect if database errors are displayed:

Knowing if some database errors are displayed is a really valuable information, because it simplifies the process to detect injection points and exploiting a SQL vulnerability, we will discuss more of it later. But how do you provoke a error, even if everything is escaped correctly? Look for the integers:

example: http://vulnsite.com/news.php?id=1

Let the assume id to be used internally as a integer in a MySQL query. Using testing vectors like id=1' or id=2-1 will not provoke any errors nor does the vector seem to be vulnerable to an injection. To provoke an error you can use the following values for id:
1) ?id=0X01
2) ?id=99999999999999999999999999999999999999999

The first example is a valid integer in PHP but not in MySQL, because of the uppercase X (there are even more difference, check http://php.net/manual/en/language.types.integer.php vs !!!). That's why this value provokes an error in the database.
The second example will be converted by PHP to INF (which is also a integer in PHP , but it is definitely not a valid integer in the MySQL database. As an example, the query will look like this:

SELECT title,text from news where id=INF

By using this method, it is easy to determine if error reporting is enabled. This method will only work if the value is used internally as a integer. It won't provoke a database error if the value is used as a string!

Using error reporting to our advantage:

After getting the information that database errors are displayed, how can we use them for our advantage.
In MySQL, it is not that easy in comparison to other DBMS to extract information via error reports. But there are two methods to do so:

o) UPDATEXML and extractValue
o) insane select statement

Personally, I prefer using UPDATEXML( it is available since MySQL v. 5.1.5). Like its name suggest that it is used to modify a xml fragment, by specifying a XPATH expression.
It has three parameters, the first one is the xml fragment, the second one the XPATH expression and the third one specifies the new fragment which will be inserted. A “normal” example:

SELECT UpdateXML('<a><b>ccc</b><d></d></a>', '/b', '<e>fff</e>')
Output: <a><b>ccc</b><d></d></a>

What do you think will happen, if you specify a illegal xpath expression, like the  @@version?

Lets take a real life example to see what happens. Let us assume that the num parameter in the following url:

http://example.com/author.php?num=2

ends up unescaped in the following query:

SELECT name,date,username from author where number=2

Normally you would try to find the number of columns to construct a valid UNION SELECT. But lets assume none of the data are passed back to the webpage. You would need techniques like time based(sleep) or off-band (DNS etc.) to extract information. If error reporting is enabled, UPDATEXML can shorten this process a lot.
To extract the version of the database, the following value for num would be enough:

http://example.com/author.php?num=2 and UPDATEXML(null,@@version,null)
==>
SELECT name,date,username from author where number=2 and UPDATEXML(null,@@version,null)

This will produce an XPATH Syntax Error: `version´
It is also possible to create a complete select statement:

UPDATEXML(null,(select schema_name from information_schema.schemata),null)

Although UPDATEXML seems like a really awesome function it has a drawback too. It can only extract the last 20 bytes at a time. If you want to extract more bytes and still use error based extraction, you have to use the second method.


The next example will create a query, which will create duplicate entry error. The duplicate entry will be the name of a table:

select 1 from dual where 1=1 AND(SELECT COUNT(*) FROM (SELECT 1 UNION SELECT null UNION SELECT !1)x GROUP BY CONCAT((SELECT table_name FROM information_schema.tables LIMIT 1 OFFSET 1),FLOOR(RAND(0)*2)))

That's all for now, but if you want to read on, here are some interesting links regarding SQL injection:

-) http://websec.ca/kb/sql_injection (← Examples where taken from there, really the best sql injection cheat sheet IMHO)
-) http://www.exploit-db.com/papers/13604/
-) http://websec.wordpress.com/2010/12/04/sqli-filter-evasion-cheat-sheet-mysql/

About The Author

Alex Infuhr is an independent security researcher, His core area of research includes Malware analysis and WAF bypassing.