Bug #12997 'like' and '\'

• Submitted: 5 Sep 2005 17:59
• Modified: 5 Sep 2005 19:04
• Reporter: Pavel Skuratovich
• Status: Won't fix
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.14-nt
• OS: Windows (Windows 2000 Server)
• CPU Architecture: Any

[5 Sep 2005 17:59] Pavel Skuratovich

Description:
double-escape of backslashes in 'like'

How to repeat:
mysql> select name from dirs limit 12;
+-------------------+
| name              |
+-------------------+
| \\dip\Їрщы√ ╥╙╥   |
| \\dip\NETLOGON    |
| \\dip\Info        |
| \\dip\CLT-INST    |
| \\dip\CertEnroll  |
| \\dip\KAV Updates |
| \\dip\GameZone    |
| \\dip\SYSVOL      |
| \\dip\VPLOGON     |
| \\dip\fmap        |
| \\dip\VPHOME      |
| \\dip\dip1        |
+-------------------+
12 rows in set (0.00 sec)

mysql> select name from dirs where name like '\\\\dip%' limit 12;
Empty set (0.00 sec)

mysql> select name from dirs where name like '\\\\\\\\dip%' limit 12;
+-------------------+
| name              |
+-------------------+
| \\dip\Їрщы√ ╥╙╥   |
| \\dip\NETLOGON    |
| \\dip\Info        |
| \\dip\CLT-INST    |
| \\dip\CertEnroll  |
| \\dip\KAV Updates |
| \\dip\GameZone    |
| \\dip\SYSVOL      |
| \\dip\VPLOGON     |
| \\dip\fmap        |
| \\dip\VPHOME      |
| \\dip\dip1        |
+-------------------+
12 rows in set (0.00 sec)

mysql> select name from dirs where name like '\\\\\\\\dip\\%' limit 12;
Empty set (0.00 sec)

mysql> select name from dirs where name like '\\\\\\\\dip\\\\%' limit 12;
+-------------------+
| name              |
+-------------------+
| \\dip\Їрщы√ ╥╙╥   |
| \\dip\NETLOGON    |
| \\dip\Info        |
| \\dip\CLT-INST    |
| \\dip\CertEnroll  |
| \\dip\KAV Updates |
| \\dip\GameZone    |
| \\dip\SYSVOL      |
| \\dip\VPLOGON     |
| \\dip\fmap        |
| \\dip\VPHOME      |
| \\dip\dip1        |
+-------------------+
12 rows in set (0.00 sec)

[5 Sep 2005 19:04] Andrey Hristov

Use SQL mode NO_BACKSLASH_ESCAPES.
http://dev.mysql.com/doc/mysql/en/server-sql-mode.html
"Disable the use of the backslash character (‘\’) as an escape character within strings. With this mode enabled, backslash becomes any ordinary character like any other. (New in MySQL 5.0.1)"
set @@sql_mode=NO_BACKSLASH_ESCAPES;

[5 Sep 2005 21:00] Pavel Skuratovich

but even if backslash is escape character, '\\\\\\\\' should represent '\\\\', _not_ '\\'!

[21 Sep 2005 8:16] Sergei Golubchik

Check the description of LIKE in the manual, it explains why you need double-escaping