Description:
If I add backspaces, a literal 0x00, or other unusual characters to an enum value, I can see very strange results with both SHOW CREATE TABLE and DESCRIBE. The result of SHOW CREATE TABLE is an invalid DDL statement. The result of DESCRIBE is also clearly not a valid `Type`.
I can only imagine it gets worse if I add FormFeed or NewLine charaters. Also I am concerned how this impacts dump-and-restore.
How to repeat:
mysql> create table t1(id int, e enum('foo', 'bar', 0x08080808080808, 'whiz', 'bang', 0x00, 'hello') NOT NULL) engine=innodb;
Query OK, 0 rows affected (0.05 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`e` enum('foo',','whiz','bang','','hello') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> describe t1\G
*************************** 1. row ***************************
Field: id
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 2. row ***************************
Field: e
Type: enum('foo',','whiz','bang','','hello')
Null: NO
Key:
Default: NULL
Extra:
2 rows in set (0.01 sec)
mysql>
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.17 |
+-----------+
1 row in set (0.01 sec)
Suggested fix:
For SHOW CREATE TABLE, the Type of `Create Table` of VAR_STRING is fine, but the contents of the enums must be escaped.
For DESCRIBE, I'm not sure the Type of the `Type` column; I think it should not be a BLOB, but rather a VAR_STRING. Regardless, it should be also be escaped properly for display.
Additionally, while I support using utf8mb4 characters in an enum, perhaps some control characters could be considered invalid.
Description: If I add backspaces, a literal 0x00, or other unusual characters to an enum value, I can see very strange results with both SHOW CREATE TABLE and DESCRIBE. The result of SHOW CREATE TABLE is an invalid DDL statement. The result of DESCRIBE is also clearly not a valid `Type`. I can only imagine it gets worse if I add FormFeed or NewLine charaters. Also I am concerned how this impacts dump-and-restore. How to repeat: mysql> create table t1(id int, e enum('foo', 'bar', 0x08080808080808, 'whiz', 'bang', 0x00, 'hello') NOT NULL) engine=innodb; Query OK, 0 rows affected (0.05 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `e` enum('foo',','whiz','bang','','hello') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> describe t1\G *************************** 1. row *************************** Field: id Type: int(11) Null: YES Key: Default: NULL Extra: *************************** 2. row *************************** Field: e Type: enum('foo',','whiz','bang','','hello') Null: NO Key: Default: NULL Extra: 2 rows in set (0.01 sec) mysql> mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.17 | +-----------+ 1 row in set (0.01 sec) Suggested fix: For SHOW CREATE TABLE, the Type of `Create Table` of VAR_STRING is fine, but the contents of the enums must be escaped. For DESCRIBE, I'm not sure the Type of the `Type` column; I think it should not be a BLOB, but rather a VAR_STRING. Regardless, it should be also be escaped properly for display. Additionally, while I support using utf8mb4 characters in an enum, perhaps some control characters could be considered invalid.