MySQL Bugs: #35710: INSERTs do not escape column names

Bug #35710	INSERTs do not escape column names
Submitted:	31 Mar 2008 16:17	Modified:	8 Apr 2008 14:31
Reporter:	Marc Liyanage	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.0.16	OS:	Windows (Vista SP1)
Assigned to:	Alfredo Kojima	CPU Architecture:	Any

Description:
MySQL Workbench emits SQL statements like this for INSERTs defined in the table definition:

INSERT INTO `attribute_type` (id, key) VALUES (1, 'text');

The column name "key" in this example is a reserved word and therefore column names should be quoted like this:

INSERT INTO `attribute_type` (`id`, `key`) VALUES (1, 'text');

The CREATE TABLE statements produced by Workbench already have quotes around the column names.

Currently I run a sed script to fix up the .sql file.

How to repeat:
Create a table in MySQL workbench that has a column name which is a reserved keyword.

In the table details, go to the Inserts tab and insert a row with values in the column.

Do File -> Export -> Forward Engineer SQL script and export the SQL file.

Load it into MySQL server. It wil fail with an error message like this:

ERROR 1064 (42000) at line 71: 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 'key) VALUES (1, 'text')'

Suggested fix:
Wrap the INSERT statement column names in quotes like in the CREATE TABLE statements.

Thank you for a bug report. Verified just as described.

this is fixed in SVN Rev 2917

A note has been added to the 5.0.17 changelog: 

Identifiers for field names in DML SQL statements would not be quoted correctly, allowing for reserved words to be included in the SQL statements.