Java / JDBC - Multiparameter Search Engine Optimization

Question

Im using the following code to generate search results from a relational database, depending on several (optional) search parameters from the web client.

I am currently using " java.sql.Statement " to achieve functionality, but I need this to be achieved using " java.sql.PreparedStatement " to prevent SQL injection.

Let me know how to change the code.

---

eg.

User logins from the web client.

• param1 - Optional
• param2 - Optional
• dateParamFr - optional
• dateParamTo - optional

Pseudocode of SQL templates depending on search parameters as follows

 IF (WITHOUT ANY SEARCH PARAMETER){ SELECT * FROM TEST_TABLE; } ELSE IF(WITH param1){ SELECT * FROM TEST_TABLE WHERE COLUMN1= param1; } ELSE IF(WITH param1 & param2){ SELECT * FROM TEST_TABLE WHERE COLUMN1= param1 AND COLUMN2= param2 } SO ON ……… 

Below is a snippet of Java code in my EJB

  /* NOTE : Hashtable pSearchParam is a method parameter */ Connection cnBOP = null; Statement stmt = null; StringBuffer sb = new StringBuffer(""); try { cnBOP = jdbcBOP.getConnection(); // DataSource jdbcBOP stmt = cnBOP.createStatement(); /* ######################## SQL BODY ######################################*/ sb.append("SELECT COLUMN1, COLUMN2, DATE_COLUMN "); sb.append("FROM TEST_TABLE "); /* ######################## SQL WHERE CLAUSE ##############################*/ if(pSearchParam.size()>=1){ sb.append("WHERE "); Enumeration e = pSearchParam.keys(); int count =0; while(e.hasMoreElements()){ if (count >=1) sb.append("AND "); String sKey = (String) e.nextElement(); if (sKey.equals("param1")) sb.append ("COLUMN1 ='"+pSearchParam.get(sKey)+"' "); else if (sKey.equals("param1")) sb.append ("COLUMN2 ='"+pSearchParam.get(sKey)+"' "); else if (sKey.equals("dateParamFr")) sb.append ("DATE_COLUMN >= TO_DATE('"+pSearchParam.get(sKey)+" 00:00:00','DD/MM/YYYY HH24:MI:SS') "); else if (sKey.equals("dateParamTo")) sb.append ("DATE_COLUMN <= TO_DATE('"+pSearchParam.get(sKey)+" 23:59:59','DD/MM/YYYY HH24:MI:SS') "); count ++; } } /* ######################## SQL ORDER BY CLAUSE ############################*/ sb.append("ORDER BY DATE_COLUMN DESC"); ResultSet rs = stmt.executeQuery(sb.toString()); 

Answer 1

Instead

 sb.append ("COLUMN1 ='"+pSearchParam.get("param1")+"' "); 

You will need to do

 sb.append ("COLUMN1 = ? "); 

and then after creating the instructions you do

 stmt.setString(1, pSearchParam.get("param1")); 

This is only for the first parameter, you need to do this for all operators and list the index in

 setString(int index, String param); 

Note that you will need to use other methods for int, long, Date ... etc.

Answer 2

Depending on the database engine, you may use SQL functions such as

 isnull(value,valueIfNull) 

e.g. in MSSQL

 select * from Order where storeId = isnull(?,storeId) 

next in your java code

 preparedStatement.setNull(1,java.sql.Types.INTEGER) 

if you need to omit this parameter from the filter or

 preparedStatement.setInt(1,20) 

if you need to find all orders with storeId = 20

Answer 3

It really looks like a job for Requests for hibernation criteria ...

Criteria is a simplified API for extracting entities by compiling Criteria Objects. This is a very convenient approach for functionality like "search" screens, where there is a variable number of conditions placed in the result set.

Answer 4

Are you using hibernate? Then you can use the API criteria . Otherwise, you can take a look at the SqlBuilder tool for generating conditional SQL queries.

You must also use the "?" instead of the actual values.

So this query should be like this.

 SELECT * FROM TEST_TABLE WHERE COLUMN1= ?; 

You can then use PreparedStatements to set values ​​for this column. An introductory tutorial on using PreparedStatement is here .