Skip to content ↓ | Skip to navigation ↓

SQL injection is arguably the most severe problem web applications face. OWASP, an online community devoted to web application security, consistently classifies injection vulnerabilities as number one on their OWASP Top 10 Project.

SQL injection vulnerabilities are a favorite amongst a number of “hactivist” groups whose aim is to cause disruption in the corporate community because they are relatively easy to discover and exploit. As a result, a number of major corporations and groups have fallen victim to SQL injection vulnerabilities.

In this series of VERT Vuln School, we’re going to play the role of an attacker and demonstrate how SQL injection vulnerabilities are discovered and exploited. Throughout this series, we assume that the reader has little-to-no knowledge of how SQL injection vulnerabilities are discovered or exploited, and therefore we provide low-level details on how attackers leverage these vulnerabilities.

To begin, we introduce our custom vulnerable web application, the Bank of VERT:

sql injection 101 image 1

Upon logging in as a normal user of the Bank of VERT website (in this case, as the user “charlie”), we’re able to select an account for which we’d like to view the balance for:

Untitled-1

Clicking on our account number reveals how much money we have in that particular account:

Untitled-2

This web application, while seemingly basic, contains fundamental issues that plague even the most popular and well-developed web applications. The first issue we’ll look at is authentication bypass.

Untitled-3

One of the most common checks for SQL injection is the injection of ‘ or ‘1’=’1

Let’s see what happens when this is injected into the password field of our web application, along with a username that probably doesn’t exist, fakeusername.

Untitled-4

We’re logged in as the user bob. How and why did this happen? Let’s take a look at some of the code:

// check if login request if(isset($_POST[‘username’]) and isset($_POST[‘password’])) { // craft vulnerable query $query = “SELECT * from users where username='” .$_POST[‘username’]. “‘ and password='” . $_POST[‘password’] . “‘”;

// get result of query from mysql $res = mysql_query($query);

// if $res is false, query failed if(!$res) { $message = ‘Login failed’; } else { // retrieve result set $row = mysql_fetch_assoc($res);

// if username is set, the query was successful // set session variable and redirect if(isset($row[‘username’])) { $_SESSION[‘username’] = $row[‘username’]; header(‘Location: http://bankofvert/account.php’); die(); } else { $message = ‘Login failed’; } } }

This is a snippet of code from index.php which performs the authentication portion of the Bank of VERT website. The important thing to note about this code is how the query is crafted:

$query = “SELECT * from users where username='” .$_POST[‘username’]. “‘ and password='” . $_POST[‘password’] . “‘”;

Even if you don’t know PHP, you can clearly see that the login-form variables, $_POST[‘username’] and $_POST[‘password’] are being directly inserted into the query. This is bad. To see why, let’s see what a normal query looks like, and what the query looks like after it contains attacker-tainted data. Let’s assume our normal user charlie is logging in with his password abc123. The crafted query (after PHP inserts these values into the query) will appear as follows:

$query = “SELECT * from users where username=’charlie’ and password=’abc123′”;

Once this query is sent to the DBMS, if there is a record where the username is charlie and the password is abc123, a result is returned (effectively meaning that the credentials were correct). Conversely, if there is not a record in the database where the username is charlie and the password is abc123, no result is returned (effectively meaning that no user with these credentials exist in the database). Now, let’s take a look at what the query will look like once the attacker submits tainted-data:

$query = “SELECT * from users where username=’ fakeusername‘ and password=’‘ or ‘1’=’1′“;

We can see that by injecting ‘ or ‘1’=’1 into the password field, the attacker is effectively breaking out of the password string of the query using a syntactically-correct snippet of SQL. As a result of doing so, he/she is controlling the overall true/false-ness of the query, effectively causing the where clause of the query to become always true (since the or operator has lower precedence than and, and because 1 is always equal to 1).

Once this query is sent to the DBMS, all records from the table are returned. In our case, the first record in the database belongs to user bob, and since bob’s database record was on top of the result-set, the web application allows the attacker to authenticate with bob’s account.

This was a detailed example of how SQL injection can be used to bypass authentication. In the next installment of this series, we will see how an attacker can use a SQL injection vulnerability to steal sensitive data.

For those that would like to try out SQL injection first hand at home (and not in your production environments), the source code for the Bank of VERT is available here.

Title image courtesy of ShutterStock

Hacking Point of Sale
  • …The first rule I've learned during my IT Education was to never check Passwords without encryption, I think even storing unencrypted Passwords is forbidden by law.

    if you store sha1($_POST['password']); into the Database, and build the Select as follows (which we ALL should have learned!!!):
    $query = "SELECT * from users where username='" .$_POST['username']. "' and password='" . sha1($_POST['password']) . "'";

    Then this injection is simply not possible!
    so The Programmer of the Bank site is a really beginner!

    • Ray

      Jonny, not true. It has nothing to do with the password itself. You're simply making a statement that is always true. Therefore, you will be logged in because 1 always equals 1.

      • joepettit

        (From @Craigtweets)

        Ray – I think the point Jonny is trying to make is that where … password = sha1("' or '1'='1") will not inject because it will end with the DBMS seeing something like:
        SELECT * from users where username='fakeusername' and password='154EEC809FB37F6944ECCF1FA9C6C981EB15D063';

        Your point of course is still valid as noted in my other comment.

        There is however a very simple and effective solution to this problem. @Jonny, @Ray — Care to post a comment with some better PHP?

  • Hi Jonny,

    Hashing is of course the way to go, but your query is just as vulnerable. Consider the username "charlie' or 1=1–".

  • So my final solution would look like this
    (php and PostgreSQL)

    <?php

    if( isset($_POST['username']) && isset($_POST['password']) ){
    $con = pg_connect();
    if( $con === false ){
    die('db connect error');
    }
    $username = pg_escape_string($con, $_POST['username']);
    $password = sha1($_POST['password']);

    $query = '
    SELECT *
    FROM users
    WHERE username = ''.$username.'' AND
    password = ''.$password.'';
    ';

    $result = pg_query ($con, $query );
    if( $result === false ){
    pg_close($con);
    die('Error in SQL Query');
    }

    $numrows = pg_num_rows($result);
    if($numrows>1||$numrows<1){
    pg_close($con);
    die('wrong user or password!');
    }

    $userdata = pg_fetch_assoc($result);
    if ( $userdata === false) {
    pg_close($con);
    die('wrong user or password!');
    }
    pg_close($con);

    //do something with userdata!

    }