0

I'm implementing dynamic search within my application, I have the following options to build a query.

  1. String concatenation from the user input
  2. Use multiple Queries, and pull the right query based on the user input
  3. Use one query, use wild cards for the inputs not given by the user.

eg: 

select * from A,B where a.id like nvl( {input}, '%')
and a.id = b.aid
and b.value like nvl({input2},'%');

Because id is a primary key I get the following error in the oracle when tried.

Zeus
  • 6,386
  • 6
  • 54
  • 89
  • Don't go with option 1 as your application will be vulnerable against attacks such as SQL injection all the way up to Cross site scripting. Also validate your input before using the variables in your query. – Rami Del Toro Aug 20 '14 at 21:11
  • Regarding wildcards if i understand your issue, it seems a Java PreparedStatement might be a good fit for this situation. – Rami Del Toro Aug 20 '14 at 21:20

2 Answers2

2

Firstly, for wildcard search you need to use the LIKE predicate, not =. Secondly, you can't use the LIKE predicate for numeric data, obviously. What you can do is this:

select * from A,B where ( a.id = {input} or {input} is null )...
mustaccio
  • 18,234
  • 16
  • 48
  • 57
0

A simple solution could be:

StringBuffer sqlSB = new StringBuffer("select * from A,B where a.id = b.aid ");
if(input!=null&&!input.equals("")){
    sqlSB.append(" and a.id = ").append(input);
}
if(input2!=null&&!input2.equals("")){
    sqlSB.append(" and b.value = '").append(input2).append("' ");
}
peterpeng
  • 43
  • 5