May 13, 2013

SQL Injection

There are 72,393 examples of SQL injection vulnerable code on GitHub.

Everyone that has ever learned to use a database in PHP probably learned using the mysql* functions. It's quick, easy, requires only one line to connect and it works pretty much anywhere that runs PHP and MySQL; what's not to like? The problem is when developers migrate from learning and tutorials to writing websites in the real world. Granted, you will probably never find mysql* calls with inline $GETs at any place of business that's even half serious about their website, but any hobbyist or developer that wants to put a website online using PHP and decides that they need to use a database will find 90% of the tutorials using mysql* . These tutorials may not even mention the security implications of using mysql_* and concatenating variables (if they do mention the security implications, assuming the reader even reads the text instead of just copy and pasting the code, they may not explain how to deal with them). You may be thinking, so what, a website nobody visits gets owned, big deal right? Well what happens when the site is on a shared host with 100s or 1000s of other sites and the attacker manages to escalate their privileges? What happens when some of those websites contain personal information, passwords and credit cards?

The best part? A result on page 1 that points to this repo has the following snippet:

<?php
    mysql_query("DELETE FROM shops1 WHERE id='$_GET[id]'");
?>

If a user was on that page and submitted a form or hit the url

/deleteshops.php?id=1%27%20OR%201%3D1%20--%27

such that $_GET['id'] was

1' OR 1=1 --'

then the query becomes:

DELETE FROM shops1 WHERE id='1' OR 1=1 --''

Boom, shops1 is empty, hope there wasn't anything important in it...like student records...

Jeff Atwood has a very short and to the point post about several of the reasons why you should be using parameterized queries. mysql* functions don't support prepared statements or parameterized queries, not to mention they will be removed in a future version of PHP, so don't use them! If you think that's too much work and don't want to learn mysqli or PDO, take a look at RedBean it's even easier then writing a string with a call to mysqlquery.