SQL injection is a common, although becoming less so, type of vulnerability that is occasionally found in websites that use SQL (Server Query Language) to access data at the server-side. If the code used to query the database doesn't correctly escape the data passed to it, it may be vulnerable. This article will explain the vulnerability, and then explain how to secure your code against it.

The vulnerability

To understand this concept, you need to have a basic concept of the SQL language. I will not be explaining that here: I'm teaching you how to write secure code, not to exploit existing code, and so am assuming that if you don't know how to write SQL then you are not using it and so don't need to secure it.

It's easiest to explain the vulnerability through an example or two. We'll start off with some vulnerable code:

<?php

// VULNERABLE CODE

include('database_connect.php'); // Connect to the database
$sql = "SELECT *
    FROM app_users
    WHERE username='" . $_POST['user'] . "'
        AND user_password='" . $_POST['pass'] . "'";
$query = mysql_query($sql);

I'm using the old mysql_* library for these examples as PDO isn't usually vulnerable; it supports prepared statements (although if used incorrectly, it can still be vulnerable), which I will explain later in the article.

As you can see, the code is checking whether the user credentials sent from a form on the previous page are valid. If the credentials are valid, it will return the user details. If they're invalid, it won't return anything. However, the code is vulnerable: if the user enters a string like ' OR ''=' into the password field, the following SQL will be generated:

SELECT *
FROM app_users
WHERE username='Callum'
    AND user_password='' OR ''=''

That will return true whether the password is correct or not, allowing the attacker to log in as any user they want to without knowing the password.

This, obviously, is a problem. That was just a basic injection, other injections are possible - for example, we can attempt to guess table names, the username of the user that is currently running MySQL (using USER_NAME() - MySQL only), and with more complex injections, view entire dumps of tables (although not with the previous code example).

The fix

The easiest fix for this is to stop using the deprecated mysql_* library. There are far better alternatives available, such as PDO and MySQLi. Both of those examples support prepared statements, which also mean that you don't have to escape your data. For more information and a few examples, see my article on PDO: PDO Basics.

If you cannot stop using the mysql_* library, then there are a few functions available to secure your code from SQL injections. The recommended way is to use mysql_real_escape_string(), which simply returns an escaped version of the same string:

<?php

include('database_connect.php'); // Connect to the database

$username = mysql_real_escape_string($_POST['user']);
$password = mysql_real_escape_string($_POST['pass']);
$sql = "SELECT *
    FROM app_users
    WHERE username='$username'
        AND user_password='$password'";
$query = mysql_query($sql);

That code is secure.

If you're using integers, then simply calling mysql_real_escape_string() won't do the trick - as 0 OR 0=0 doesn't contain any quotes, nothing will be changed; the following code is still vulnerable:

<?php

include('database_connect.php'); // Connect to the database

$userid = mysql_real_escape_string($_POST['id']);
$sql = "SELECT *
    FROM app_users
    WHERE user_id=$userid";
$query = mysql_query($sql);

Passing 0 OR 0=0 to it will generate the following SQL:

SELECT *
FROM app_users
WHERE user_id=0 OR 0=0

In order to combat this, we must cast the variable to integer, like this:

<?php

include('database_connect.php'); // Connect to the database

$userid = (int) $_POST['id'];
$sql = "SELECT *
    FROM app_users
    WHERE user_id=$userid";
$query = mysql_query($sql);

In summary, SQL injections are a very real and potentially very dangerous type of exploit, that could lead to a lot of damage being caused, such as data being deleted or a table containing sensitive user information such as passwords, emails or credit card information being released. They are moderately easily prevented by using prepared statements in a better library such as PDO, or using a function like mysql_real_escape_string() to escape input.