Most of us Security Analysts will be aware of SQL Injection. This article takes you through exploiting an Error-based SQL Injection using Double Queries. As for the demo below, we are using MySQL database alongside PHP.
Note: We shall not be going into basics of detecting an SQL Injection.
LEVEL-1 - Finding the White Rabbit
Hello Alice! Let’s have a look at the application and try to spot the White Rabbit.
Seems like a simple application that does some processing with the help of the parameter id. As in any SQL Injection, our first move would be to insert a single quote in the id parameter.
And MySQL ERROR! The backend code just paved the way for us to enter the wonderland. As shown above, the error is caused because of the extra single quote.
LEVEL-2 - Following the Rabbit
Let’s try poking at this injection a little bit more. Let’s try a simple boolean AND condition to check how the application reacts.
Seems to be working fine! Some content when the condition is true, and nothing when the condition is false. Good for a Boolean-based blind SQL Injection. But, can we do something which is faster and will require lesser efforts, and might produce less traffic than performing a Boolean-based Injection.
If you are thinking we can use the MySQL Error, you are spot-on!
LEVEL-3 - Down the Rabbit Hole
Before exploiting the error, let’s get the base right. We are going to go through the following concepts:
- count()
- Group by clause
- floor() and rand()
- The MySQL bug when count() and Group by are used together
Time to revisit the MySQL command prompt!
Count() - count(*) will display the number of rows in the table.
Group by - Group by does aggregation. It aggregates or brings together the same values in the column. The below screenshot will clear it.
As seen above, the value “sample” has 2 rows and “demo” and “demo2” have 1 each.
Floor() and Rand() - Floor() will reduce an decimal value to the nearest possible in integer, and Rand() will create a decimal value between 0 and 1. Using both of these in combination will produce 0 and 1 values randomly.
Now, let’s combine all of the above.
Step 1 - Create random values.
Note: “a” is used as an alias for floor(rand()*2).
Step 2 - Let’s group ‘em. We have 3 possible results:
- A combination of 0 and 1
- Only 0
- Only 1
Step 3 - Now let’s try to count the number of rows of each value being produced by the above statement. So basically, count represents the number of records being grouped together under the column “a”.
Upon executing the above statement a couple of times more, we get the below error:
Duplicate entry ‘1’ for key ‘group_key’ - seems like there is problem with the group by’s temporary table while calculating the number of rows being aggregated together. Ideally, there should be unique values 0 or 1 or both after aggregation and this temporary table is then passed to count(). However, there seems to be redundancy in this temporary table that is created.
Ideal Grouped table scenario
Select Count |
Values (group_key) |
2 |
0 |
2 |
1 |
Error generated scenario
Select Count |
Values (group_key) |
2 |
0 |
2 |
1 |
? (Error) |
1 (Duplicate Entry) |
Let’s use this conundrum caused by Select, count() and Group by to extract information from MySQL.
LEVEL-4 - Go for the Queen
Let’s try to get information based on the error that we receive from MySQL. Considering a real-life attack scenario, we might not know the name of any table so we can use information_schema for the extraction purpose. In the example shown below, information_schema.tables is being used.
Note: concat is used for concatenating 2 strings. database() is the function for extracting the name of the current database being used.
Applying group by:
Adding the spice of select and count():
Invoking the error on repeated execution:
As seen here, the error now displays the name of the current database.
Now, let’s come back to the application and go for the prize.
On executing the above command on the application:
AND operates on 2 operands, and the output of the second query gives 2 columns. That can be resolved by nesting this query inside another query that returns only column. In other words, we shall make out main query a derived table for another select command to work on.
Using the same on the application:
As the query might be returning more than one row (0 and 1), it gives out an error for the same. But that should not be a problem as the Duplicacy error has not been invoked. As was seen above on the MySQL prompt, let’s try executing the same query a few times more.
Bam what!? All hail SQL Injection!
We have managed to extract the database name through the MySQL verbose errors shown by the application. Let’s try to extract more information.
MySQL version
Now, we shall aim for the table name and the columns in it. This can be done by using the tables information_schema.tables and information_schema.columns.
Displaying the first table in the current database
Attempt to displaying the second table in the current database (No more tables)
In the same way, the columns of the users table can also be enumerated by using information_schema.columns.
Enumerating the first column of table users
Enumerating the second column of table users
Enumerating the third column of table users (No more columns)
Similarly, we can extract more and more information from the database using the MySQL errors.
And Alice shall live happily ever after in wonderland!
Demo details:
- Backend Database: MySQL
- Database name: test_app
- Table name: users
- Column names: id,name
- Vulnerable PHP code
connect.php
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test_app";
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
show_details.php
<?php
require_once "connect.php";
if(isset($_GET['id']))
{
//$array = Array();
$id=$_GET['id'];
$sql = "SELECT * from users where id='".$id."' LIMIT 0,1";
$result = $conn->query($sql);
if ($result->num_rows!=0){
$array = $result->fetch_assoc();
echo "No error here!";
//echo "<label>ID: </label>".$array['id'];
//echo "<br/><label>Name: </label>".$array['name'];
}
else {
//echo "some problem";
print_r($conn->error);
}
}
?>
References: https://github.com/Audi-1/sqli-labs
Article Link: https://blog.lucideus.com/2019/03/sql-injection-understand-double-query.html