MySQL Bugs: #4505: Wrong string with backslash escaping in ByteFx.Data.MySqlClient.MySqlParameter

Bug #4505	Wrong string with backslash escaping in ByteFx.Data.MySqlClient.MySqlParameter
Submitted:	10 Jul 2004 22:01	Modified:	12 Jul 2004 16:07
Reporter:	Konrad N	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	0.76	OS:	Any (All)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
String escaping in ByteFx.Data.MySqlClient.MySqlParameter correctly handles ' and " signs (so they become \' and \" respectively) but doesn't escape the backslash character at all. So when you pass a string with single backslashes in it they disappear, worse if there's one at the end of the parameter then mysql thinks the closing ' of the string is escaped, naturally this messes up the whole sql command.

How to repeat:
insCmd = new MySqlCommand();
insCmd.Connection = AConnection;
insCmd.CommandText = "INSERT INTO items (ItemName) VALUES (@Name)";
insCmd.Parameters.Add("@Name", MySqlDbType.VarChar).Value = @"TEST\";
insCmd.ExecuteNonQuery();

Suggested fix:
Change MySqlParamter from:
private string EscapeString( string s )
{
s = s.Replace("\'", "\\\'");
s = s.Replace("\"", "\\\"");
return s;
}

to:
private string EscapeString( string s )
{
s = s.Replace("\'", "\\\'");
s = s.Replace("\"", "\\\"");
s = s.Replace("\\", "\\\\");
return s;
}

(Note: escaping of byte arrays via EscapeByteArray handles \ correctly)

Sorry, the possible solution should obviously be:
private string EscapeString( string s )
{
s = s.Replace("\\", "\\\\"); //done fist to escape only the original backslashes
s = s.Replace("\'", "\\\'");
s = s.Replace("\"", "\\\"");
return s;
}

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

You may consider escaping the null char as well

e.g. (as above plus the second line)

private string EscapeString( string s )
{
	s = s.Replace("\\", "\\\\");
	s = s.Replace("\0", "\\0");
	s = s.Replace("\'", "\\\'");
	return s.Replace("\"", "\\\"");
}

Ant.