Search Using MySQL: Avoiding Wildcards

Question

I am currently searching my query database (using JDBC) as follows:

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

Now this is obviously very bad, because it allows SQL injection as well as insert wildcards such as% and _.

My question is: how can I make a request in such a way that even if searchString contains any of these characters, they will be processed literally?

Answer 1

First, do not use LCASE with LIKE unless you are case sensitive (which is not the default value in MySQL).

As for escaping these characters, just attach them with the \ character, so foo%bar will become foo\%bar .

(It has been a while since I used Java, but can this work :)

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

(or using regex):

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

As for preventing SQL injection, just bind the variable as usual:

 WHERE LCASE(Items.Name) LIKE ? 

And create a related line like:

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

Answer 2

According to this , you can escape them with a slash ( \ ) or by specifying your own escape character:

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

You will need to search and replace with mysql LIKE wildcards in your language (Java?), % And _ to replace them with \% and \_ respectively. The other template that you mentioned above is not supported (according to related documents).