Prepared statement with dynamic where clause

Question

I have a search page with multiple search criteria

Employee Name
Employee Id
Date of joining
Department

etc

User can provide one or more search criteria. I need to query database to get the search results.

Using plain JDBC, there are two options to achieve this.

Prepare SQL query by appending search criteria provided by user.

ex:

String selectClause = "SELECT * FROM EMPLOYEES WHERE ";
String whereClause = "";
if(StringUtils.isNotBlank(empName)){
    if(whereClause.length > 0){
        whereClause += " AND ";
    }
    selectQuery += " EMP_NAME = " + empName;
}
if(StringUtils.isNotBlank(empID)){
    if(whereClause.length > 0){
        whereClause += " AND ";
    }
    selectQuery += " EMP_ID = " + empID;
}
//... and so on ...

Using preparestatement

ex:

String query = "SELECT * FROM EMPLOYEES WHERE EMP_NAME = ? AND EMP_ID = ? DATE_OF_JOINING = ? AND DEPARTMENT = ?";

This answer explains that like ex 1 above, ex2 can be modified, something like below

String selectClause = "SELECT * FROM EMPLOYEES WHERE ";
String whereClause = "";
if(StringUtils.isNotBlank(empName)){
    if(whereClause.length > 0){
        whereClause += " AND ";
    }
    selectQuery += " EMP_NAME = ?";
}
if(StringUtils.isNotBlank(empID)){
    if(whereClause.length > 0){
        whereClause += " AND ";
    }
    selectQuery += " EMP_ID = ?";
}
//... and so on ...

Then carefully (keeping parameter index in mind) the input needs to set to the prepared statement. This doesn't sounds to be a very ideal solution.

Is there a way to do this in an elegant way (without ORM frameworks) ?

Your first approach can *easily* lead to SQL injection attacks if you are not extremely careful. — Bruno Reis, Mar 14 '13 at 09:24

score 4 · Accepted Answer · answered Mar 14 '13 at 09:25

4

I wouldn't like using a StringBuilder to dynamically create a query each and every time, especially when the number of meaningful combinations is countable and finite.

I'd always prefer static Strings. Yes, you have to type them in, but you'll do that once. I'd rather do that than pay the price in complexity and at runtime.

answered Mar 14 '13 at 09:25

duffymo

305,152
44
369
561

3

You mean I should have static queries ready for each combination of inputs ? Currently I have four inputs, so queries for combination of the four inputs ? Going forward there may be more inputs. Won't this complete things ? – Apurv Mar 14 '13 at 09:27
Yes, I would type they out. I'd say that a touch typist can bang out 10 queries without too much trouble. All combinations aren't equally valid. I'd say that you'd be better off thinking about the ones that users are most likely to really use and just implement them once statically as PreparedStatements. You could probably have them coded in the time it takes to debate the answer here. – duffymo Mar 14 '13 at 09:34
Won't it be unfair with my employer to not follow the recommended approach ? – Apurv Mar 14 '13 at 10:15
I don't know what that comment means, and I'm not sure on whose authority the recommended approach is assumed to be best. You owe it to yourself and your employer to produce the best application you can based on several critiria that may conflict: performance, readability, maintainability, security, etc. – duffymo Mar 14 '13 at 11:59
I am not speaking against you or your approach. I have 100% faith in the approach suggested by you. I am trying to know and evaluate all the suggested alternatives. – Apurv Mar 14 '13 at 12:04
Thanks a lot. Looks like this is the only option for this scenario. – Apurv Mar 19 '13 at 03:55

score 4 · Answer 2 · edited May 23 '17 at 12:13

4

In such conditions I prefer adding 1=1 in where clause so that you dont have to keep track of where to insert AND.

String selectClause = "SELECT * FROM EMPLOYEES WHERE 1=1 ";
if(StringUtils.isNotBlank(empName)){
   selectQuery += "AND EMP_NAME = " + empName;
}
if(StringUtils.isNotBlank(empID)){
   selectQuery += "AND EMP_ID = " + empID;
}
//... and so on ...

Related question.

edited May 23 '17 at 12:13

Community

1
1

answered Feb 16 '14 at 06:11

Ajinkya

22,324
33
110
161

1

Although 1=1 is a very convenient way of adding all the 'ANDs', be aware that some RDBMS engines/optimisers go bonkers and your query performance may become very bad. I add all the conditions into an List and join them with an AND. Something like `String whereClause = StringUtils.join(conditionsList, " AND ");` – Yogi Jul 03 '15 at 12:46
@xyz What if we have some condition with `OR` clause also? – Piyush Feb 29 '16 at 13:37
@Piyush then use `OR` instead of `AND` like `selectQuery += "OR.. ` – Ajinkya Feb 29 '16 at 14:06
@xyz Then all conditions will become useless because `1=1 OR some_condition` will be always `true`. – Piyush Feb 29 '16 at 14:50
3

this example is vulnerable to SQL injection as mentioned in the comments above by @Bruno Reis. How would we accomplish this in an easy way using Prepared Statements? – Erick Apr 02 '16 at 16:44

John Joseph · Answer 3 · 2017-01-09T16:35:12.483

4

This is easy to do without any complex or expensive logic, in a single line...

Assuming that your three variables are @name, @surname, and @gender.

Also assuming that a zero-length string will be provided when a filter is not required.

Then, your Select statement is simply:

    select * from table_name where (name = @name or @name = '') and (surname = @surname or @surname = '') and (gender = @gender or @gender = '')

That's all there is to it! No complex or expensive logic.

edited Jan 09 '17 at 16:35

answered Jan 09 '17 at 16:30

John Joseph

1,003
1
10
20

1

Is this a JDBC prepared statement ? What SQL is generated if name isn't a required parameter ? – andyd Feb 12 '18 at 09:19

Prepared statement with dynamic where clause

3 Answers3

Linked