| Bug #71398 | Backslash and Semicolon Problem when SQL Modes Include NO_BACKSLASH_ESCAPES | ||
|---|---|---|---|
| Submitted: | 16 Jan 2014 9:11 | Modified: | 21 Jan 2014 8:32 |
| Reporter: | Preecha Warapapong | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Errors | Severity: | S3 (Non-critical) |
| Version: | 5.6.15 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[16 Jan 2014 10:46]
MySQL Verification Team
Hello Preecha,
Thank you for the report.
I can not repeat reported behavior and it seems to work as expected.
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.15-log |
+------------+
1 row in set (0.00 sec)
mysql> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------+
| sql_mode | NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create table tbl1 (col1 varchar(20), col2 varchar(20), col3 varchar(20));
Query OK, 0 rows affected (0.14 sec)
mysql> insert into tbl1 (col1, col2, col3) values ('a\c', 'd;e', 'fg\'); -- will work
Query OK, 1 row affected (0.00 sec)
mysql> insert into tbl1 (col1, col2, col3) values ('fg\', 'd;e', 'a\c'); -- will fail
Query OK, 1 row affected (0.01 sec)
mysql> insert into tbl1 (col1, col2, col3) values ('a\c', 'd;e', 'fg\'''); -- will work
Query OK, 1 row affected (0.01 sec)
mysql> insert into tbl1 (col1, col2, col3) values ('fg\''', 'd;e', 'a\c'); -- will work
Query OK, 1 row affected (0.04 sec)
mysql> insert into tbl1 (col1, col2, col3) values ('a\c', 'd;e', 'fg\''h'); -- will work
Query OK, 1 row affected (0.01 sec)
mysql> insert into tbl1 (col1, col2, col3) values ('fg\''h', 'd;e', 'a\c'); -- will work
Query OK, 1 row affected (0.04 sec)
mysql> select * from tbl1;
+-------+------+-------+
| col1 | col2 | col3 |
+-------+------+-------+
| a\c | d;e | fg\ |
| fg\ | d;e | a\c |
| a\c | d;e | fg\' |
| fg\' | d;e | a\c |
| a\c | d;e | fg\'h |
| fg\'h | d;e | a\c |
+-------+------+-------+
6 rows in set (0.00 sec)
mysql> select 'a\b', 'd;e', 'fg\' from dual; -- will work
+-----+-----+-----+
| a\b | d;e | fg\ |
+-----+-----+-----+
| a\b | d;e | fg\ |
+-----+-----+-----+
1 row in set (0.00 sec)
mysql> select 'fg\', 'd;e', 'a\b' from dual; -- will fail
+-----+-----+-----+
| fg\ | d;e | a\b |
+-----+-----+-----+
| fg\ | d;e | a\b |
+-----+-----+-----+
1 row in set (0.00 sec)
mysql> select 'a\b', 'd;e', 'fg\''' from dual; -- will work
+-----+-----+------+
| a\b | d;e | fg\' |
+-----+-----+------+
| a\b | d;e | fg\' |
+-----+-----+------+
1 row in set (0.00 sec)
mysql> select 'fg\''', 'd;e', 'a\b' from dual; -- will fail
+------+-----+-----+
| fg\' | d;e | a\b |
+------+-----+-----+
| fg\' | d;e | a\b |
+------+-----+-----+
1 row in set (0.00 sec)
mysql> select 'a\b', 'd;e', 'fg\''h' from dual; -- will work
+-----+-----+-------+
| a\b | d;e | fg\'h |
+-----+-----+-------+
| a\b | d;e | fg\'h |
+-----+-----+-------+
1 row in set (0.00 sec)
mysql> select 'fg\''h', 'd;e', 'a\b' from dual; -- will fail
+-------+-----+-----+
| fg\'h | d;e | a\b |
+-------+-----+-----+
| fg\'h | d;e | a\b |
+-------+-----+-----+
1 row in set (0.00 sec)
mysql> select * from tbl1 where 'd;e' = 'ab\'; -- will work
Empty set (0.00 sec)
mysql> select * from tbl1 where 'ab\' = 'd;e'; -- will fail
Empty set (0.00 sec)
mysql> select * from tbl1 where 'd;e' = 'ab\'''; -- will work
Empty set (0.00 sec)
mysql> select * from tbl1 where 'ab\''' = 'd;e'; -- will fail
Empty set (0.00 sec)
mysql> select * from tbl1 where 'd;e' = 'ab\''c'; -- will work
Empty set (0.00 sec)
mysql> select * from tbl1 where 'ab\''c' = 'd;e'; -- will fail
Empty set (0.00 sec)
Thanks,
Umesh
[20 Jan 2014 9:35]
Preecha Warapapong
Thanks for your response, Umesh. I found this problem in 'MySQL Workbench' and 'Toad for MySQL'. I tested with mysql command line like you did. The problem could not be reproduced either. PHP using php_mysql.dll and Java using JDBC also work. The problem must be somewhere else. But it's quite all right for me if PHP and Java works. However, can you advise how to make it work in Workbench and Toad? Best regards, Preecha
[21 Jan 2014 8:32]
Preecha Warapapong
Now I think it's not critical so I changed the severity.
Description: When the NO_BACKSLASH_ESCAPES is included in the SQL modes, a literal semicolon after a backslash-single-quote sequence will cause a syntax error. How to repeat: 1. Set SQL modes to include NO_BACKSLASH_ESCAPES. 2. Restart MySQL. 3. Test the following SQLs. create table tbl1 (col1 varchar(20), col2 varchar(20), col3 varchar(20)); insert into tbl1 (col1, col2, col3) values ('a\c', 'd;e', 'fg\'); -- will work insert into tbl1 (col1, col2, col3) values ('fg\', 'd;e', 'a\c'); -- will fail insert into tbl1 (col1, col2, col3) values ('a\c', 'd;e', 'fg\'''); -- will work insert into tbl1 (col1, col2, col3) values ('fg\''', 'd;e', 'a\c'); -- will work insert into tbl1 (col1, col2, col3) values ('a\c', 'd;e', 'fg\''h'); -- will work insert into tbl1 (col1, col2, col3) values ('fg\''h', 'd;e', 'a\c'); -- will work select 'a\b', 'd;e', 'fg\' from dual; -- will work select 'fg\', 'd;e', 'a\b' from dual; -- will fail select 'a\b', 'd;e', 'fg\''' from dual; -- will work select 'fg\''', 'd;e', 'a\b' from dual; -- will fail select 'a\b', 'd;e', 'fg\''h' from dual; -- will work select 'fg\''h', 'd;e', 'a\b' from dual; -- will fail select * from tbl1 where 'd;e' = 'ab\'; -- will work select * from tbl1 where 'ab\' = 'd;e'; -- will fail select * from tbl1 where 'd;e' = 'ab\'''; -- will work select * from tbl1 where 'ab\''' = 'd;e'; -- will fail select * from tbl1 where 'd;e' = 'ab\''c'; -- will work select * from tbl1 where 'ab\''c' = 'd;e'; -- will fail My conclusion is that, with the NO_BACKSLASH_ESCAPES mode, when there is a string with a backslash followed by a single quote (either the closing single quote or a literal single quote), a literal semicolon inside a string that comes after the backslash-single-quote sequence will cause a syntax error.