Insight on UNION query SQL injection

[Nytro]

[h=1] Insight on UNION query SQL injection[/h]

Last week I wrote about DBMS fingerprint through inband SQL injection (also known as UNION query SQL injection) assuming that a web application parameter is affected by such type of SQL injection threat. Today I will keep the same scenario to focus on the detection of the inband SQL injection vulnerability, which is a prerequirement to exploit it performing the DBMS fingerprint and any other possible attack.

There are mainly two techniques to detect if the vulnerable URL parameter is affected by an inband SQL injection vulnerability:

• UNION ALL SELECT NULL
  
• ORDER BY
  

UNION ALL SELECT NULL

The trick here is to perform the HTTP request taking advantage of the SQL syntax standard UNION ALL statement. Appending to the vulnerable parameter the UNION ALL SELECT NULL statement N times as long as we do not get any DBMS error messages. Common DBMS error messages when the occurences of NULL differ from the number of table columns are:

MySQL:

The used SELECT statements have a different number of columns

PostgreSQL:

each UNION query must have the same number of columns

Microsoft SQL Server:

All queries in an SQL statement containing a UNION operator must have an equal number of expressions in their target lists

Oracle:

ORA-01789: query block has incorrect number of result columns

In our scenario at first request (with only one NULL) we will get a DBMS error message (similar to one of the aboves) or a blank page (if the administrator configured the web server not to return error messages), so we will request http://example/index.php?id=1 UNION ALL SELECT NULL, NULL and we will get a normal page, without any DBMS error message: the remote SELECT query is on a table with two columns and we can append data through the UNION ALL SELECT statement.

ORDER BY

This technique consists in performing the HTTP request taking advantage of the SQL syntax standard ORDER BY statement.

Appending to the vulnerable parameter the ORDER BY NUM statement incrementing the number NUM as long as we do not get any DBMS error messages. Common DBMS error messages when the number NUM is greather than the number of table columns are:

MySQL:

Unknown column 'NUM' in 'order clause'

PostgreSQL:

ORDER BY position NUM is not in select list

Microsoft SQL Server:

The ORDER BY position number NUM is out of range of the number of items in the select list

Oracle:

ORA-01785: ORDER BY item must be the number of a SELECT-list expression

In our scenario at first request (http://example/index.php?id=1 ORDER BY 1) we will not get the normal page (the query output will be sorted alphabetically based on the first table column values), so we will request http://example/index.php?id=1 ORDER BY 2 and we will get again a normal page. Now we already know that the table has two or more columns. Now requesting http://example/index.php?id=1 ORDER BY 3 we will get a DBMS error message (similar to one of the aboves) or a blank page (if the administrator configured the web server not to return error messages): the remote SELECT query is on a table with two columns and we can append data through the UNION ALL SELECT statement.

EXPLOITING

Both techniques are valid, but probably ORDER BY is more reliable on some custom DBMS/web application settings where, for example, NULL is not allowed as column type: quite rare case, but it can happens.

Once we know how many columns there are in the table we have to check if the inband SQL injection is effectively visible (exploitable). In our scenario we can request http://example/index.php?id=1 UNION ALL SELECT NULL, '1234' and if the string 1234 appears in the HTML source code of the HTTP response page, we have an exploitable SQL injection. Remember to correctly encode with a DBMS CHAR() or similar function the strings to evade magic_quotes_gpc and some other security settings. I will illustrate some possible security settings evasion techniques in a future post.

Now that we have an exploitable inband SQL injection we are ready to take advantage of this threat in our penetration test, for example performing a remote DBMS fingerprint [1] [2], usually the second step when exploiting this web applications vulnerability.

NOTES

The ALL is strongly advised to evade DISTINCT if present in the original web application SELECT query.

The columns entries data type must fit: usually NULL fits all DBMS data types. The point here is to find a string (depending on the remote DBMS varchar, bpchar, etc) where inject our query. In our scenario being the remote DBMS MySQL it will work also in the first column even if it is an integer (int) because MySQL is not too restrictive in data types matching.

In case the web application SELECT statement is similar to "SELECT id, name FROM testtable WHERE id=" . $_GET['id'] . " LIMIT 0, 1" we can evade the SQL syntax LIMIT by commenting our own injected query at the end, for instance requesting http://example/index.php?id=1 UNION ALL SELECT NULL, NULL-- where '--' starts a valid SQL comment in MySQL, Microsoft SQL Server and others DBMS.

Implementation on sqlmap code:

• inband SQL injection library
  

Posted 11th July 2007 by Bernardo Damele

Sursa: Bernardo Damele A. G.: Insight on UNION query SQL injection