Searching using MySQL: How to escape wildcards

Question

I am currently searching my database with a query (using JDBC) like this:

"... AND LCASE(Items.Name) LIKE '%" + searchString.toLowerCase() + "%';"

Now, this is obviously very bad, because it allows for SQL injection as well as insertion of wildcard symbols such as % and _.

My question is, how can I do a query such that even if the searchString contains any of these characters, they will be treated literally?

Answer 1

First, don't use LCASE with LIKE unless you're using a case-sensitive locale (which is not the default with MySQL).

As far as escaping those characters, just prefix them with a \ character, so foo%bar becomes foo\%bar.

(It's been a while since I've used Java, but might this work:)

searchString.replaceAll('%', '\\\\%').replaceAll('_', '\\\\_')

(or using a regex):

Regex r = new Regex('(?:%|_)', '\\\\$&');
r.replaceAll(searchString)

As far as preventing SQL injection, just bind the variable as normal:

WHERE LCASE(Items.Name) LIKE ?

And create the bound string like:

'%' + searchString.replaceAll('%', '\\\\%').replaceAll('_', '\\\\_') + '%'