10/22/08

Simple Java tricks to protect your web application against SQL injection

Your application is vulnerable to SQL Injection when you send unfiltered strings to the database. Most modern ORM frameworks should take care of it (but don't take my word!... go ahead and check how secure your framework is).
Sometimes, you have to work with plain JDBC (or ODBC). Here is a couple of tricks that help:
  1. First and foremost, avoid concatenating strings for SQL queries. Use prepared statements unless is not possible (i.e. cases when you have undefined number of parameters)
  2. Leverage the language type system: If you're passing a number, use Integer instead of String... any invalid character will fail the conversion and will not reach the DB.
  3. If there's no option but concatenate strings, make sure the database comment quotes are escaped (for example, in DB2 you have to replace the single quote character with 2 single quote characters: instead of "SELECT * FROM users WHERE name='"+param+"'" use "SELECT * FROM users WHERE name='"+param.replaceAll("'","''")+"'"
For something a little more advanced, you can wrap the strings in some kind of "EscapedString" class, and use that class in the signature of the DAOs (related to 2. )

Note: by no means this is a comprehensive list. Application security is very hard, check your database documentation...