First let’s have a look at what SQL injection is about. SQL injection means that an attacker is injecting some pieces of SQL code in a call to a server instead of just sending some text information in order to go around security mechanisms or in order to perform something which shouldn’t be allowed.

Here’s a very simple example. Let’s say you have a very poorly programmed login function which is called with two parameter, a user name and a password. If you take the parameters and built an SQL statement like this:

$query = "SELECT 1 FROM users WHERE user_id='".$user_name."' AND password='".$password"'"

An attacker may send the following:

• User name: admin' —
• Password: anything

The generated SQL query would be:

SELECT 1 FROM users WHERE user_id='admin' --' AND password='anything'

The double dash would make the rest of the line a comment and the statement would always return 1 allowing the attacker to login as admin without valid credentials.

An easy fix for this security issue is not to return 1 but to return an MD5 of the password and compare it with the password provided. Unfortunately, it is also trivial to workaround such security fixes. Let’s say you statement now looks like this:

SELECT password FROM user WHERE user_name='xxx'

All the attacker has to do is to use the following username: admin' AND 1=0 UNION SELECT 'known_md5_checksum

But SQL injection is not only used to be able to login without credential. It can be used to perform actions which are not intended to be allowed. This is typically done by using batched queries i.e. closing the first query and having a second query executed which would either return sensitive information or destroying something e.g. using a user name like: admin'; DROP TABLE important_table —

The first statement will be executed normally and the drop table will then be executed additionally. Fortunately, when using PHP and MySQL these kind of batched queries are not supported. The execution will fail since you can only have one statement executed at a time. But it you used PostgreSQL instead of MySQL it would be possible.

Another thing which is often done using SQL injection is getting access to data in the database which should be protected. This is usually done using a kind of UNION injection. The idea behind it is that:

• the server you are attacking is fetching data using a query and displaying this data in a tabular form
• you inject a UNION clause to fetch data from another table
• the data from both table are displayed in the table on the client

Let’s say you have an order table and you can call the server to display all items in a particular order with such a statement:

$statement = "SELECT name, value FROM items WHERE order_id=".$order_id;

If the attacker now sends the following as order_id, he will get a list of all users and their passwords: 1 UNION SELECT name, password FROM users

I do hope the passwords will be at least encrypted but encryption alone is not always enough to protect data. You also need to make sure the encrypted data cannot be accessed that easily.

Of course an attacker will need to know which kind of statements are executed by your software in order to exploit such a security hole. But it might not be as difficult as you think… I’ll post another article about how you can manage to get information about the query being executed later.

Many database engines also provide the functionality to execute commands in the operating system from SQL. The commands are executed using the user running the database engine. It’s sometimes very useful but in an SQL injection scenario it may allow an attacker to not only steal information or damage the database but also the operating system. Fortunately, in our case the xp_cmdshell command used to do this (MSSQL Server and Sybase) does not exist in MySQL.

So what is to be done to protect yourself against SQL injection attacks ?

First if you use MSSQL Server or Sybase: to prevent an attacker from destroying the whole server or prevent him from getting access to any file on the computer, you should disable xp_cmdshell or run the database engine with a user with very limited access rights to the rest of the system.

Now, let’s get back to MySQL and PHP. Here you should use Use prepared statements and parameterized queries using PDO or Mysqli. These statements are sent to the database engine and are parsed independently of anything else. Like this it is not possible to inject SQL code in a parameter.

An example using Mysqli:

$stmt = $db->prepare('SELECT password FROM users WHERE user_name = ?');
$stmt->bind_param('s', $user_name);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // compare the returned value with the MD5 sum of the provided password
}

And using PDO:

$stmt = $pdo->prepare('SELECT password FROM users WHERE user_name = :user_name');
$stmt->execute(array('user_name' => $user_name));
foreach ($stmt as $row) {
    // compare the returned value with the MD5 sum of the provided password
}

Note that PDO is an extension but it is bundled by default since PHP 5.1 and a MySQL driver is also available by default.

This is basically the best way to secure your software. In case you cannot use PDO or Mysqli, there are other techniques to prevent SQL injection attacks. I’ll list them in an update to this post in a few days.