PHP SQL injection & other basic security issues

SQL injection is the number one way websites are compromised. This stems from its relative simplicity and the fact that a simple programmer oversight on just one piece of data can leave the entire site vulnerable. Here I run through how to prevent SQL injection attacks and some other basic security issues using the idea of a simple login form.

The theory behind SQL injection is simple to explain. SQL queries are generally built up as strings which are then run on the database. A natural operation to perform is to get some user provided data e.g. username and password and put this into the query. An example query, not populated with provided data would be:

SELECT * from users WHERE username='' AND password= '';

This all seems simple, we just place the user provided details in the correct place. E.g. if the user provides the username user123 and the password secret, our query looks like so:

SELECT * from users WHERE username='user123'
AND password ='secret';

When we run this, if the user is in the database we’ll get a result, else we’ll get back nothing. We could have opted to just return the number of results using COUNT but we’ll assume we’re going to count the results ourselves.

Here’s an example PHP implementation of this:

if(isset($_POST["submit"])) {
    $query = "SELECT * from users 
WHERE username='" . $_POST["username"] . "' AND password='" . $_POST['password'] . "';";   
    
    if(mysql_num_rows(mysql_query($query)) > 0) {
        echo "Logged in!";
    } else {
        echo "Incorrect username or password";
    }
}

So here we’ve taken the POST values from a user form submission, placed them in our query and ran it. This code however, is very vulnerable. Lets imagine the user enters any username and their password as this:

‘ OR 1 =’1

Now when we place this into our query, it becomes:

SELECT * from users WHERE username='' AND password='' 
OR 1 = '1';

Our query will now return results even if the login details are completely incorrect. The user has ‘broke out’ of the password value and modified the criteria of the query. The first criteria can fail but 1 will always equal 1, so we’ll always get results.

The code was wrong for two resons, first it failed to escape the user input, it allowed the use of an inverted comma to break out into the logic of the query. Second, our criteria for success is whether there is more than one result. Thus if 100 results get returned, we let them in. We should have checked that we got one and only one result and disallow the login otherwise. If we made this modification then the attack above would not work (if we had more than one user in the database).

To stop user input breaking out into the logic of the query, PHP provides the mysql_real_escape_string function. This needs to be run on each piece of user provided data we put into the query. The improved code is thus:

if(isset($_POST["submit"])) {
    $username = mysql_real_escape_string($_POST["username"]);
    $password = mysql_real_escape_string($_POST["password"]);

    $query = "SELECT * from users 
WHERE username='$username' AND password='$password';";   
    
    if(mysql_num_rows(mysql_query($query)) == 1) {
        echo "Logged in!";
    } else {
        echo "Incorrect username or password";
    }
}

The user can no longer break out of our query as when they try, their inverted comma will be escaped, that is a backslash character will be placed before it.

Unfortunately in PHP there’s one more factor we need to consider, though it’s becoming less of an issue. PHP has a deprecated (as of v5.3.0) feature call magic quotes which automatically escapes incoming data. Whether or not this is on depends on the server configuration. If it is on and we use the above code, we’ll end up with data which has been escaped twice.

So we first need to check if the data has been escaped. We can do this using the function get_magic_quotes_gpc(). It’s best to make a simple function for escaping data which checks this. For example:

function escape_data($data) {
    if(get_magic_quotes_gpc()) {
       return $data;
    }
    return mysql_real_escape_string($data);
}

First the function checks if magic_quotes are enabled. If so, the data will have been escaped already so it’s returned as-is. Else, mysql_real_escape_string is used to escape the data before returning.

Revisiting the example, here’s the final code which makes use of the above function:

if(isset($_POST["submit"])) {
    $username = escape_data($_POST["username"]);
    $password = escape_data($_POST["password"]);

    $query = "SELECT * from users 
WHERE username='$username' AND password='$password';";   
    
    if(mysql_num_rows(mysql_query($query)) == 1) {
        echo "Logged in!";
    } else {
        echo "Incorrect username or password";
    }
}

