Details
-
Type:
Bug
-
Status: Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 5.3.12, 5.5.37, 10.0.13
-
Fix Version/s: 10.0
-
Component/s: None
-
Labels:None
Description
QUOTE() is designed to be an SQL counterpart for the C API function
mysql_real_escape_string().
The latter uses the current client character set to escape.
If the client character set is BIG5 or SJIS, the function correctly
distinguishes between 0x5C as the second byte in a mult-byte character
and 0x5C as the real backslash escape character.
The function QUOTE() instead of using @@character_set_connection,
erroneously uses the character set of the argument instead.
So for example, if one wants to escape a BINARY/VARBINARY/BLOB value
and use it to build a BIG5 or SJIS encoded query, it won't work.
This query demonstrates the problem (make sure to use a BIG5 terminal window when reproducing):
MariaDB [test]> SET NAMES big5; SELECT _BIG5 0xA35C, QUOTE(_BINARY 0xA35C), HEX(QUOTE(_BINARY 0xA35C)); Query OK, 0 rows affected (0.00 sec) +--------------+-----------------------+----------------------------+ | _BIG5 0xA35C | QUOTE(_BINARY 0xA35C) | HEX(QUOTE(_BINARY 0xA35C)) | +--------------+-----------------------+----------------------------+ | α | 'α\' | 27A35C5C27 | +--------------+-----------------------+----------------------------+ 1 row in set (0.00 sec)
Notice, 0x5C was escaped with another 0x5C. It should have not been,
because 0x5C is the second byte of a BIG5 character A35C!
If I use the result of the second column to build an SQL query, it will fail
because the erroneously added extra backslash will cause a syntax error:
MariaDB [test]> SET NAMES big5; SET @a:=CONCAT('SELECT ', QUOTE(_BINARY 0xA35C)); SELECT @a; PREPARE stmt FROM @a;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
+--------------+
| @a |
+--------------+
| SELECT 'α\' |
+--------------+
1 row in set (0.00 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''α\'' at line 1
QUOTE() should use @@character_set_connection.
Also, to be possible to use QUOTE() in a more flexible way,
it would be nice to have a new syntax to specify the desired
character set to be uses for escaping rules:
SELECT QUOTE(0xA35C USING big5), QUOTE(0xA35C USING binary);
In the above example,
"USING big5" would not add a new 0x5C, while
"USING binary" would.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions