[ACCEPTED]-Escape special characters in MySQL using C# and ASP.Net-mysql

Accepted answer
Score: 23

If you are using Mysql client library, You 1 can use this functions

 MySql.Data.MySqlClient.MySqlHelper.EscapeString("YOUR DATA STRING")

In your case:

MySQLCommand cmd = new MySQLCommand("",conn);
cmd.CommandText = "INSERT INTO blogEntry (entryText) VALUE ('"+MySql.Data.MySqlClient.MySqlHelper.EscapeString(entryText)+"');";
Score: 16

It would be better to use a parameterized 4 query. That way you would not need to escape 3 characters, and it would (more importantly) help 2 prevent an SQL injection attack.

cmd.CommandText = "INSERT INTO blogEntry (entryText) VALUES (@myvalue)"

And then 1 something along the lines of:

cmd.Parameters.AddWithValue("@myvalue", TextBox1.Text);
Score: 1

You should use parameterized SQL at least, otherwise 3 your attempts at sanitizing input could 2 leave you open to SQL Injection.

Check here.

Something 1 like:

cmd.CommandText = "INSERT INTO blogEntry (entryText) VALUE (?entryText)";
cmd.Parameters.Add("?entryText", MySqlDbType.VarChar, 255, textBox1.text); 
Score: 1

You can use Regular expression to replace 6 special chars. Also .Net provide RegEx class 5 which has Escape method to replace special 4 chars which Escapes a minimal set of characters 3 (\, *, +, ?, |, {, [, (,), ^, $,., #, and 2 white space) by replacing them with their 1 escape codes.

MySQLCommand cmd = new MySQLCommand("",conn);
cmd.CommandText = "INSERT INTO blogEntry (entryText) VALUE (" + Regex.Escape(textBox1.text) + ");";


Score: 1

Here's a simple way to escape any unwanted 8 characters. Suppose the characters inside 7 the curly brackets are the characters you 6 want to avoid.

char[] charsToTrim = { 'O','R', '`', ' ', '\'', '=', '1' , '-', ';'};  

string value = " `admin` OR 1=1;-- ";

lblescape.Text = value.Trim(charsToTrim);

The Trim(char[]) will avoid 5 all the unwanted characters but it has its 4 own disadvantages such as you cannot use 3 the character "O" or "R" even though you 2 want them to use, but I guess you can make 1 some arrangements to it..

Score: 0

You would absolutely want to use parameters 6 in this case. It will avoid the escaping 5 issue and also protect you against SQL injection 4 attacks (to which this code is currently 3 vulnerable).

To use parameters, your code 2 would look like:

MySQLCommand cmd = new MySQLCommand("",conn);
cmd.CommandText = "INSERT INTO blogEntry (entryText) VALUE (?entryText);";

Then afterwards, you would 1 initialize the parameter. Something like:

cmd.Parameters.Add("?entryText", textBox1.Text);

More Related questions