Avoiding the SQL injection exploit

Photo courtesy of slworking

Nobody likes to be the guy who coded the "simple" website that later compromised a whole server and was used to leech ten-thousands of dollars out of unsuspecting citizens, and thus dragged an innocent company in trouble. Or the explaining that inevitably follows. Having security audited a few custom websites lately, I got the feeling that there is a need for a resource that in a few simple lessons helps web developers make more secure sites and avoid trouble.

This is to be a series on the most "popular" programming errors, delivered weekly. It's focused primarily on PHP, because that's the web's most prominent language, but the concepts (and the errors) are similar in other programming languages, too (I'm looking at you, ASP programmers).

What is SQL injection?

It's called SQL injection when an attacker can rewrite the program's SQL queries. It can be used to access any data in the database without authorization. It can also be used to destroy data, or whole databases.

It's one of the three most trivial, most widespread, most exploitable, and most dangerous programming errors. Fortunately, it's not that hard to avoid.

Consider the following PHP code:

$product = $_REQUEST['id'];
$query  = "SELECT * FROM products WHERE id=$product";
$result = mysql_query($conn, $query);

or, with adodb, this even simpler one:

$product = $conn->getAll("SELECT * from products WHERE id = " . $_REQUEST['id']);

The problem is, what if someone calls your page like this? (And yes, something like this can be easily figured out. This is from an actual security audit where I crafted this URL without knowledge about the site's PHP code or its DB structure.)

http://your-site/products.php?id=2%20union%20select%201,2,3,4,table_name,6%20from%20information_schema.tables

Do you see what is happening? The SQL that your program will send to the database will be like this:

SELECT * from products WHERE id = 2
UNION SELECT 1,2,3,4,table_name,6 from information_schema.tables

It lists all the table names in your database! And now it's easy to see how could one use the same technique to find out column names, then list -- for example -- all the email lists of your users, and spam them all, dragging the company into trouble? (There will be users who are using unique email addresses for every site. There are even services for that.)

And it's actually even simpler to inject some "DROP TABLE ..." or "DROP DATABASE ..." statements, disrupting business while you restore a backup. (You DO have backups of the site's database, do you?) And then again, the attacker will be able to drop the database again with only a browser reload, until you actually fix your code.

How to avoid SQL injection?

It's easy. Just treat every input, especially in SQL statements and program execution (using exec, passthru, etc, I hope you can see now how that could be exploited in a similar way) as hostile, and use PHP's escaping functions on them, like this:

$product = $_REQUEST['id'];
$query  = "SELECT * FROM products WHERE id='" . mysql_real_escape_string($product) . "'";
$result = mysql_query($conn, $query);

or, with adodb, this even simpler one:

$product = $conn->getAll("SELECT * from products WHERE id = " . $conn->qstr($_REQUEST['id']));

There are two things to notice here. One is having quotes around the parameter. If there are no quotes, then not even security patches like Suhosin can protect you! (The audit I quote here was performed on a well-configured, Suhosin patched server.)

Then there is the escaping of malicious characters, like quotes, exactly to avoid breaking out of the aforementioned quotes. Let's suppose $_REQUEST['id'] being "2'; DROP TABLE products; --" for a moment...

Without escaping:

SELECT * from products WHERE id = '2'; DROP TABLE products; -- ';

With escaping:

SELECT * from products WHERE id = '2\'; DROP TABLE products; -- ';

The first one actually drops a table, the second one just returns zero rows.

That's it. Just use quotes, and escaping in every SQL and every shell command.

Of the three most common and most dangerous mistakes SQL injection is the hardest to get your head around, so if you understood this one, the next two ones will be easy! :)

Further reading:

About SQL injection on php.net
SQL injection at the Web Application Security Consortium
About web security exploits on Smashing Magazine