MySQL Bugs: #38906: illegal/no quoting in Insert Editor

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:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.0.24	OS:	Any
Assigned to:	Alfredo Kojima	CPU Architecture:	Any
Tags:	CHECKED

Description:
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 )
ENGINE = InnoDB

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');

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

This should be documented for 5.0 and fixed in 5.2.

This bug has vanished because of the reimplementation of the INSERTs editor in 5.2
My tests indicate quoting works as expected.

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.

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.