Now comes the obvious question, if I have magic_quotes on, why bother with the function? Can’t we just assume all data is escaped? The reason is this creates a big portability problem. If we move the code to a different server with magic quotes off, suddenly all the user inputs go unchecked. Because we never put the escape function in, we have to go manually looking for all user provided data and escape it. If you have magic_quotes on and you can configure the PHP settings (that is, you are not on shared hosting with no configuration access) you should turn it off and escape data manually as needed. For portability it’s best to always make the PHP application responsible for escaping user provided data. There are a couple of other good reasons for disabling magic quotes too:

  1. As it escapes all data there is a performance hit when it escapes data that doesn’t need to be
  2. Having all data escaped can be annoying as not all user input is destined for a SQL query, e.g. it could be sent out in an email. Also, you may want to perform some manipulation on the raw data before it gets escaped

Above I have only mentioned the practice of modifying a query. We could also consider that the user may try to put in a totally different query, e.g. they could enter their password as:

‘;DELETE FROM users WHERE username=” OR 1=’1

Here the select query has been ended and a whole new delete one added. Luckily, PHP disallows multiple queries so the above, even though it produces a normally valid SQL query will produce an error when it is run. The escaping technique would also prevent this anyway. That’s not to say a user couldn’t easily clear an entire table with one SQL injection though. Consider that there is functionality provided for a user to delete, for instance, a post which they have written. They could modify the criteria just as in the SELECT example. This is where limits are useful in providing an extra barrier of defence. If you know a query should only affect a set number of database entries, specify it explicitly:

DELETE FROM posts WHERE post_id = '1' LIMIT 1;

Now if the user manages to provide a post_id of, as mentioned before ‘ OR 1 =’1 they will have managed to only delete one post- a far cry from destroying all data in the table.

It’s important to remember that other systems (besides standard PHP) allow multiple queries, making the need for escaping data of even more importance.

One final and important consideration is the rights that the user accessing the database has. When you provide your database access details through PHP, you specify a username and password. You should ensure that the website uses a MySQL user account with limited privillidges. To do this, you should create an account and assign privillidges accordingly by following the MySQL documentation. Many web hosts also provide a simple way to do this through the sites control panel. Allowing only select, insert, update and delete should be enough. These only modify the data in the tables as opposed to statements such as create, alter and drop which modify the structure of the database.

The example below is a small one page site which is vulnerable to the attacks mentioned above. The simplist way to get it running is using xampp. Install or extract xampp, place the extracted folder (named insecure) in xampp/htdocs then run xampp_start and visit http://localhost/insecure. If you have changed the MySQL login settings, this will need to be reflected in db.inc.

Insecure login example

What security problems does it show?

  1. The database connection file, with the database connection information is stored in a .inc file. This works fine as when it gets included, PHP evaluates it. However if the user visits the file directly (http://localhost/insecure/db.inc) PHP does not evaluate it as it does not evaluate files with the .inc extension by default. This means anyone can see all our database login information. This file should have had a PHP extension and ideally been placed outside the web folder so it could not be accessed via a URL.
  2. The form does not specify whether it’s a GET or POST operation, so the browser defaults to GET. The PHP script handles this by looking in the GET superglobal rather than POST. However, the use of GET means the password is sent in the URL so it’s easy for anyone watching to see it and the URL including the password will be stored in the user’s history. POST should be used instead.
  3. All error reporting is enabled. Aside from being horrible to look at, the error messages can give an attacker hints about the effect their attacks are having, speeding up the process of compromising the site. Error reporting should be disabled for the live site.
  4. The user data is never escaped, so they can inject into the query. It should be escaped as described above.
  5. The code checks if more than zero results have been returned as the criteria for a successful login. It should check if one and only one result has been returned.
  6. SQL query errors are reported, making the process of finding a successful injection much easier. These errors should never be shown to the user, an ambiguous ‘Login failed’ style message is enough.
  7. The root MySQL account is used, and it has no password. This is handy for the example but very dangerous if it were to be used in practice.

Most of the above are fairly common sense but it’s interesting to see just how many security issues even a tiny site can raise.

This entry was posted in Security. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>