Good Habits Part II-c: Output Filtering SQL LIKE clauses

As I had said at the beginning of the "good habits" series, I wanted to cover more than the usual suspects. So here's one that seems to frequently get missed....

When creating Prepared Statements or Parameterized Queries, everybody knows how to use placeholders for literals where the entire literal is being replaced. For example, rather than:

stmt.prepare("SELECT lastname, firstname FROM person WHERE lastname = '" + strLastName + "'");

It's better to do something like:

stmt = conn.prepareStatement("SELECT lastname, firstname FROM person WHERE lastname = ?");

But what about a like clause?

WHERE lastname LIKE '" + strLastName + "%'"

Well, you can actually include the literal as a parameter and concatenate the trailing wildcard:

WHERE lastname LIKE ? + '%'

Now I just wish I knew how to deal with underbars - a single character wildcard - as part of the literal, while still giving the application the safety of the prepared statement.