MySQL Bugs: #26381: MySql Connector 1.09 Parameters in some circumstances passes apostrophes

Bug #26381	MySql Connector 1.09 Parameters in some circumstances passes apostrophes
Submitted:	14 Feb 2007 20:35	Modified:	26 Jul 2007 16:44
Reporter:	Sachman Bhatti	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	1.09	OS:	Windows (Windows 2003/IIS 7)
Assigned to:		CPU Architecture:	Any
Tags:	1.09

Description:
The 1.09 Connector, (not reproducible in 1.07), when adding a string parameter which contains a single quote (') in some circumstances results in it passing it to the MySqlCommand causing it to give a bad SELECT command error.  This happens even if you use cmd.Parameters.Add("?myparam",myParam.Replace("'","''"))

How to repeat:
Passing it:  The Chrysler unit of the German-American automaker (NYSE: DCX) announced its long-awaited plan at its Auburn Hills headquarters, saying it would cut 16 per cent of the U.S. division’s work force, a move it hoped would return its U.S. operations to profitability by next year.

will always result in the error and I believe even "U.S. division’s work force" alone will do it.

The C# code looks as follows (with the sample text above as the value of All_Description)

MySqlCommand cmd = new MySqlCommand("UPDATE listings SET paymentoptions=?paymentoptions,description=?description WHERE id=?id", conn);
            cmd.Parameters.Add("?id", ID);
            cmd.Parameters.Add("?description", All_Description);
            cmd.Parameters.Add("?paymentoptions", All_PaymentOptions);
            cmd.ExecuteNonQuery();
            cmd.Dispose();

Hi and thanks for your report.

Verified as described.

There is no escaping happening for your character ("’") in MySqlString.cs Line 39
		private string EscapeString(string s)
		{
			s = s.Replace("\\", "\\\\");
			s = s.Replace("\'", "\\\'");
			s = s.Replace("\"", "\\\"");
			s = s.Replace("`", "\\`");
			return s;
		}

You might add line yourself:
			s = s.Replace("’", "\\’");

After adding, everything works as expected:
After READ
name
U.S. division's workforce
Ready

Test case:
Table 'a':
CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(30) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

SP:
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`spTestBug26381` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `spTestBug26381`(IN txt VARCHAR(30))
BEGIN
  INSERT INTO a (name) VALUES (txt);
END $$
DELIMITER ;

MySqlConnection conn = new MySqlConnection();
conn.ConnectionString = "DataSource=localhost;Database=test;UserID=root;Password=;PORT=3306";
conn.Open();
MySqlCommand cmd = new MySqlCommand("spTestBug26381", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("?txt", "U.S. division’s workforce");
cmd.Parameters[0].Direction = ParameterDirection.Input;
int rowsAffected = cmd.ExecuteNonQuery();
MySqlCommand fetchcmd = new MySqlCommand("SELECT * FROM a ORDER BY Id DESC LIMIT 1", conn);
MySqlDataReader dr = fetchcmd.ExecuteReader();
dr.Read();
Console.WriteLine("After READ");
Console.WriteLine(dr.GetName(1));
Console.WriteLine(dr.GetString(1));
dr.Close();
Console.WriteLine("Ready");
cmd.Dispose();
conn.Close();
Console.ReadKey();

You might also want to escape this other character
	private string EscapeString(string s)
		{
			s = s.Replace("\\", "\\\\");
			s = s.Replace("\'", "\\\'");
			s = s.Replace("\"", "\\\"");
			s = s.Replace("`", "\\`");
			s = s.Replace("’", "\\’");
			s = s.Replace("‘", "\\‘");
			return s;
		}

This is not a bug.  The user is not using a charset that can represent those quote characters.  The quote characters he is using are Unicode characters in the 2000 range and are not used by MySQL for symbol quoting.  The connector will convert them to ascii quotes if an ascii or latin1 encoding is used and then will escape those quotes. IF the user wishes to use these quote characters he/she needs to use a UTF8 table and a UTF8 charset on the connector.