How to create a SQL multiple search statement where all parameters are optional?

Question

I would like to know if there is any smart way to make an SQL statement for a search engine where there are 5 optional parameters. All parameters can be used or only one of them or a combination of any of them. This amounts to up to 3,000+ different combinations.

A statement must be prepared to avoid SQL injection.

I looked at this post, but it has shrunk quite a bit.

What I'm looking for is something like

String sql = SELECT * FROM table WHERE (optional1) =? And (optional2) =? And (optional3) =? And (optional4) =? And (optional5) =?

prepare.setString (1, optional1) and so on ...

Answer 1

Use your Java code to add parameters to the where clause based on the presence of your arguments (their length or existence, depending on what you actually have). Thus, if an optional parameter is not needed, it will not even be part of your SQL statement. Plain.

Answer 2

I believe it should work (not tested)

SELECT * FROM table WHERE field1 = CASE WHEN ? IS NULL THEN field1 ELSE ? END AND field2 = CASE WHEN ? IS NULL THEN field2 ELSE ? END AND .... etc //java code if ([optional1 is required]) { prepared.setString(1, optional1) ; prepared.setString(2, optional1) ; } else { prepared.setNull(1, java.sql.Types.VARCHAR) ; prepared.setNull(2, java.sql.Types.VARCHAR) ; } 

and etc.

Answer 3

@ a1ex07 gave the answer for this as one request. Using NULL and checking them in each condition.

 WHERE table.x = CASE WHEN @x IS NULL THEN table.x ELSE @x END 

or...

 WHERE (@x IS NULL OR table.x = @x) 

or...

 WHERE table.x = COALESCE(@x, table.x) 

etc. etc.

However, there is one warning; How convenient it is to make one request, to do all this, all these answers are suboptimal. Often they are boring.

When you write ONE request, only one execution plan is created. And this ONE execution plan should be suitable for ALL possible combinations of values. But this fixes which indexes are being looked up, what order they are looking for, etc. This gives the worst plan for requesting one size for all.

Instead, you better add conditions as needed. You still parameterize them, but you do not include the condition if you know that the parameter is NULL.

This is a good link explaining this further, it is specifically for MS SQL Server, but it is usually applied to any DBMS that caches plans after compiling SQL.

http://www.sommarskog.se/dyn-search.html