Sweating the Details

In this world of simple hacking tools, lost revenues and broken hearts, it pays to have your bases covered. One of the largest open hazards facing the beginning web programmer is SQL injection attacks, and this article will teach you the basics of safeguarding your fledgling development.

SQL injections tend to hurt, a lot

What is a SQL injection? Injection attacks involve a remote attacker causing your server to execute (often malicious) code inputted through a form on your site. Such attacks usually involve some variation of the following:

1. Your site takes input from a form, and then passes it in as arguments to a SQL query. For example:
   mysql_query("SELECT * FROM myTable WHERE idvalue = '".$id);
   In this case, the $id value is received from an HTML form.
2. The attacker, rather than submitting normal input, submits something like this:
   4' OR 1=1.
3. This causes the submitted query to become: SELECT * FROM myTable WHERE idvalue = '4' OR 1=1. This will cause all of the rows in myTable to be returned, potentially compromising information in the database. What if this were a table of users with personal information?
4. More malicious still, the hacker could enter:
   ';DROP TABLE users;
   This would cause you to lose an entire table.

You can read more about injection attacks at Wikipedia.

A simple cure

The effects of SQL injection attacks can be disastrous. Fortunately, the defense is quite straightforward.

Use mysql_real_escape_string on all inputs that will be placed into SQL statements. This seems to be the most thorough way of cleansing input. This will cause ‘, “, and a few other characters to turn into their backslashed escape versions. Alternatively, you can use other escapement functions such as addslashes, htmlentities and htmlspecialchars.

Never, ever feed anything directly from the user into a SQL query. Not even for testing. Test code has a way of creeping into your release application and opening a security hole.