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: 
Have you seen the JDBC [NamedParameterJDBCTemplate](http://static.springsource.org/spring/docs/2.0.x/api/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplate.html) ?

> 
> 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.
Here is a useful technique for this particular case, where you have a number of clauses in your `WHERE` but you don't know in advance which ones you need to apply. 

Will your user search by title?

```
select id, title, author from book where title = :title

```

Or by author?

```
select id, title, author from book where author = :author

```

Or both?

```
select id, title, author from book where title = :title and author = :author

```

Bad enough with only 2 fields. The number of combinations (and therefore of distinct PreparedStatements) goes up exponentially with the number of conditions. True, chances are you have enough room in your PreparedStatement pool for all those combinations, and to build the clauses programatically in Java, you just need one `if` branch per condition. Still, it's not that pretty.

**You can fix this in a neat way by simply composing a `SELECT` that looks the same regardless of whether each individual condition is needed.**

I hardly need mention that you use a `PreparedStatement` as suggested by the other answers, and a [NamedParameterJdbcTemplate](http://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplate.html) is nice if you're using Spring.

Here it is:

```
select id, title, author
from book
where coalesce(:title, title) = title
and coalesce(:author, author) = author

```

Then you supply `NULL` for each unused condition. `coalesce()` is a function that returns its first non-null argument. Thus if you pass `NULL` for `:title`, the first clause is `where coalesce(NULL, title) = title` which evaluates to `where title = title` which, being always true, has no effect on the results.

Depending on how the optimiser handles such queries, you may take a performance hit. But probably not in a modern database.

(Though similar, this problem is *not* the same as the `IN (?, ?, ?)` clause problem where you don't know the number of values in the list, since here you *do* have a fixed number of possible clauses and you just need to activate/disactivate them individually.)