i am trying to database some files on my computer with mysql in c#, its having an issue with VALUES ('drum'n'bass')
+5
A:
You use a backslash - \' to escape a single quote:
VALUES ('drum\'n\'bass')
You should however be using prepared statements and not build SQL strings yourself.
Oded
2010-04-20 06:28:47
I agree. But if you do want to create SQL manually, you should at least use `string.Format()` to deal with the quotes.
Prutswonder
2010-04-20 06:32:58
since C# is given, we can probably assume that all this is within a string (also, since the above is not valid C# code, as single quote denotes char). In that case, we need the double backslash, because the single quote is not an escapable character within a string. we want to *actually add a backslash* to the string, that is being passed to mysql. *that's* where it'll be used to escape: `string sql = "... VALUES('drum\\'n\\'bass')"`
David Hedlund
2010-04-20 06:33:53
@David - I am assuming this error is occurring on the SQL server.
Oded
2010-04-20 06:40:39
@Oded: well yes, I'm guessing that's very likely where the error occurs, but since C# is thrown into the mix, I'm also guessing that the SQL string is *generated* by C#, and then you are very much right that OP wants a string that *ends up* like in your example. I was only going into detail as to how such a string is achieved which may or may not have been obvious to OP. This might be a non-issue tho as you've already raised the more important point of not putting yourself in a situation where you need to worry about this in the first place =)
David Hedlund
2010-04-20 13:50:15
A:
If you for some reason need to escape the strings yourself instead of using a parameterised query, you need to escape both backslashes and apostrophes for a literal string in MySQL:
value = value.Replace(@"\", @"\\").Replace("'", @"\'");
This is important, if you don't escape the strings correctly, the query is wide open for SQL injection attacks.
Guffa
2010-04-20 06:33:30
A:
Just to reiterate, you shouldn't be doing this yourself.
Use parameterised queries:
tomfanning
2010-04-20 06:38:15
A:
private string escapeChar(string strToEsc)
{
if (strToEsc.IndexOf("'") > -1)
{
strToEsc = strToEsc.Replace("'", @"'");
}
if (strToEsc.IndexOf("’") > -1)
{
strToEsc = strToEsc.Replace("’", @"’");
}
if (strToEsc.IndexOf("‘") > -1)
{
strToEsc = strToEsc.Replace("‘", @"‘");
}
return strToEsc;
}
klay
2010-05-05 00:17:06