CODEWITHSUNDEEP
javasqlprepared-statement
Mnementh
Mnementh

Reputation: 51311

How to create a dynamic prepared statement in Java?

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?

Upvotes: 2

Views: 18824

Answers (5)

saravana
saravana

Reputation: 1

Declare one method and pass the userinput through that method. Then use setString or setLong depending on your input data type within the preparedstement object.

Upvotes: -1

ChadNC
ChadNC

Reputation: 2503

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.

Upvotes: 3

Carlos Gavidia-Calderon
Carlos Gavidia-Calderon

Reputation: 7253

If you're using Hibernate, Criteria Queries API is a nice tool for avoiding String concatenation to build SQL Queries. With plain JDBC, you'll have to go with a StringBuffer as @Jhonatan said.

Upvotes: 0

Alpedar
Alpedar

Reputation: 1344

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.

Upvotes: 2

Jonathan
Jonathan

Reputation: 1367

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.

Upvotes: 3

Related Questions