How to format string parameters before sending to MySql in .NET?

Question

How can I check a string for incorrect MySQL syntax caused by (') ?

for example: We Bike'd fast

I need to get rid of the ' before inserting it into the Database.

See this: http://stackoverflow.com/questions/229889/sanitizing-mysql-user-parameters — Austin Salonen, Dec 22 '11 at 17:58
Are you looking for a built in method that handles all MySQL special characters or just a way to look for and remove a character you specify? — cadrell0, Dec 22 '11 at 17:58
Say I have a bunch of variables containing strings that will then be put into a MySQL query. I need to some how validate that there are no characters in that insert query that would cause an error. The only character that I know does this would be the ' symbol — MDL, Dec 22 '11 at 18:00
Instead of manual string escaping, why not use Parametrized SQL that will handle this for you? — Luke Hutton, Dec 22 '11 at 18:04
I'll have to look into parameterizing my query I guess, but i was kind of just looking for the quick and dirty way to accomplish this. — MDL, Dec 22 '11 at 18:08
Yes, you will get the benefit of stopping SQL injection, and other benefits such as performance, see http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html Quick and dirty here not the best since having Parametrized SQL across the whole app will help security and performance and your coworkers will like you ;) — Luke Hutton, Dec 22 '11 at 18:11
Side note: "We Bike'd fast" is bad grammar. :) Should be "We biked fast". I'd suggest "We biked quickly", but to me that means "We quickly started biking". — cHao, Dec 22 '11 at 18:12

score 5 · Answer 1 · answered Dec 22 '11 at 18:03

5

You should use MysqlCommand and command paramters to build your insert statement, this will do the escaping for you.

Alternatively there is a EscapeString method you can use.

answered Dec 22 '11 at 18:03

ChrisWue

18,612
4
58
83

Olivier Jacot-Descombes · Accepted Answer · 2011-12-22T18:45:34.573

0

The simple answer is:

textValue = textValue.Replace("'","''");

But it is usually better to use command parameters.

See SqlCommand.Parameters Property on msdn. (I do not know the MySql equivalent)

EDIT:

Example with Replace:

string sql = String.Format("SELECT * FROM tbl WHERE name ='{0}'",
                           textBox1.Value.Replace("'","''"));

edited Dec 22 '11 at 18:45

answered Dec 22 '11 at 18:03

Olivier Jacot-Descombes

104,806
13
138
188

Thanks, I like the .Replace way to change the string. Could I just do Value.Replace("'", "") after getting the string for my variable? – MDL Dec 22 '11 at 18:11

score 0 · Answer 3 · answered Dec 22 '11 at 18:04

You can add the using System.Web directive to your .cs file and use the HTMLEncode which will then replace ' with &apos but when you read it back out you'll have to call HTMLDecode() to convert it back to '.

This you would use before you create your parametrized statement on the front end. Then pass the end result as a parameter to your sqlCommand.

How to format string parameters before sending to MySql in .NET?

3 Answers3