How To Dodge SQL Injection

Recently, a lot of .gov and .edu web sites have been the target of SQL Injection attacks that seed their database with Javascript that does all sorts of nasty things, including targeting an old RealPlayer vulnerability. For all the gory details on this particular incident, I recommend reading the Web Security Blog over at ModSecurity.com for all the details, or the original SANS report of the attacks.

As web developers, the integrity of our databases and our web sites are our livelihood. Attacks like this can severely damage our search engine rankings and infect our customers – neither of which are good for business. These kinds of attacks were bound to happen, because of the lack of understanding concerning SQL Injection, cross-site scripting, and other modern threats that affect the web. So, instead of discussing the impact of these attacks or the specific methodology that was used, I'm going to discuss some ways to prevent this sort of attack from affecting your web site.

Defending Against Web Attacks

The first thing you have to understand is how these attacks work. Again, I'm not going to get into the details (there are many other fine sources for that, just Google it), but nearly all of them require a way to modify your page before a victim receives it. This can be achieved through inserting malicious information into forms or HTTP headers, and this is where the majority of defensive measures can be put.

Filter Input

If you're receiving input from the outside, you need to expect the worse. When it's coming in from the outside, input can consist of absolutely everything. It can be null, it can be paragraphs and paragraphs (especially if posted from a form – the post method allows a lot of text, and HTML limits can be overridden), it can be broken unicode input and it can be PHP, Javascript, and SQL code. Make sure to clean your input of any encoded characters, trim it or reject any input that exceeds the size limits you set. Remove any unallowed tags to prevent people from inserting scripts remotely or modifying the appearance of your page.

I tend to do most of these things in a loop, until there are no more changes found. This prevents malicious input from being specifically designed to stick around after the first pass.

Escape Output

If you properly escape any output that will be used in HTML or inserted into a database, many attacks will lose their effectiveness. If someone was attempting to end your SQL query by inserting a single quote at the end of their input, ending your query, and inserting a new SQL query of their own after that, properly escaping the data you insert into your queries would prevent their single quote from being interpreted by the database.

There are easier ways to safeguard your database operations, like using the PDO class in PHP or using prepared statements, but you should always escape input sent to a database regardless.

Escaping characters used in HTML like < and >, make inserting HTML tags into your document rather hard. If the sites affected by the latest SQL injections had been escaping their output, they would have not automatically inserted the