picoCTF 2014 SQL Injection 1 Writeup

In this series pf writeups we'll be dissecting SQL injections to solve picoCTF challenges. There will be a total of 4 articles in this series, with each one going a bit more in depth and using different SQL injection techniques to exploit vulnerabilities present on the problem webpages.

Analyzing the source code

For Injection 1, we are given this website: http://web2014.picoctf.com/injection1/.  A very casual login prompt, but with an interesting footnote of "login.php source code". Opening up the source code we are presented with:

<?php

include "config.php";

$con = mysqli_connect("localhost", "sql1", "sql1", "sql1");

$username = $_POST["username"];

$password = $_POST["password"];

$debug = $_POST["debug"];

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

$result = mysqli_query($con, $query);



if (intval($debug)) {

  echo "<pre>";

  echo "username: ", htmlspecialchars($username), "\n";

  echo "password: ", htmlspecialchars($password), "\n";

  echo "SQL query: ", htmlspecialchars($query), "\n";

  if (mysqli_errno($con) !== 0) {

    echo "SQL error: ", htmlspecialchars(mysqli_error($con)), "\n";

  }

  echo "</pre>";

}



if (mysqli_num_rows($result) !== 1) {

  echo "<h1>Login failed.</h1>";

} else {

  echo "<h1>Logged in!</h1>";

  echo "<p>Your flag is: $FLAG</p>";

}



?>

Upon first glace, nothing seems glaringly wrong or insecure about this login script. However,  notice how the php script is creating variables for $username and $password based solely off user input, and then using them to construct a SQL query. Before going any further, it is an absolute and outright red flag  that unfiltered or un-sanitized user input that will be used to construct queries, open files, or access any sort of resource, is a source of possible exploits and leaves open gaping security holes. This example is no different. We are doing direct substitution into the query string of whatever the $_POST data is.

One more point to make is that the login script will only give us the flag if there is a single row in the database that conforms and meets the requirements. Although this is expected, its an important point to make when we analyze how to exploit this SQL injection.

The problem with substitutions

In a Utopian world where no user ever had intentions of committing a malicious deed, this substitution of $_POST data into a query string would suffice. Unfortunately, people are naturally malicious. Let's see why such a substitution could be the source of so many problems.

Here's the query string we're dealing with:

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

If $username = dog and $password = paws, then the query would end up looking like this:
"SELECT * FROM users WHERE username='dog' AND password='paws'";

A perfectly valid query string! But what if $username = 'dog ( yes thats a single quote)    and $password = paws?
"SELECT * FROM users WHERE username='' dog' AND password='paws'"

Notice how the username entry changes completely! Since this is a direct substitution, our query gets completely messed up. That query in specific would cause an error since it isn't valid SQL, but in the same way that an innocent single quote can destroy the validity of a SQL query, multiple innocent single quotes can destroy the integrity of a SQL query while still leaving it valid. Let's explore.

Injection

If we analyze what our database is doing upon SELECT queries ( such as the one present in this login script), we'll see that it will return rows where the conditional statements present in the query are true. In this case, the conditionals in the query statement are where username AND password match records in the database. Since we don't have the proper credentials, we can't get back any rows from that datanbase, but we already saw that we can alter the query to have it contain anything we wish. There's nothing stopping us then from injecting our own SQL code into the query! Speaking in the  terminology, this query is highly susceptible to UNION injection. Similar to how set unions operate in math, UNION statements in SQL queries also bring in all elements from both sides of the UNION operator. Here's an example:

   "SELECT * FROM users WHERE username='testing_union' AND password='more_tests' UNION SELECT * FROM users WHERE username='union_user' AND password='union_password";

What the UNION does here is effectively perform a set-union with the left-hand side "WHERE username='testing_union' AND password='more_tests'" and the right-hand side (SELECT * FROM users WHERE username='union_user' AND password='union_password"). If both of the SELECT statements happen to return a valid row, the UNION statement will end up merging them together into a set containing 2 rows. However, if both SELECT statements were invalid, the UNION would also return an empty set. The interesting part comes when one of the sides returns an empty set, and the other side returns a valid set. The UNION in this case would, in math terms, be doing , {null} U {valid_row}. This produces a single-item  set with element {valid_row}!.

How is that useful for solving this challenge? Since we can inject these UNION statements, we can effectively UNION an invalid set (by providing bogus username and passwords) with a valid set. This will in turn cause the check  if (mysqli_num_rows($result) !== 1) to be false, since it contains 1 row, and print out a flag!

The only problems with performing these UNION queries, as we'll see, is that it requires a bit of guessing. The guessing part originates from the fact that because we're doing "SELECT *", the asterisk signifies that we would like to select (and return) all columns pertaining to a row in the database. Since we don't exactly know how many columns are present in this table, creating a fictitious row will require guessing how many columns constitute a row in this table. We know it has to be at least 2 (because we know for sure there's a 'username' and 'password' field). So if our query looked like this:

"SELECT * FROM users WHERE username='bogus' AND password='bogus' UNION SELECT 1,2";
The UNION in this case will merge the results from selecting username and passwords of 'bogus' (which is clearly empty) and the result of SELECT 1,2 (which will return a row containing the elements (1,2) for the values of username and password).

However, we could also make the query look like this:

"SELECT * FROM users WHERE username='bogus' UNION SELECT 1,2";
 And we'd achieve the same results as UNION is still performing the same operation. As a matter of fact, it will be easier to produce a query of the second form in our injection. The only thing holding us back is that we don't know how many columns each row has, but 2 is a reasonable start.  In order to effectively modify our query to match the  one above, we'll have to set username  =   bogus' UNION SELECT 1,2; -- ' and password  can be anything! Let's perform the substitution and see the results.

Here's the substitution. (assuming we set password = 'bogus')

"SELECT * FROM users WHERE username='bogus' UNION SELECT 1,2; -- '' AND password='bogus'";

The injection here contains the characters '--' which in MySQL is defined as a comment line. Everything after the '--' is ignored. So if we ignore everything past after the '--' we get

 "SELECT * FROM users WHERE username='bogus' UNION SELECT 1,2;"

Which will return us a row of (1,2). Of course, this is subject to getting the correct amount of columns. If each row contains 3 columns Per Se, then it would be UNION SELECT 1,2,3. Let's try with 1,2 and see the results.

There's the flag!

This is part 1/4 for the picoCTF 2014 SQL injection write ups, and this article mainly served to be an introduction to SQL injection and applying it to Injection 1 from the picoCTF challenges. In the next SQL Injection post, we'll look into Injection 2.

Please leave me a comment if you wish to say anything =)