% sign in Java's PreparedStatement

Question

PreparedStatement ps = con.createStatement("select * from table1 where last_name like ?");
ps.setString(1, "'%"+lastName+"'");

Will this work the same as...

Statement s = con.createStatement("select * from table1 where last_name like %"+ lastName);

Or does PreparedStatement strip out the % sign?

Answer 1

% is a wildcard character (in Oracle at least), so in theory both should work the same (assuming you add the missing single-quotes)

However, the first would be regarded as better practice, since it may enable the database optimser not to re-parse the statement. The first should also protect you against SQL injection whereas the second may not.

Answer 2

The second one won't work because you forgot the quotes around the string! next to that you need to escape and be careful for sql injection.

Assume SQL

lastName = "and a quote' or a bracket()";
Statement s = con.createStatement("select * from table1 where last_name like '%"+ lastName + "'");

the resulting SQL is:

select * from table1 where last_name like '%and a quote' or a bracket()'

which will fail

Binding variables make it always safer to work with.

Answer 3

We often use the first approach without issue. For example:

String sql = "SELECT * FROM LETTER_BIN WHERE LTR_XML Like ' (?) ' AND LTR_BIN_BARCODE_ID = (?)";
try
{
    // Cast a prepared statement into an OralcePreparedStatement
    opstmt = (OraclePreparedStatement) conn.prepareStatement(sql);
    // Set the clob using a string
    opstmt.setString(1,fX.toString());
    // for this barcode
    opstmt.setLong(2,lbbi);
    // Execute the OraclePreparedStatement
    opstmt.execute();
} catch(java.sql.SQLException e)
{
    System.err.println(e.toString());
} finally
{
    if(opstmt != null)
    {
     try
     {
      opstmt.close();
     } catch(java.sql.SQLException ignore)
     {
      System.err.println("PREPARED STMT ERROR: "+ignore.toString());
     }
    }

}

Answer 4

Short answer is: Yes, assuming you fix the quoting, the two should give the same results. The percent sign will not be "stripped out" of a prepared statement, any more than any other character would be.

Longer answer: The issue of prepared statement vs single-use statement can be complex. If you're only going to execute it once, a prepared statement will take longer, because the database engine has to do all the setup for a prepared statement, then insert the values, then have it floating around in a cache until the engine decides to flush it. Also, the optimizer often can't process a prepared statement as efficiently. The whole point of a prepared statement is that the optimizer parses the query and devises a query plan once. Suppose you say something like "select customer_name from customer where customer_type=? and customer_zip=?". You have indexes on both type and zip. With a single-use statement (with real values filled in rather than question marks, of course), the query optimizer in many database engines can look at statistics on distribution of values for the two fields, and pick the index that will give the smaller set of records, then read all of these sequentially and eliminate the records that fail the second test. With a prepared statement, it must pick the index before knowing what values will be supplied, so it may pick the less efficient index.

You should never ever ever on pain of death ever write code that just slaps quotes around an unknown value and stuff it into a SQL statement. Either use prepared statements, or write a function that properly escapes any embedded quotes. Such a function is trivial to write. I don't understand why JDBC does not include one, so you have to write it yourself and include it with every app. (This is especially true given that some SQL dialects have characters other than single quote that should be escaped.)

Here's an example of such a function in Java:

public static String q(String s)
{
  if (s==null)
    return "null";
  if (s.indexOf('\'')<0)
    return "'"+s+"'";
  int sl=s.length();
  char[] c2=new char[sl*2+2];
  c2[0]='\''; 
  int p2=1;
  for (int p=0;p<sl;++p)
  {
    char c=s.charAt(p);
    if (c=='\'')
      c2[p2++]=c;
    c2[p2++]=c;
  }
  c2[p2++]='\'';
  return new String(c2,0,p2);
}

(Note: I just edited that function up from the version I pulled out of my code to eliminate some special cases not relevant here -- sorry if I introduced some minor errors when doing that.)

I usually give it a really short name like "q" so I can just write:

String sql="select customer_name from customer where customer_type="+q(custType)
  +" and customer_zip="+q(custZip);

or something quick and easy like that. It's a violation of "give functions complete and meaningful names" but I think it worthwhile here, where I may use the same function ten times in one statement.

Then I overload it to take dates and numbers and other special types and handle them appropriately.

Answer 5

Using prepared statements with bind variables is much faster because it means that Oracle doesn't have to parse (compile) sql statements again and again. Oracle stores all the executed statements together with the execution plans in a shared hash table for reuse. However Oracle will only reuse the execution plan of prepared statements with bind variables. When you do:

"select * from table1 where last_name like %"+ lastName

Oracle doesn't reuse the execution plan.

(Oracle hashes every sql statement and when you use select ... where last_name like %"+ lastName every sql statement has a different hash value because variable lastname almost always has a different value, so Oracle can't find the sql statement in the hash table and Oracle can't reuse the execution plan.)

In a multi concurrency situation the impact is even bigger because Oracle locks this shared hash table. Those locks don't last long but in a multi concurrency situation locking really starts to hurt. When you use prepared statements with bind variables almost no locking is neccessary. Oracle by the way calls those spin locks latches.

Only when you have a dataware house and your queries take minutes (reporting) instead of split seconds you can use non-prepared statements.

Answer 6

Okay, I'll take your word for it on Oracle. This is, not surprisingly, database-engine dependent. Postgres behaves as I described. When using MySQL from JDBC -- at least as of a couple of years ago when I last looked into this -- there is pretty much zero difference between prepared statements and single-use statements, because the MySQL JDBC driver saves prepared statements on the CLIENT side, when you execute the prepared statement it fills in the values as text and ships it over to the database engine. So as far as the engine is concerned, there really is no such thing as a prepared statement. I would not be at all surprised to learn that other engines have completely different behavior.