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

[10 Jul 2004 22:01] Konrad N
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)
[10 Jul 2004 22:04] Konrad N
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;
}
[12 Jul 2004 16:07] Reggie Burnett
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
[19 Aug 2004 23:09] Anthony Johnston
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.