Starting Out With SQL Injection

Straight from Wikipedia, "SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application". Usually this exploit is because the user's input is not sanitized when it's submitted to a web form or application. On a virtual machine I have DVWA (Damn Vulnerable Web App) setup, which will help us with understanding the more hands-on approach. For those of you that haven't used DVWA before, it is a web application that is full of security holes for you to practice solidify your understanding of web application security. You can grab a copy to follow along.

http://www.dvwa.co.uk/download.php

Normally you won't look at any code when attacking DVWA, however since we are just starting out and learning how SQL injection works I will give you an example to work with. For our injection to be successful we will need to understand just how SQL works. We know from looking over this server that the back end for DVWA is MySQL although the back end database doesn't really matter for this basic example. Suppose you want to query the firstname and lastname of users in your database and the key value would be the user's ID. Your query might look something like:

SELECT first_name, last_name FROM users WHERE user_id = 'jsmith'; 

The results should come back as follows provided that the user exists:

First Name: John
Last Name: Smith

This is simple enough. What if I wanted to enumerate all the users though without having to check each individual userid by hand? Let's look at a PHP code sample.

<?php

if(isset($_GET['submit'])){

     // Get the user's ID from the submitted form field  
     $id = $GET['userid'];    

     // Build the query  
     $q = "SELECT first_name, last_name FROM users WHERE user_id = '$id'";    

     // Run the query and store the results  
     $r = mysql_query($q);    

     // Check if there were results from the query
     $num = mysql_numrows($r);
 
 if ($num > 0){
 
  $i = 0;
  
  while ($i < $num){
  
   $fname = mysql_results($r, $i, "first_name");
   $lname = mysql_results($r, $i, "last_name");
   
   // Print out the info for the user
   echo 'ID: '. $id .'<br />';
   echo 'First Name: '. $fname .'<br />';
   echo 'Last Name: '. $lname .'<br />';
   
   $i++;
  }
 }
} 

This code should be self explanatory. The query ($q) looks for the first name and last name of the user whose id is submitted to the form. Now let's look a little deeper at how we can manipulate this query a little bit. When I enter "jsmith" in the form and hit submit my query is evaluated out to:

SELECT first_name, last_name FROM users WHERE user_id = 'jsmith'; 

Just like we saw originally. What if I entered a character instead of a user_id? Suppose I put in a single quote (') and submitted the form, what do you think will happen? Turns out that the page displays the following message:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''''' at line 1"

This may seem a little weird at first, but the SQL is actually telling you that the single quote " ' ", which is surrounded by double quotes is causing an error when expanding out the query. The one thing that you will learn with SQL injection is that errors are always a good thing. How can we get the database to display the information of all users? We would have to build an SQL statement that would have to be valid for not only my user_id, but for all user_id fields. Actually the trick isn't to manipulate the field it is to ignore it all together. To perform our first SQL injection we will want to submit the following in the web form:

jsmith' OR 1='1 

When you do this you will essentially be submitting an expression as your user_id variable. The SQL query is now going to expand and give you the following:

SELECT first_name, last_name FROM users WHERE user_id = 'jsmith' OR 1='1';

Well we definitely have a user_id named jsmith so that will evaluate to true, however we have also introduced an OR statement. When evaluated the SQL will check to see if there is a user_id = jsmith OR if 1 = 1, which is always true! Since it is always true the results should always be returned and you should now see a list of the first name and last name of all users in the database! Let's look again at how this actually works. Instead of just a user_id being queried for, we created an input so that the first case is either true or false (user_id = 'jsmith') and a second case that will always be true (1=1). Also because of the way we crafted the statement the SQL is well formed (notice the single quote after the user_id to close the first item and the single quote in front of the 1 to close the second item). So our crafted jsmith' OR 1='1 gets dropped into the WHERE clause to form:

WHERE user_id = 'jsmith' OR 1='1'; 

This is just the beginning of SQL injection and there are tons of different variables to take into consideration when trying to exploit a target. Not all database back ends understand the same syntax, comments for databases are different, and different software prevents certain SQL injections all together! Now that you have seen and done an example though you will have to get familiar with the different types of injection that you can perform. In the next post I'll talk more about SQL injection and in particular how you can use it to map fields in the schema of the database.