MySQL Bugs: #20103: Escaping with backslash does not work

Bug #20103	Escaping with backslash does not work
Submitted:	27 May 2006 9:50	Modified:	9 Aug 2006 20:08
Reporter:	Hakan Küçükyılmaz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.0.23-BK	OS:	Linux (SuSE 10.0)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
Escaping with backslash does not work as expected.

Manual states:
http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html
...
To specify a different escape character, use the ESCAPE clause

 

How to repeat:
[11:49] root@test>SELECT 'Andrey_' LIKE 'Andrey|_' ESCAPE '|';
+--------------------------------------+
| 'Andrey_' LIKE 'Andrey|_' ESCAPE '|' |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (0.00 sec)

[11:50] root@test>SELECT 'Andrey_' LIKE 'Andrey\_' ESCAPE '\';
    '> 

[11:50] root@test>SELECT 'Andrey_' LIKE 'Andrey\_' ESCAPE '\';
    '> ';
ERROR 1210 (HY000): Incorrect arguments to ESCAPE

Suggested fix:
Backslash should be allowed as escape character.

On the same manual page (http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html) there is a clear explanation:

"Note: Because MySQL uses C escape syntax in strings (for example, ‘\n’ to represent a newline character), you must double any ‘\’ that you use in LIKE strings. For example, to search for ‘\n’, specify it as ‘\\n’. "

Same applies to ESCAPE clause:

mysql> SELECT 'David_' LIKE 'David\_' ESCAPE '\\';
+-------------------------------------+
| 'David_' LIKE 'David\_' ESCAPE '\\' |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.23    |
+-----------+
1 row in set (0.00 sec)

It is intended behaviour in MySQL, I believe.

Sorry, I just tried with ESCAPE '\\', too.

[12:21] root@test>SELECT 'Andrey_' LIKE 'Andrey\_' ESCAPE '\\';
+---------------------------------------+
| 'Andrey_' LIKE 'Andrey\_' ESCAPE '\\' |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)

But with NO_BACKSLASH_ESCAPE it does not work:

[12:21] root@test>set sql_mode='NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.00 sec)

5.0.23-debug
[12:23] root@test>SELECT 'Andrey_' LIKE 'Andrey\_' ESCAPE '\';
    '> ';
+--------------------------------------+
| 'Andrey_' LIKE 'Andrey\_' ESCAPE '\' |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1

[12:23] root@test>SELECT 'Andrey_' LIKE 'Andrey\_' ESCAPE '\\';
ERROR 1210 (HY000): Incorrect arguments to ESCAPE

It looks like that the single quote does not get closed properly.

The (verified) fact that there is no way to use \ as ESCAPE character after

set sql_mode='NO_BACKSLASH_ESCAPES';

is either a bug or (more likely) a request for a more detailed documentation. Thank you for persistence.

According to the manual, NO_BACKSLASH_ESCAPES
is supposed to make backslash an ordinary character
like any other character.  Looks to me like it doesn't
work:

mysql> set sql_mode = 'NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.24 sec)

mysql> select '\';
    '>

Note how the prompt changes, indicating that \ has been
taken as an escape character before the ' character.  But
perhaps this problem occurs only in certain contexts. The
following appears to be correct:

mysql> set sql_mode = '';
Query OK, 0 rows affected (0.12 sec)

mysql> select '\\', '\t';
+---+---+
| \ |   |
+---+---+
| \ |    | 
+---+---+
1 row in set (0.00 sec)

mysql> set sql_mode = 'NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.00 sec)

mysql> select '\\', '\t';
+----+----+
| \\ | \t |
+----+----+
| \\ | \t | 
+----+----+
1 row in set (0.00 sec)

Maybe the problem is limited to backslash before
' or " characters.

There're two different bugs here.

1.
[12:23] root@test>SELECT 'Andrey_' LIKE 'Andrey\_' ESCAPE '\\';
ERROR 1210 (HY000): Incorrect arguments to ESCAPE

ESCAPE argument is not un-escaped by the server.
This is a server bug.

2. [12:23] root@test>SELECT 'Andrey_' LIKE 'Andrey\_' ESCAPE '\';
    '>

A client still treats backslash as the escape character in NO_BACKSLASH_ESCAPE mode.
This is a bug in mysql command line client.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9695

It appears to me that the patch breaks handling of escaped quote characters in the mysql client.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9755

ChangeSet@1.2222, 2006-07-29 09:21:29+03:00, gkodinov@macbook.mshome.net +4 -0
  Bug #20103: Escaping with backslash does not work
   - implement the --no-backslash-escapes option
     to disable the special meaning of backslash 
     at the client side. This is needed to complement
     the server-side no_backslash_escapes sql mode.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9877

ChangeSet@1.2222, 2006-08-01 11:05:54+03:00, gkodinov@macbook.gmz +3 -0
  Bug #20103: Escaping with backslash does not work
   - make the client to respect the server-side 
     no_backslash_escapes option and disable the 
     special meaning of backslash also at client side.

Fixed in 5.0.25, 5.1.12

Noted in 5.0.25, 5.1.12 changelogs.

Hello, 
I am running 5.0.27 and have found and I believe a similiar issue in the insert clause (through the Heidi client interface)

INSERT INTO `File`( FileName, Path, Size, Creation, Modified, LastAccess, CheckSum, Attributes) VALUES ('The House of Sand and Fog CD1.avi','e:\\',734916608,'2005-05-13 13:59:00','2005-05-13 13:59:00','',0,1)

returns an error but when i change 'e:\\' to 'e:\\ ' the insert completes - I believe this is a server bug. Please correct me if I'm wrong, I hope it helps.
Regards,
Greg.