|
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.
|