MySQL Bugs: #73797: problems with escaping with \ and ' combined in a string.

Bug #73797	problems with escaping with \ and ' combined in a string.
Submitted:	3 Sep 2014 20:10	Modified:	4 Sep 2014 8:08
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	and - from 5.0 to 5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have a customer/orders database. One table has a `customer_name` column. Some records had data inserted incorrectly to this column due to a bug in an application (probably).

Example: "Peter's Pizza-bar" was inserted as "Peter\'s Pizza-bar". In other words: application language escapes were not removed when inserting to the database.  To repeat: I believe that this is not a problem with MySQL, but with the application.

However I am not able to design a query that finds all affected rows reliably. No query I try, works as expected - finding either less or more rows. The query is of the simple form "SELECT * FROM orders_table WHERE <where_clause using LIKE-operator on `customer_name` column> ORDER BY `order_id` DESC;" .. and the <where_clause> on `customer_name` column is the problem.

The server is 5.6.12 - but I don't think it matters.

How to repeat:
A simplified test case not involving a table: 

SELECT "a\'b " LIKE "a\'b ";  -- returns 1. I'd expect 0 as "\'" should simply be evaluated as "'" in a LIKE-clause? 
SELECT "a\'b " LIKE "a\\'b "; -- 1 ; this is what I expect. "\\'" evaluates to "\'"
SELECT "a\'b " LIKE "a\\\'b "; -- 1 ; also expected. "\\\'" evaluates to "\'" in my unerstanding.
SELECT "a\'b " LIKE "a\\\\'b " -- 0 ; also expected. "\\\\'" evaluates to "\\'

In the example I use "doublequotes" to enclose strings in order to simplify the escaping of a string containing " ' " (singlequotes). If I use 'singlequotes' it becomes even more.. errhh .. funny :-). Doublequotes should be OK as there is no ANSI sql_mode involved and strings do not contain doublequotes.

I checked all versions from 5.0 to 5.6.  It is the same with the simple test case.  The behavior may be intended/expected.  But in that case I do not find sufficient guidance in documentation at http://dev.mysql.com/doc/refman/5.6/en/string-literals.html how to write the WHERE .. LIKE clause. 

Suggested fix:
Just confused!

OK .. my mistake.  The test case with stringliterals does not describe my problem properly. 

All those  will work as expected (using different ways to escape " ' " and quoting using "doublequotes" and "singlequotes'.

SELECT "a\\'b " LIKE "a\\\\'b ";
SELECT "a\\'b " LIKE 'a\\\\''b ';
SELECT "a\\'b " LIKE "a\\\\'b ";
SELECT "a\\'b " LIKE "a\\\\\'b ";
SELECT 'a\\\'b ' LIKE "a\\\\'b ";
SELECT 'a\\''b ' LIKE "a\\\\'b ";
-- etc. all return "1". A full cartesian product of all possibilities would likely create a larger list!

So closing!