Skip to content

SQL Injection

SQL injection has been a major cause of information breaches (Talk Talk, Ashely Maddison etc). Such breaches are easily avoidable, and usually occur because the developer trusts user input. Considering that they can usually be stopped through the use of parameterised queries, you have to wonder why they still exist - but they do.

Principles behind SQL injection

To perform injection we need a couple of things in place. We need:

  • Direct control over the input sent to the server (ie though GET/POST) requests
  • The Developer to have broken the Rule #1 and trusted user input

Injection occurs when we can modify the SQL query string sent to the server by adding (injecting) code that the developers were not intending us to run.

An example Query

If we think about a SQL query that can be used to check login credentials we could have something like:

SELECT * from user WHERE username='this' and PASSWORD='that'

Without using placeholders this would lead us to code that looks something like this (in Python):

qry = "SELECT * FROM users WHERE username='{0}' AND password='{1}'".format(username, password)
result = sql.run(qry) #Run
if len(result) == 0:
    print "FAIL"
    return False

Or the Equally Flawed PHP version:

$username = $_GET['username'] //Get username from request
$password = $_GET['password'] //Get password from request

$qry_string = "SELECT * FROM users WHERE username='".username."' AND password=".'password'"
$result = $conn -> query($sql);

if ($result->num_rows == 0){
    //Redirect to fail
    header( 'Location: fail.php');
}

There are a few errors in this code that we will exploit shortly:

  • Building the query string through concatenation
  • Having a weak fail condition (only fail if we get 0 rows returned)

Breaking the Query

Let's take our first go at breaking the query here. Given the flaws above, if we can get the database to return more than one row, then the program will allow us to login.

We will often have to guess at how the query string is generated. Applying some common sense and poking to see what error messages come up is helpful here.

Recall that SQL allows us to use logical operators to help filter the results. Thus If we can inject the operator OR 1=1 into the query we will have the following SQL query (which will return all rows of the users table) which means we will return more than one row of the table, so we meet the login criteria.

SELECT * from user WHERE username='this' and PASSWORD='that' OR 1=1;

As 1=1 is always TRUE, this should return the result containing all the rows of the database.

Note: there are a few things here that could trip you up:

  • Order of Precedence: In logic the AND operator is evaluated before OR so we need to make sure our query ends up being (SELECT * FROM USER WHERE (username = 'this' AND PASSWORD='that') OR 1=1;

  • Passwords are likely to be hashed before being sent to the database. In this case our query could also be hashed. Think about where you are putting the injection code.

We now need to try to work out how to inject the code. The important thing to consider here is that the query will tend to be built up from a string. For example: 

$output = "SELECT * FROM users WHERE username = '<this>' AND password = '<that>';"
(Note: angle brackets denote user input)

If we replace the contents of the password (that) with the desired payload that OR 1=1 we end up with:

  • user = "this"
  • password = "that OR 1=1"
$output = "SELECT * FROM users WHERE username = 'this' AND password = 'that OR 1=1';"

Which compares the password value to the string that OR 1=1. Here we need to escape the string portion of the input. To work around this we need to think about where the quotation marks are needed.

As we want the input to be SELECT * from user WHERE username='this' and PASSWORD='that' OR 1=1; We need to add a quote after that making our input that' OR 1=1

  • user = "this"
  • password = "that' OR 1=1"

While this escapes the first part of the string, we still have an error due to the trailing quotation mark. With our SQL query something like: `SELECT * FROM user WHERE username="this" AND PASSWORD="that" OR 1=1";' With an extra unclosed quotation mark after the OR 1=1 part. This will cause an error, when SQL processes the string, so we need to remove it.

$output = "SELECT * FROM users WHERE username = 'this' AND password = 'that' OR 1=1';"

We can make use of a couple of approaches to remove this from the final query:

  • By adding quotes around the bulk of the OR statement: -password = "that' OR '1'='1"

  • By using comments (this is my preferred approach) -password = "that' OR 1=1#"

Putting it all together With a Demo.

Lets use the SQL Injection theory from above to bypass a login page on the web trainer. (You can find the login page at http://172.18.0.1/sqli_login.php)

Web Trainer Login Page

The page takes two parameters:

  • Email Address
  • Password

Our first step is to try to guess the structure of the query string generated: For example SELECT * FROM user WHERE email = "<input>" AND password = "<input>"

We can then try the page, to see what happens (and if we have discovered any potential credentials then we could use them as a starting point). Entering garbage cretentals of "Foo" and "Bar" gives us the following.

Login with junk credentials

Easy Mode

NOTE: "Easy mode" is on here, I have added a debugging statement that shows the query string being used by the server, hopefully you wont encounter this in real life.

However, we do miss out on a common source of information leakage on login pages, as the error message doenst tell us which part of the query is wrong. It is still common to see sites with either "Username incorect" or "Password Incorrect" shown as seperate statements, this can tell us that we have guessed one part of the credentials correctly.

Our next job is to try to break the SQL statement. Lets just try entering the username as foo'. However, this doesn't break our statement as we expected. The reason for this are the quotation marks used. In many languages the single ' and double " quotes can be used interchanagably. Lets try the example using the other form of quotes, which does breaks our query, and displays an SQL error message.

Login with SQL Error

However, this does give us a clue to the format of the Query string.

  • We know there are two ways of representing strings Single Quote, and Double Quote
  • We know that a single quote doenst work
  • Therefore a double quote must be used.

Now we have worked out the correct form of the query passed to the database, we can try to get SQL to return a set rows from the table.

Gotcha: Hashing

Hashing of input before passing it to the password string is relivitvely common. I tend to avoid using tha part of the input for this reason.

As there are two input fields there are two places we could put the injected statement, the username or the password. However, passwords are often hashed before being passed to the database, which will also hash our injected text. You can see this if we try the input:

  • username = "foo"
  • password = "bar OR 1=1"

As the password gets hashed `SELECT * FROM user WHERE username = 'foo' AND password = 'md5hash(bar' OR 1=1');'; before it is inserted, we end up with the query being.

DEBUG: Query is: SELECT * FROM hashuser WHERE email='foo' AND password='ea00d33392c18ce22dd1d67381dace39';

We therefore need to do the injection part in the username field.

Lets put all the pieces together.

  • username foo' OR 1=1;#
  • password whatever

This turns our query into 

SELECT * FROM user WHERE username = "foo" OR 1 = 1;# AND password = "whatever";

But as anything after the hash is ignored, what the database server actually sees is just

SELECT * FROM user WHERE username = "foo" OR 1 = 1;

Completely bypassing the password element.

Successful login with SQLi

The Query returns all rows of the database, and ignores the password part. As the logic on the server for checking passwords just relies on having more than 0 rows returned for success, we are logged in as the first user in the DB.

Summary

In this artcle we have covered the basics of SQL injection, and demonstrated how we can inject statements into an poorly written SQL statemeent to effect the way the query is run.

In the next article we will build on these techniques to enumerate the database, and gain access to more information.

Your task

You can find some SQL login bypass examples on the web trainer at http://172.18.0.1/sqli_login.php

Hints:

  • Read the error messages to help
  • Think about the logic in the application, are passwords hashed etc
Back to top