MySQL Bugs: #44318: Tokenizer

Bug #44318	Tokenizer
Submitted:	16 Apr 2009 12:23	Modified:	20 Apr 2009 13:32
Reporter:	Torsten Kerz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.0.2	OS:	Any
Assigned to:	Reggie Burnett	CPU Architecture:	Any
Tags:	ExecuteNonQuery MySqlTokenizer IndexOutOfRangeException

Description:
The MySqlTokenizer fails to split fieldnames from values, if there is no space between them and there are unlucky sign combinations in a string value behind the equal-sign.

As a result MySqlCommand.ExecuteNonQuery raises an index out of range exception.
 

Example statements (note the missing spaces around the equal-sign): 

INSERT INTO anytable SET Text='test--test';

In this case the tokenizer detects accidentally an comment in the value. 

UPDATE anytable SET Project='123-456',Text='Can you explain this ?',Duration=15 WHERE ID=4711;'

This case will lead to a MySqlException, because the questionmark ("?") in the value is been detected as a parameter sign. ("Fatal error encountered during command execution. EXCEPTION: MySqlException - Parameter '?'' must be defined.")

How to repeat:

Command = SqlConnection.CreateCommand
Command.CommandText = "INSERT INTO `anytable` SET Text='test--test';"
Command.ExecuteNonQuery()

Suggested fix:
Have a look at MySqlTokenizer.ReadUnquotedToken and include the equal-sign or/and the quotion marks ("'", "´", etc.) as a additional breaker for the current token.

Hi Torsten and thanks for your report.

I can not repeat it using following test case against latest c/NET sources from trunk:
      MySqlConnection con = new MySqlConnection();
      con.ConnectionString = "DataSource=opensol;Database=test;UserID=**;Password=**;PORT=**;logging=True;allow batch=true";
      con.Open();
      MySqlCommand cmdCreateTable = new MySqlCommand("DROP TABLE IF EXISTS bug44318", con);
      cmdCreateTable.ExecuteNonQuery();
      cmdCreateTable.CommandText = "CREATE TABLE `bug44318`(`Col1` int(10) NOT NULL auto_increment, `Col2` VARCHAR(50) NOT NULL, PRIMARY KEY (`Col1`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
      cmdCreateTable.ExecuteNonQuery();
      cmdCreateTable.CommandText = "INSERT INTO `bug44318`VALUES (1, 'Pero')";
      cmdCreateTable.ExecuteNonQuery();

      cmdCreateTable.CommandText = "INSERT INTO bug44318 SET Col2='text--testtext'";
      Assert.AreEqual(1, cmdCreateTable.ExecuteNonQuery());
      cmdCreateTable.Dispose();
      con.Close();
      con.Dispose();

This works just as expected... Anything I'm missing as I see no changes documented for tokenizer?

Ok, so
      cmdCreateTable.CommandText = "UPDATE bug44318 SET Col1=1549,Col2='text--pero' WHERE Col1=1";
      Assert.AreEqual(1, cmdCreateTable.ExecuteNonQuery());
works as expected while
      cmdCreateTable.CommandText = "UPDATE bug44318 SET Col1=1549,Col2='text--pero?' WHERE Col1=1";
      Assert.AreEqual(1, cmdCreateTable.ExecuteNonQuery());
throws inner exception "{"Parameter '?'' must be defined."}" as reported.

Verified as described using modified test case with spaces before and after --.
MySQLTokenizer.cs, LN 132: "Index was outside the bounds of the array."

Using rev 1559 of trunk.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/72420

fixed in 6.0.3

An entry was added to the 6.0.3 changelog:

The MySqlTokenizer failed to split fieldnames from values if they were not separated by a space. This also happened if the string contained certain characters. As a result MySqlCommand.ExecuteNonQuery raised an index out of range exception.

The resulting errors are illustrated by the following examples. Note, the example statements do not have delimiting spaces around the = operator.

INSERT INTO anytable SET Text='test--test';

The tokenizer incorrectly interpreted the value as containing a comment.

UPDATE anytable SET Project='123-456',Text='Can you explain this ?',Duration=15 WHERE
ID=4711;'

A MySqlException was generated, as the ? in the value was interpreted by the tokenizer as a parameter sign. The error message generated was:

Fatal error encountered during command execution.
EXCEPTION: MySqlException - Parameter '?'' must be defined.

Tony ...

INSERT INTO anytable SET Text='test--test';

doesn't raise an exception, but 

INSERT INTO anytable SET Text='test -- test';

will do. ;-)