[ACCEPTED]-Escape special characters in MySQL using C# and ASP.Net-mysql
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)+"');";
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);
You should use parameterized SQL at least, otherwise 3 your attempts at sanitizing input could 2 leave you open to SQL Injection.
Something 1 like:
cmd.CommandText = "INSERT INTO blogEntry (entryText) VALUE (?entryText)";
cmd.Parameters.Add("?entryText", MySqlDbType.VarChar, 255, textBox1.text);
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) + ");";
http://msdn.microsoft.com/en-us/library/system.text.regularexpressions.regex.escape.aspx
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..
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
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.