MySQL Bugs: #68676: Escaping backslash in identifier name

Bug #68676	Escaping backslash in identifier name
Submitted:	14 Mar 2013 21:25	Modified:	19 Mar 2013 20:00
Reporter:	Kamil Dziedzic	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.5.30, 5.0.97, 5.1.70, 5.5.32, 5.6.12, 5.7.2	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Hi,

Not sure if this is bug (and if then very trivial) or I'm doing something wrong.
Currently I'm trying to figure out how to properly escape characters in name identifiers/aliases. Let's say there is an application/library used by developers which allows to input user defined column names/aliases. However even though it is used by developers it is always good to properly escape those identifiers.

I've read this:
http://dev.mysql.com/doc/refman/5.5/en/identifiers.html

Some quotes from documentation:
1. "An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it."
2. "The identifier quote character is the backtick (“`”):"
3. "Identifier quote characters can be included within an identifier if you quote the identifier. If the character to be included within the identifier is the same as that used to quote the identifier itself, then you need to double the character."
4. "Permitted characters in quoted identifiers include the full Unicode Basic Multilingual Plane (BMP), except U+0000:
    ASCII: U+0001 .. U+007F
    Extended: U+0080 .. U+FFFF 
"

So if someone gets fancy and wants to add a\b`c (a, backslash, b, backtick, c) then according to documentation I should only double inserted backticks (s/`/``/) and also put whole expression in backticks.

# mysql <<'EOF' 
use foo;
CREATE TEMPORARY TABLE `bar` ( `a\b``c` INT ) ENGINE=INNODB;
DESCRIBE `bar`;
INSERT INTO `bar` (`a\b``c`) values (1);
SELECT `a\b``c` FROM `bar`;
EOF
Field   Type    Null    Key     Default Extra
a\\b`c  int(11) YES             NULL
a\b`c
1

Great, works as expected.

But what if we remove the 'b' letter from this column name?

# mysql <<'EOF' 
use foo;
CREATE TEMPORARY TABLE `bar` ( `a\``c` INT ) ENGINE=INNODB;
DESCRIBE `bar`;
INSERT INTO `bar` (`a\``c`) values (1);
SELECT `a\``c` FROM `bar`;
EOF

ERROR at line 2: Unknown command '\`'.

Uhm... why? Backslash character requires to be escaped? Ok, let's try again by using double backslash:

# mysql <<'EOF' 
use foo;
CREATE TEMPORARY TABLE `bar` ( `a\\``c` INT ) ENGINE=INNODB;
DESCRIBE `bar`;
INSERT INTO `bar` (`a\\``c`) values (1);
SELECT `a\\``c` FROM `bar`;
EOF
Field   Type    Null    Key     Default Extra
a\\\\`c int(11) YES             NULL
a\\`c
1

Uhm... no. I wanted one backslash not two.

Somehow expression \` (backslash, backtick) is treated specially and I have no idea why and how to overcome this problem (except disallowing backslash).

How to repeat:
# mysql <<'EOF' 
use foo;
CREATE TEMPORARY TABLE `bar` ( `a\``c` INT ) ENGINE=INNODB;
DESCRIBE `bar`;
INSERT INTO `bar` (`a\``c`) values (1);
SELECT `a\``c` FROM `bar`;
EOF

ERROR at line 2: Unknown command '\`'.

Suggested fix:
# mysql <<'EOF' 
use foo;
CREATE TEMPORARY TABLE `bar` ( `a\``c` INT ) ENGINE=INNODB;
DESCRIBE `bar`;
INSERT INTO `bar` (`a\``c`) values (1);
SELECT `a\``c` FROM `bar`;
EOF
Field   Type    Null    Key     Default Extra
a\\`c  int(11) YES             NULL
a\`c
1

Thank you for the report.

Verified as described.

Any chance for a response why this happens?

FYI I came across this bug recently and found an interesting mysql client trick to reference the oddly named table, that may itself be a bug.

--------------------

mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE `\` ( `foo` int(10) ) ENGINE=InnoDB CHARSET=utf8; `;
Query OK, 0 rows affected (0.00 sec)

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 '`' at line 1
mysql> show tables;
+---------------+
| Tables_in_foo |
+---------------+
| \             |
+---------------+
1 row in set (0.00 sec)

mysql> DROP TABLE `\`; `;
Query OK, 0 rows affected (0.00 sec)

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 '`' at line 1
mysql> show tables;
Empty set (0.00 sec)

--------------------

Pretty sure that that shouldn't work.

Should I open a new bug report specifically for this, or is it ok to lump it in here with #68676?

I know this is old but it also causes issues with calling procedures.

mysql -h your.hostname.com -e "CALL your_table_schema.\`your_procedure_name\`\(\);"

Returns on the command line.

ERROR at line 1: Unknown command '\('.

current mysql version 

5.6.34-log

Any update on this being fixed or do I just need to update to the newest mysql version?

Cheers,

JayRizzo

Bshite