Bug #4505 Wrong string with backslash escaping in ByteFx.Data.MySqlClient.MySqlParameter
Submitted: 11 Jul 2004 0:01 Modified: 12 Jul 2004 18:07
Reporter: Konrad N
Status: Closed
Category:Connector/Net Severity:S3 (Non-critical)
Version:0.76 OS:Any (All)
Assigned to: Reggie Burnett Target Version:

[11 Jul 2004 0: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)
[11 Jul 2004 0: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 18: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
[20 Aug 2004 1: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.