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...


Christoffer "Kreiger" Hammarström said...

No, no, no!

You do not EVER concatenate user inputed data into sql strings, and try to sanitize it.

Use placeholders (put a questionmark "?" where the data goes).

Gabriel C. said...

Yep, that's recommendation #1.
But in rare cases you have an undefined number of parameters and is not practical to use parameters.
IN clauses can be an example, although you're right: you can "unroll" it in a series of " OR value=? " ... I guess at that point is a trade off...

Christoffer Hammarström said...

No, that's against recommendation #3.

If you have an undefined number of parameters, you build your statement with one placeholder for each, and set the value for each.

Christoffer Hammarström said...

Just build the in clause dynamically with placeholders: "... IN (?,?,?,?,?,?) ..."

Gabriel C. said...

You're totally right and I like the approach.
The only drawback is that in some complex query cases, it can make the code even more complex (I guess is the price to pay for safety).
I imagine you need to keep a list of parameter values, and for every "?" you add to the query, you add the value to the list, then compile the statement and apply the parameters

Christoffer Hammarström said...

Exactly right.

Chrisranjana said...

Also if it is possible at all allow ONLY valid post or GET variables Maybe check from an allowed list everytime ?

Web Development Company

domain web hosting said...

SQL Injection is subset of the an unverified/unsanitized user input vulnerability and I actually have alot of experience updating older apps to use PreparedStatements, it is fairly easy except if you are using alot of dynamic statements. Still this process is time consuming, and the idea is to convince the application to run SQL code that was not intended. If the application is creating SQL strings naively on the fly and then running them, it's straightforward to create some real surprises.