Bug #85440 unusual values in enums subvert SHOW CREATE TABLE and DESCRIBE
Submitted: 14 Mar 2017 10:58 Modified: 14 Mar 2017 11:43
Reporter: Eric Herman (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version: 5.7.17 , 5.6.35 OS:Linux
Assigned to: CPU Architecture:Any
Tags: enum

[14 Mar 2017 10:58] Eric Herman
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
1 row in set (0.00 sec)

mysql> describe t1\G
*************************** 1. row ***************************
  Field: id
   Type: int(11)
   Null: YES
Default: NULL
*************************** 2. row ***************************
  Field: e
   Type: enum('foo',','whiz','bang','','hello')
   Null: NO
Default: NULL
2 rows in set (0.01 sec)

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.
[14 Mar 2017 11:43] MySQL Verification Team
Hello Eric Herman,

Thank you for the report and test case.