Sanitizing SQL input in Java when using LIKE

Question

We all know that in Java you are supposed to use PreparedStatments to avoid security flaws.

However, I have noticed that when using LIKE statements to do a "starts with" search, PreparedStatments can cause performance problems. Because the statement is compiled without the value, the driver must assume that the wildcard could be anywhere, including the beginning so it skips any indexes on that column. But if you compile the statement with the LIKE value as a literal, it sees that it can use the column's index on the value up until the wildcard and the query performs well.

//safe, but prepared statement can't use index on MYCOL
String userInput = "myval%";
statement = conn.prepareStatement("SELECT * FROM MYTABLE WHERE MYCOL LIKE ?");
statement.setString(1, userInput);
rs = statement.executeQuery();


//can use index on MYCOL, but is not safe
statement = conn.createStatement();
rs = statement.executeQuery("SELECT * FROM MYTABLE WHERE MYCOL LIKE '"+userInput+"'");

So how do I avoid the security flaws while keeping the index? I'm assuming that I'll have to try to sanitize the input instead of using a PreparedStatement -- if so, does a library exist to do it correctly? Or is there same java.sql or (oracle) database hint way of telling the prepared statement that it will be able to use the index on the value?

Answer 1

Have you considered creating a stored_procedure on the database side? That way you can just send the stored_procedure name and the like string as a parameter to the database, and work with the result set on the java side. This way, you know you query will use indexes of the table you are working on.