How to create a dynamic prepared statement in Java?

Question

I know using prepared statements helps to avoid sql-injection. My problem is, that a prepared statement is usually very static. I have a problem, where I build the where-clause of the sql-query at runtime, depending on user-input. Depending on which input-fields are filled I have to add the correspending statements to the where-clause. How can this be realized with prepared statements?

Solution 1

I guess you could dynamically build your prepared statements based on what columns they want to query, i.e. use a StringBuffer and a loop to build them based on the required columns.

For efficiency you should keep these in some kind of in-memory lookup. So you'd end up with a Map or other collection of prepared statements where the retrieval key is the columns they're designed to query against.

Solution 2

If you are building the where/and clause(s) in your query based upon what what options the user selected then, I'm guessing, that you already know what changes will have to be made to the query in order get the desired data. If that is the case then you could create method that takes in the option the user asked for as a parameter to the method, the method builds the query and returns that query.

//something similar to the following
public String buildQuery(int option){
  StringBuilder sb = new StringBuilder();
  sb.append("select fields from table");
  switch(option){
    case 1:
     //build query and append to sb
     sb.append("where clause for option1");
    case 2:
     //build query and append to sb
     sb.append("where clause for option2");
    default:
    // build query using default
    sb.append("default where clause");
  }

  return sb.toString();
}
// create the stored procedure
PreparedStatement ps = conn.prepareStatement(buildQuery(2));
ResultSet rs = ps.executeQuery();

Now if you need to use specific user entered values in the query you can store them temporarily in a list or map and then set them.

ps.setString(1,list.get(0));
ps.setString(2,list.get(1));
ResultSet rs = ps.executeQuery();

There's probbaly a more elegeant solution to the problem but we have an application that queries the database for records using the user's input to build the queries and it's been working fine for the last two years. Hope that helps.

Solution 3

If you only want the protection from sql-injection, constructing the query at runtime is OK, as long as you don't insert anything user sent into the query (but inserting clause if user sent at least something in field is ok).

Performance - I don't know. Maybe DB will cache prepared statements, so if you construct exactly same again, it will be faster, or it won't. I dont know how to find out.

Comments

[Jonathan]

From the JavaDocs for PreparedStatement "An object that represents a precompiled SQL statement." Based on that I wouldn't trust the DB to cache them, I'd prefer to cache them myself. If course you could always profile that. But it'd probably be quicker just to implement the cache ;)

[Mnementh]

OK, but that means also I have also to keep the list of parameters with my created sql-query, because the adding of values is only possible with indices. Am I right?

[Jonathan]

Yes, you'd need to keep the parameter column names to do a lookup in your cache, you'd also need to keep their order or have the order predicable in some way (alphabetic?) so you know which "?" to replace with your query data. Hope this is all making sense, SQL isn't my first language :)

[Alpedar]

acording theserverside.com/news/1365244/… the caching can be done by connection pool.

[Erick]

I think the best way of doing dynamic search is using a store procedure. It can get messy when trying to build the query and the parameters using prepared statements. And if you don't use prepared statements, you are vulnerable to injection attacks.