How to protect against SQL injection when the WHERE clause is built dynamically from search form?

Question

I know that the only really correct way to protect SQL queries against SQL injection in Java is using PreparedStatements.

However, such a statement requires that the basic structure (selected attributes, joined tables, the structure of the WHERE condition) will not vary.

I have here a JSP application that contains a search form with about a dozen fields. But the user does not have to fill in all of them - just the one he needs. Thus my WHERE condition is different every time.

What should I do to still prevent SQL injection?
Escape the user-supplied values? Write a wrapper class that builds a PreparedStatement each time? Or something else?

The database is PostgreSQL 8.4, but I would prefer a general solution.

Thanks a lot in advance.

Answer 1

Have you seen the JDBC NamedParameterJDBCTemplate ?

The NamedParameterJdbcTemplate class adds support for programming JDBC statements using named parameters (as opposed to programming JDBC statements using only classic placeholder ('?') arguments.

You can do stuff like:

String sql = "select count(0) from T_ACTOR where first_name = :first_name";
SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", firstName);
return namedParameterJdbcTemplate.queryForInt(sql, namedParameters);

and build your query string dynamically, and then build your SqlParameterSource similarly.