Bug #38906 illegal/no quoting in Insert Editor
Submitted: 20 Aug 2008 9:02 Modified: 6 Aug 2009 10:10
Reporter: Bruno Baketaric Email Updates:
Status: Closed Impact on me:
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:5.0.24 OS:Any
Assigned to: Alfredo Kojima CPU Architecture:Any

[20 Aug 2008 9:02] Bruno Baketaric
The Insert Editor doesn't handle escaping of the quoting-character, if the user manually surrounds the inserted text by the quoting-character. 

How to repeat:
Create a Table like this:

CREATE  TABLE IF NOT EXISTS `wwf`.`foo_table` (
  `foo` VARCHAR(40) NOT NULL )

Open the Insert Editor and insert this Value:
foo ' bar

The Insert Editor automatically surrounds the Text with the quoting character and escapes correctly - resulting Value is:
'foo \' bar'

Now insert this value:
'foo ' bar'

Result: no quoting takes place.

Forward Engineer a SQL-Create Script including the INSERT Statements. Result is a illegal SQL-Statement:
INSERT INTO `foo_table` (`foo`) VALUES ('foo ' bar');
[20 Aug 2008 18:04] Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[12 Mar 2009 14:03] Susanne Ebrecht
This should be documented for 5.0 and fixed in 5.2.
[19 Jul 2009 1:16] Alfredo Kojima
This bug has vanished because of the reimplementation of the INSERTs editor in 5.2
My tests indicate quoting works as expected.
[6 Aug 2009 9:05] Johannes Taxacher
for 5.0 and 5.1 this is expected behavior. for the docs:

if one enters a value without leading- and trailing-quotes, the editor sets quotes AND escapes special chars - otherwise, if the user already enters quoted text, the editor doesn't check entry anymore, assuming user enters correctly escaped and quoted sequence!

in 5.2 this changes a bit because of a new insert editor:

user is supposed to enter text into std.-inserts-editor in 5.2 without doing any manual quoting or escaping. just enter the text as it should be stored, and WB takes care of that when creating the SQL statements.
[6 Aug 2009 10:10] Tony Bedford
The Inserts Editor section of the main documentation was updated and clarified. 

A 'change' entry was also added to the 5.2.3 changelog as a 'heads up':

In the Inserts tab of the Table Editor, if a value was entered with quoting then any characters in the string requiring escaping were not escaped. However, if the same string was entered without quoting then the string would be escaped correctly by MySQL Workbench.

For 5.0 and 5.1 this is expected behavior. If a value is entered without leading and trailing quotes, the Inserts Editor adds quoting and escapes characters that require it. However, if quoted text is entered, the Inserts Editor carries out no further checks since it assumes a correctly escaped and quoted sequence has been entered.

5.2 features a new Inserts Editor. In this case the user enters the string without quoting or escaping and the Inserts Editor takes care of all quoting and escaping as required.