SQL Injection

SQL injection has been a major cause of information breaches (Talk Talk, Ashely Madison 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, or other ways of user input.
• 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='A User' and PASSWORD='Their Password';

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 a PHP based version that is equally flawed

$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)

The problem with Flexibility

The first issue an attacker may exploit is the flexibility of SQL itself. We need to be able to build complex queries to get the job done, and the database engine has no way of telling a "good" query from a "bad" one.

For example, the following statements are both valid SQL

SELECT * FROM user WHERE username="Dang" AND password="Swordfish";

as is this statement

SELECT * FROM user WHERE username="Dang" AND password="Swordfish" OR evil="True";

So if we can find a way to injecting more elements into the SQL statement, then we will be able to change the behaviour of the query

The Problem with building database strings

To see how this is done, we need to look at the way the database query is built.

If the developer build the string through concatenation (or simple variable substitution), it gives the attacker a way to inject these values

For example. If the user supplies the following inputs

• username = Dang
• password = Swordfish

Our Query ends up being as we expected:

SELECT * from users WHERE username="Dan" AND password="Swordfish"

To build our desired query, we could manipulate the value of the password variable. For example if we used the input.

• username = Dang
• password = Swordfish OR evil=True

our SQL statement ends up looking like this

SELECT * from users WHERE username="Dan" AND password="Swordfish OR evil=True"

Messing Around in Quotes

The query is getting closer to what we wanted, but we still have a problem. Because of the way the strings are built, the statements will be evaluated against whatever string is supplied. Therefore, we are currently checking to see if the password is Equal to Swordfish OR evil=True.

The attacker needs to find a way of breaking this input string into its components. We can do this by inserting our own quotes in the input string.

• username = Dang
• password = Swordfish" OR evil="True

Turns our query into valid SQL

SELECT * from users WHERE username="Dan" AND password="Swordfish" OR evil="True";

This will break our query into its individual elements, meaning that the OR statement is executed.

Important

The quotes used depend on how the query is built. You may need to substitute single and double quotes break the query.

TODO: Video

Breaking the Query

Let's take our first go at breaking the query here.

The logic in the program will allow the user to login if the database query returns more than one row. Therefore if we can manipulate the query to do this we should be able to gain access.

Note

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.

As we don't know the users password, we will need to trick the database into returning an appropriate record.

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. We can see this in the truth table below:

Username	Password	Match User	Match Pass	Match 1=1
James	Swordfish			YES
Dan	Foobar	YES	YES	YES
John	Pirate			YES

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, its likely that the password string will be hashed. In this case, if we use the password field our injected 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 enclosed 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#"
  • password = "that' OR 1=1--"

Note

Both the Hash # and double minus -- represent comments. You can use whatever you want but my experience, -- is more reliable.

However, I cant seem to shake the habit of # meaning a comment, so I tend to use it by default¹.

This allows the attacker to create a string that will return all elements in the user table.

If we think back to the logic of the login page, the system will authenticate as the FIRST element that is returned. Therefore we are able to login as a user.

Example Login Bypass: 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)

The page takes two parameters:

• Email Address
• Password

Our first step is to try to guess the structure of the query string generated: As this is a login page, we can take a pretty good guess that is going to be something like this.

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 credentials of "Foo" and "Bar" gives us the following.

Easy Mode

"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 doesn't tell us which part of the query is wrong. It is still common to see sites with either "Username incorrect" or "Password Incorrect" shown as separate 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 interchangeably. Lets try the example using the other form of quotes, which does breaks our query, and displays an SQL error message.

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 doesn't 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 relatively common.

I tend to avoid using that 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 before it is inserted, we end up with the query being.

SELECT * FROM user WHERE username = 'foo' AND password = 'md5hash(bar' OR 1=1');';

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.

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 article we have covered the basics of SQL injection, and demonstrated how we can inject statements into an poorly written SQL statement 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.

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

---

1. At times this habit has lead to much wailing and gnashing of teeth. ↩