MySQL Bugs: #27026: Escaping LIKE sequences in JDBC prepared statements

Bug #27026	Escaping LIKE sequences in JDBC prepared statements
Submitted:	10 Mar 2007 15:53	Modified:	31 Mar 2014 12:24
Reporter:	Matan Amir	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.0.5	OS:	Any
Assigned to:	Alexander Soklakov	CPU Architecture:	Any
Tags:	escaping, jdbc, like, preparedstatements

Description:
The MySQL JDBC driver's PreparedStatement escapes special characters entered as strings which is fine.  The problem happens when you have LIKE statements that you yourself want to escape the LIKE sequence characters.  This is because to escape the % or _ in the LIKE sequence is to use the same \ char (if for example we wanted to use % or _ literally):

Any suggestions on a way to get around this at least?

How to repeat:
What works
-----------
stmt.setString("bob%");   -- search for anything that starts with bob... works as expected:

select * from mytable where mycolumn like 'bob%';

What doesn't work
-----------------
stmt.setString("10\\% off%"); -- search for anything that starts with "10% off"... does not work as expected because the \ is replaced with \\ in the prepared statement so the final sql is:

select * from mytable where mycolumn like '10\\% off%';

Suggested fix:
maybe a different way of escaping LIKE sequence special characters?

Hi and thanks for your report.

Please provide us with as much info as you can:
 - MySQL server version
 - Host / client OS
 - JDK/JRE version
 - Small but complete test case demonstrating the error, including DDL statements

Could it be that you missed processEscapeCodesForPrepStmts (Should the driver process escape codes in queries that are prepared) connection property?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I close this report as "Can't repeat" because there is no feedback for a long time and codebase is too old. Please, feel free to reopen it if the problem still exists in current driver.