Blind SQL Injection 1.0
In this article we’ll discuss Blind SQL Injection and how the attack can be carried out. We’ll start off with the basics of SQL Injection, briefly discuss its types and then find out how Blind SQL Injection is different. We’ll look at what data can be stolen and whether this attack can be used to gain further control over other hosts in the network. Lastly we will look at how to mitigate an SQL Injection attack.
How does stuff…normally work?
The fundamental question to be asked is – How does user input supplied on a webpage give you results? What happens when you search for a specific book on Amazon? What you type on a webpage is user input which is sent to a server on Amazon. Now, something has to be present on Amazon which will read that user input and decide what to display on your browser. This is where the application’s source code comes in; it reads the user input, forms an SQL query with it and sends this statement to a database. The database runs the SQL query and returns relevant results. These results are stored in variables in the source code, which are then written back to the user’s browser. Lets look at exactly what happens:
The user enters “Harry Potter” in a search box. This is packaged into a SQL query by the application’s code and sent to the database. That query could look somewhat like this.
<strong>SELECT * from books where title='Harry Potter';</strong>
The database would run this query and return a number of rows; not more than 7 though .. for now anyway ;)
So what’s SQL Injection…?
Now, think of it… you can control the output of a process only if you have some control over the input to that process. The only thing you really have control on, is the user input; the application’s code and DB records are not what any user will have direct access to. So, its safe to conclude that an attacker is going to tamper with the user input, while using SQL Injection as an attack technique.
The user input in that earlier query was “Harry Potter”. Nothing else. So if you want to try and perform SQL Injection, you’re going to have to do something to the user input. So instead of typing in just “Harry Potter”, lets type in Harry Potter’ instead, and see what happens.
Huh? There’s no book like that. Right. But look what that user input does to the backend query. It modfies the query as below (extra ‘ character at the end) and causes the SQL query to break and throw an error back on screen, complaining about the malformed syntax:
<strong>SELECT * from books where title='Harry Potter'';</strong>
The moment you see this error on screen, you’ll know that you have been successful in modifying the results of ..something you could not see (SQL query) by giving it an input which you could(HarryPotter’). That’s why it’s called SQL Injection.
Now, in this case you had an error back on screen..telling you that what you did was successful. At times, despite the backend query being vulnerable, the developer would have coded the application or configured the framework to not display any errors to the user. In many cases, the application will not return the results of any modified SQL query to the user’s screen.This makes it a little difficult for an attacker to find out if he’s on the right track or not….that is where Blind SQL Injection comes in.
Blind SQL Injection
Now think of a blind man with a white cane who is trying to move around his house. How does he know that he is in his kitchen? He feels around for familiar things, while tapping with his cane for the same purpose. The moment his hands or cane come into contact with something familiar, he changes direction and starts tapping and feeling again. He repeats this process till he is in the kitchen. Correct? Various objects around the house told him that he was on the right track, although he himself couldn’t see any of them. Do you now want to guess..what Blind SQL Injection is? :)
The attacker will send query after query to the web application, which won’t display a result at any point, but will give the attacker some response…to his input. Its now up to the attacker to look at the response and decide whether his query succeeded or not. Still sounding vague? Lets revisit the blind man example.. There were only 2 possible responses that would make sense to the blind man. True OR False. There wasn’t any other option. If he found a familiar object…TRUE, if not ..FALSE. Similarly the attacker has to form queries that will return TRUE or FALSE. If the query returns TRUE page1.html will be displayed and if FALSE error.html would be displayed. And he can continuously tweak his queries ..again and again till he retrieves whatever data he wants from the application.
Lets revisit our Harry Potter example and see how we’d use blind SQL injection there. We’ll give an input ofHarry Potter’ and 1=1;# and see what happens. The query that gets formed is as follows:
<strong>SELECT * from books where title='Harry Potter' and 1=1;#';</strong>
We have the # because that signifies a comment and we’re just saying we aren’t interested at all in what there is after the #. The query though means.. Search for all books whose title is Harry Potter and where 1=1. Now the first part is self explanatory, but what about 1=1?
Remember..We’re thinking Boolean, we’re thinking TRUE/FALSE. So we want a query whose result is TRUE or FALSE. And 1 is always equal to 1. So the query will return TRUE, which means.. Harry Potter books were found and 1=1. TRUE and TRUE.
Now we know how a TRUE response looks like. We need a FALSE response to confirm the presence of Blind SQL Injection. So we give our user input as Harry Potter’ and 1=2;# . The query that gets formed then is as follows:
<strong>SELECT * from books where title='Harry Potter' and 1=2;#';</strong>
…. which evaluates to TRUE and FALSE = FALSE…. which leads to a different page being displayed, maybe a generic error page..thus confirming the presence of SQL injection. If both the responses had been perfectly alike, we would’ve had to try other test cases to determine if a parameter was vulnerable to SQL Injection or not.
To sum up.. Crafted Input — Different responses — Potentially vulnerable to Blind SQL
Data retrieval from other tables – Manual
Lets now try and look at how we can use BlindSQL to retrieve data from the database. The first thing we do is do a search for a record we know exists. Lets search for Harry Potter.
Now lets try and modify the query a little bit to Harry Potter’ AND 1=1#. Here ‘Harry Potter’ is the known initial input, the first ‘ is to comply with MySQL syntax, the AND 1=1 is the string which will try and detect if the parameter is vulnerable to SQL Injection or not and the # is a comment in MySQL, saying we don’t care what the rest of the query says. Lets look at the result.
Exactly the same result. That’s because both parts of the query are still TRUE. Now lets change this to Harry Potter’ AND 1=2#. If we still get the same result, we wont be sure if the application is vulnerable to SQL Injection or not, but if its a different page….
We get a blank page as a result…no rows..no data. This clearly means that we have managed to change the structure of the underlying query and the page is vulnerable to SQL Injection.
Now lets try and extract other data from the application. Lets target just the version of MySQL running here; that’s more than enough to explain things. Just to make things a little easier – we’ll retrieve these using the Error Based technique first and retrieve the exact same values using BlindSQL, just to show you both are possible.
So lets look at data retrieval using the easier error based technique first. The first input we’ll use is:
Harry Potter’ which predictably results in an error message as below.
Lets find out how many columns the DB has. The input Harry Potter’ ORDER BY 3# returns a normal page but the input Harry Potter’ ORDER BY 4# returns an error as under, telling us there are 3 columns that were queried.
Now let us see if the data is actually displayed on screen if we add a new query. So lets give as input – Harry Potter’ UNION SELECT 1,2,3#
Look at the last row. The numbers 1,2 and 3 are all displayed on screen which means we can now try and retrieve other more interesting data and display it on screen; in the places where 1,2 and 3 are displayed. Lets try and get the MySQL version then? Our input will be Harry Potter’ UNION SELECT 1,2,version()# , which means we should have the version displayed in the next screenshot instead of 3.
Bingo! Look at the last row. We have the MySQL version which is 5.1.41-3ubuntu12.10. version() by the way is a built in MySQL function which we can call directly. Other databases might have similar functions but with a different name.
Yes, yes.. we can keep playing here (how do I know? ;)) and modify our queries to retrieve data from any table that our user has rights to query. Now lets look at getting theMySQL version out using Blind SQL Injection, which as you will see is a little more complex :). Remember .. blind SQL = Boolean = TRUE or FALSE. This means query output MUST be either TRUE or FALSE. So lets go..
Well, we know one thing for sure.. the version number will start with a number. So we can craft a query to try and guess the first number. So lets give our input as Harry Potter’ and substring(version(),1,1)=1;#. The substring function is another builtin MySQL function which will extract, in this case – 1 character from offset 1 of the data returned by version() and compare it with 1(right hand side of the =). Too much eh? Don’t worry, things’ll get clearer going forward :). For now just try and understand as much as you can.
The result is a blank page. When did we last get a blank page? Try and remember. It was when we gave an input of 1=2. This means that some part of this input Harry Potter’ and substring(version(),1,1)=1;# is most definitely FALSE. Since we know for sure there are books which have their title as Harry Potter, it must be the substring part of the query which is evaluating to FALSE.
Now WHAT in the substring query? It has to be the ’1′ against which the compare is being done. Try and understand..We are asking the DB – If there is a book with a title ‘Harry Potter’ and if the first character of version() is 1, return TRUE.. else return FALSE. The DB returned FALSE, which means the first character of version() is NOT equal to 1 for sure!
Still a little unclear? Lets look at the next 4 queries where we compare against 2,3,4 and 5 and study their outputs. Our inputs will be:
Harry Potter' and substring(version(),1,1)=2;# Harry Potter' and substring(version(),1,1)=3;# Harry Potter' and substring(version(),1,1)=4;# Harry Potter' and substring(version(),1,1)=5;#
Comparing against 2,3 and 4 gives us a blank page like before..but the moment we compare against 5 we get a list of Harry Potter books.. this means that when a compare against 5 was done..both parts of the SQL query evaluated to TRUE. This means the version of MySQL starts with 5 :)
Much clearer now? Lets repeat this process for the second character. Now think a bit. The latest released version of MySQL is 5.x. So if the first character is a 5, the second character almost certainly has to be a . Lets confirm this with an input of Harry Potter’ and substring(version(),2,1)=’.';#
Yes. Perfect .. list of books again. Change this to Harry Potter’ and substring(version(),2,1)=’#'; and you get a blank page. So our version starts with ’5.’
Similarly correct inputs for the next few characters will be:
Harry Potter' and substring(version(),3,1)=1;# Harry Potter' and substring(version(),4,1)='.';# Harry Potter' and substring(version(),5,1)=4;;# Harry Potter' and substring(version(),6,1)=1;#
This brings our MySQL version string to start with 5.1.41. You can proceed similarly till you reach the end of the string. You can use the length() function in MySQL to determine the length of the string. In this case the length of the string is 19, which I found out by using this input:-
Harry Potter’ and length(version())=19;#. So you will have to launch 19 substring queries at minimum to find out the exact MySQL version. And as you most probably have already realised, it is going to be much more than 19, as you have no clue about the correct character to compare against each time. And this is just for finding out the version..where at least we have some idea about its structure. For a list of users etc… the number of queries you’ll have to fire manually, is going to be huge. Really huge. It’ll be fun for around an hour and unbelievably boring after that.. trust me ;). That’s where automation comes in. Lets look at how we can achieve this and much more using an automated tool.
Data retrieval from other tables – Automated
There’s plenty of automated tools available to do your querying. I’m not going to go into a discussion of which one is better than the other here ;). All I’m going to do is to run sqlmap and show you how much quicker it is to extract a huge amount of information in no time at all(relative to the manual method anyway ;)).
So I just feed a valid URL to sqlmap as follows:
<strong>arvind@pal-lin-arvind:/var/www$ sqlmap -u "http://localhost/blindsql_insecure.php?search=Harry Potter" -b</strong>
And sqlmap does a lot of ‘magic’ in the backend and gives me a result very quickly. Here’s a screenshot:
I guess you won’t be doing anymore manual testing then. Look towards the end of the screenshot by the way.. it says SqlMap had to perform 139 queries to get that info. Now you know how many queries, you’ll have to manually issue. Approximately anyway ;). All jokes apart though, its good to do your first few tests manually so you understand how exactly things work, before switching to automtaed tools.
I’m not going to fill this article up with what all Sqlmap can do. It can do a huge huge lot and even give you complete control of the OS if certain other conditions are fulfilled. I’ll leave that as an exercise for you though, if you’re interested, which I dare say you will be ;)
I hope that clarifies the difference between Error based and Blind SQL injection though and how Blind SQL injections is much more time consuming. There’s plenty of other shortcuts on how to even speed up Blind SQL, but really that’s not in the scope of this particular article.
Sample Code:
Here is the code that I used while writing this article. You can copy these files on to your own webserver and try it out if you want. My setup is Ubuntu 10.04 – Apache 2.2 – PHP5.3.2.1. Make sure that you have the PHP5-mysql packages installed as well; else your code won’t work.
The HTML File:
<HTML> <BODY> <FORM NAME="test" action="blindsql_insecure.php" method="GET"> <INPUT TYPE="text" name="search"></INPUT> <INPUT TYPE="submit" name="Submit" value=Submit></INPUT> </FORM> </BODY> </HTML>
PHP Code – Insecure dynamic queries
<?php
$a = $_GET['search'];
$con = mysql_connect("localhost","ad","test123");
if (!$con){
die('Could not connect: ' . mysql_error());
}
mysql_select_db("test_blindsql", $con);
$result = mysql_query("SELECT * FROM books WHERE title = '$a'") or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
echo "The returned result is Title - $row[0], Bookname - $row[1], Price $row[2]";
echo "<BR>";
echo "<BR>";
}
?>
PHP Code – Secure parameterized queries
<?php
$a = $_GET['search'];
$dbh = new mysqli("localhost" , "ad" , "test123" , "test_blindsql");
$res1 = $dbh->prepare("SELECT * FROM books WHERE title = ?");
$res1->bind_param("s",$a);
$res1->execute();
$res1->bind_result($dis1,$dis2,$dis3);
while ($res1->fetch()) {
echo "The returned result is Title - $dis1, Bookname - $dis2, Price $dis3";
echo "<BR>";
echo "<BR>";
}
?>
mysql> describe test_blindsql.books;
<blockquote> <pre>+----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | title | varchar(20) | YES | | NULL | | | bookname | varchar(200) | YES | | NULL | | | price | varchar(15) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+</pre> </blockquote>
You can fill up this table with a few rows of data before using Blind SQL Injection to retrieve data.
Mitigation:
The best way to mitigate SQL Injection is to use parametrized queries or bind variables throughout your application, wherever user input is taken into consideration while forming a query. Using dynamic queries allow the user to supply input which will modify the underlying SQL query. Using parameterized queries force the application to treat all user input as data and not give any special meaning to user input.
Other solutions include blacklisting or escaping of special characters like ‘;-# etc. While this can be effective in numerous cases, there’s always a risk of some new attack string being discovered which will bypass these filters. The same holds true for whitelisting of certain character sets as well.
Conclusion:
SQL Injection is an extremely destructive attack and if exploited can lead to an attacker eventually gaining control over large parts of your network over a period of time. Even a single vulnerable parameter in an application, can be enough for this to become a reality.
If you’ve written your code a long time ago, its probably a good idea to audit your code once and convert all dynamic queries into parameterized queries. There are numerous source code auditing scripts and tools available online; you could even write your own customized scripts though – if you are comfortable coding.
Comments
Post a Comment