PHP Tip: Prevent SQL Injection

SQL Injection is one of the most common exploits. It’s a sneaky technique that takes advantage of unsafe database querying practices to gain access to the database.

Suppose you have a input form that asks for an email address for a newsletter subscription. The data is passed to the script, which inserts the data with the following:

$input = $_POST['email'];
mysql_query("INSERT INTO emails (email) VALUES('$input')");

Looks fine at a glance, doesn’t it? Well, it would if you’re new to the horrors of SQL injection. Note that the form field’s data is passed right along without any validation. That is not good. Some contempt-worthy person could come along and type something like this into the form:

blah@example.org'); DROP TABLE emails;

This would insert a dummy email, then delete the whole database table. Oops.

How can you protect yourself from SQL Injection? The first step is to validate your data. You’re expecting an email address to be submitted, right? So why don’t you make sure the submitted data looks like an email address? You could use regular expressions (or something) to make sure the string is a substring followed by a “@” followed by another substring, and make sure there aren’t any characters that wouldn’t be valid in an email address.

The next step is to use the mysql_real_escape_string() function to remove any escape characters from the string, to make sure there are no unpleasant surprises in the input string. The PHP function reference recommends that you do this any time you query the database with information from a user.

SQL Injection is definitey something you need to be aware of. Do some Google-ing and read up on it. The worst that could happen is having no one try to hack the script you spent time securing.