| 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: | |
| Category: | MySQL Server: Document Store: MySQL Shell | Severity: | S2 (Serious) |
| Version: | 1.0.4 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[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;.
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);