Bug #82381 Incorrect single quote escape handling under classic SQL mode
Submitted: 29 Jul 2016 10:25 Modified: 23 Feb 2017 17:10
Reporter: Dennis Cheung Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Document Store: MySQL Shell Severity:S2 (Serious)
Version:1.0.4 OS:Windows
Assigned to: CPU Architecture:Any

[29 Jul 2016 10:25] Dennis Cheung
Description:
When ruunning in "mysqlsh.exe --classic --sqlc" mode, the escape single quote by using double single quote in a single quoted is broken.

How to repeat:
Case 1 - should produce same result but last one is broken
SELECT "'" as a;
SELECT '\'' as a;
SELECT '''' as a;

Case 2
SELECT 'SELECT 'SELECT ''; SELECT '2' ;

Actual:
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 ''SELECT ''' at line 1
+---+
| 2 |
+---+
| 2 |
+---+

Expected:
mysql-sql> SELECT 'SELECT ''; SELECT '; SELECT 1' ;
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 ''SELECT ''' at line 1
+------------+
| ; SELECT 1 |
+------------+
| ; SELECT 1 |
+------------+
1 row in set (0.01 sec)

mysql-sql> SELECT 'SELECT ''; SELECT '; SELECT 1' ;
+-------------------+
| SELECT '; SELECT  |
+-------------------+
| SELECT '; SELECT  |
+-------------------+
1 row in set (0.00 sec)
       ... (wait prompt until single quote);
[31 Jul 2016 17:01] MySQL Verification Team
Hello Dennis Cheung,

Thank you for the report and test case.
Observed the issue with first case.

Thanks,
Umesh
[31 Jul 2016 17:01] MySQL Verification Team
-- mysql shell

root@ubuntu1604lts:~# mysqlsh -u root -h localhost -p --classic --sqlc
Creating a Classic Session to root@localhost:3306
Enter password:
No default schema selected.

Welcome to MySQL Shell 1.0.4 Development Preview

Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help', '\h' or '\?' for help.

Currently in SQL mode. Use \js or \py to switch the shell to a scripting language.
mysql-sql> 
mysql-sql> SELECT "'" as a;
+---+
| a |
+---+
| ' |
+---+
1 row in set (0.00 sec)

mysql-sql> SELECT '\'' as a;
+---+
| a |
+---+
| ' |
+---+
1 row in set (0.00 sec)

mysql-sql> SELECT '''' as a;
       ... 
       ... 

	   
-- MySQL CLI

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.14: bin/mysql -uroot -S /tmp/mysql_ushastry.sock                                                                        Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.14-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT "'" as a;
+---+
| a |
+---+
| ' |
+---+
1 row in set (0.00 sec)

mysql> SELECT '\'' as a;
+---+
| a |
+---+
| ' |
+---+
1 row in set (0.00 sec)

mysql> SELECT '''' as a;
+---+
| a |
+---+
| ' |
+---+
1 row in set (0.00 sec)
[23 Feb 2017 17:10] David Moss
Posted by developer:
 
Thank you for your feedback, this has been fixed in upcoming versions and the following was added to the 1.0.8 change log:
The usage of single quotes in escaping has been made consistent with the handing in MySQL, for example SELECT '''' as a;.