| Bug #111892 | Two single quotes escaping inconsistency | ||
|---|---|---|---|
| Submitted: | 26 Jul 2023 6:52 | Modified: | 27 Jul 2023 12:22 |
| Reporter: | Nathee Jaywaree | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Parser | Severity: | S3 (Non-critical) |
| Version: | 8.1 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | escape character, single quote | ||
[26 Jul 2023 12:30]
MySQL Verification Team
Hi Mr. Jaywaree, Thank you for your bug report. What you are reporting is quite correct. However, this issue has been already addressed in 8.1.0 which is out already. Most of the SQL modes can not be set any more, so it is very unlikely that these problems will be fixed in 8.0, since 8.1 is already out.
[27 Jul 2023 3:00]
Nathee Jaywaree
Hello MySQL Verification Team, I have tested again in MySQL Community 8.1.0 with the same steps, and it seems that the problem still occurs.
[27 Jul 2023 12:22]
MySQL Verification Team
Hi Mr. Javwaree, We managed to repeat your test case with 8.0.34 and 8.1.0: +---+ | ' | +---+ | ' | +---+ ERROR 1064 (42000) at line 4: 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 ERROR 1064 (42000) at line 6: 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 ERROR 1064 (42000) at line 8: 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 +---+ | " | +---+ | " | +---+ +---------------------------------------------+ | ------------- WITH \ ESCAPES -------------- | +---------------------------------------------+ | ------------- WITH \ ESCAPES -------------- | +---------------------------------------------+ +---+ | ' | +---+ | ' | +---+ +---+ | " | +---+ | " | +---+ The problem was that sql_mode does not tolerate blanks in delimiters ...... Verified.
Description: Two single quotes are not being escaped correctly with NO_BACKSLASH_ESCAPES sql mode enabled in some statements as shown below. It is unexpected that the same string is accepted in the SELECT statement, but results in an error when used in the CREATE TABLE statement. How to repeat: SET sql_mode = 'NO_BACKSLASH_ESCAPES'; SELECT ''''; -- returns ' CREATE TABLE t0 (c0 TEXT CHECK(c0 = '''')); -- 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 ''\'')' CREATE TABLE t0 (c0 TEXT DEFAULT('''')); -- ERROR 1064 (42000): ... near ''\'')' CREATE TABLE t0 (c0 TEXT GENERATED ALWAYS AS ('''')); -- ERROR 1064 (42000): ... near ''\'')' SELECT """"; -- returns " CREATE TABLE t0 (c0 TEXT CHECK(c0 = """")); -- QUERY OK Selecting the two single quotes expression itself is fine, but causes syntax error when used in table constraints. However, escaping two double quotes is fine in both cases. With NO_BACKSLASH_ESCAPES mode disabled: SET sql_mode = ''; SELECT ''''; -- returns ' CREATE TABLE t0 (c0 TEXT CHECK(c0 = '''')); -- QUERY OK SELECT """"; -- returns " CREATE TABLE t0 (c0 TEXT CHECK(c0 = """")); -- QUERY OK Suggested fix: There seems to be differences in escaping expressions in SELECT statements and expressions in table constraints. Backslash may be added to the expression incorrectly with single quote.