The Problem

Your website is vulnerable to hackers unless you take steps to prevent them exploiting techniques such as SQL Injection and other attacks.

What is SQL Injection

This is simply a technique of adding SQL code into a field in such as way that it executes where you don't expect. An example is probably the best explanation.

You have a simple form that asks for a user name and password. This form is posted and you have a page that reads the form fields (lets call them username and password). You run a simple SQL to check that they have supplied valid credentials (this is just a simple example, you shouldn't ever store unencrypted passwords in a table):

sql = "SELECT COUNT(*) FROM MY_USERS WHERE USERNAME='$username' and PASSWORD='$password'";

Now suppose that instead of the fields username and password containing plain values, such as "SCOTT" and "TIGER", they contained the strange values "a' OR ('b'='b" and "c' ) OR 'd'='d". If unchecked, this would give you the following SQL string:

sql = "SELECT COUNT(*) FROM MY_USERS WHERE USERNAME='a' OR ('b'='b' and PASSWORD='c' ) OR 'd'='d'";

This is always going to evaluate as true, allowing the hacker to log into your site. What could be worse is that they could also execute a SQL command. I expect a "DELETE FROM" command could be a problem for you if they got lucky with the table name, as would a "DROP TABLE". Even a simple query that caused your database to get stuck in a loop would crash your server.

The Solution

It's very simple - you just need to test everything that you get from the browser, even hidden fields. Luckily PHP does a lot of the work for you in a single function:

$username = htmlspecialchars( $_POST['username'], ENT_QUOTES);

What we're doing is not only to strip out any html characters (thereby guarding against html and javascript attacks) but the addition of the ENT_QUOTES parameter means that single quote characters are stripped out. In the example above that would reduce the hackers attack to:

sql = "SELECT COUNT(*) FROM MY_USERS WHERE USERNAME='a OR (b=b' and PASSWORD='c ) OR d=d'";

This is sufficient to ward off many attacks. You need to think about the database you use and consider any other vulnerabilities in it's SQL syntax. Find out if it is possible to execute two commands in one statement. Is it possible to deliberately break a SQL statement by inserting certain characters? What error handling have you got in place to deal with exceptions?

Security is a real pain, but having your site hacked and perhaps losing your entire database is much, much worse. Not only that, but you've got to explain to your customers how you cut corners. It takes longer to recover your reputation than it does to restore your site from backup (assuming you have one).

Patrick Haston
27 October 2008