For all of you developers out there working with databases, most of you (especially if you work in the web field) of SQL injection attacks. These attacks “inject” SQL queries via user input forms or URL string queries. The SQL used in these injections are typically very bad and reek havoc upon the sites that depend on these databases. The most common example of an SQL injection is similar to that found in the “Exploits of a Mom” comic strip by xkcd:

The SQL query in the comic strip finishes the current query then deletes (the “DROP” command for most engines) the named table. The loss would be tremendous.

To fight such attacks, many developers (like myself, up to a few months ago) sanitize the hell out of their user input. However, this can be a pretty painstaking operation. Even if you put it all into an easy-to-call function, you still put in the extra system resources to calling the function.

Much of the sanitizing can be replaced, instead, by using statement parameterizing. It’s just a fancy way of saying let the database handle putting in the data. Using the MySQLi class in PHP (for instance), you can parameterize the SQL statement (mysqli::prepare). MySQL takes care of putting the variables into the statement.

Almost any language with database support (and support of prepared statements by the database, of course) supports parameterized statements. Check out Bobby-Tables.com, an online resource to preventing SQL injection. It takes a look how to parameterize statements using different languages (including PHP, dotNet, and Python). It’s a very good work in progress, in my opinion.

Do you know of any good resources or methods for  parameterizing statements or avoiding SQL injection attacks? Let a comment!