Escape special characters in MySQL using C# and ASP.Net

Question

I have a ASP.Net web page with a MySQL back end. The blog content on this site is populated from the MySQL database. When ever I wanted to write a blog entry, I would open MySQL Workbench, right click on the table and select "edit data" and then proceed to type my entry. Well, now I'm working on a C# application that will allow me to write a blog entry so that I don't have to open workbench everytime. Everything so far is working great except for one minor issue: special characters.

==Example==

In textBox1 I will write the following,

I can tell you that this won't work because of the apostrophe

My code looks like this (may be a little off as I'm writing this from memory, but it does work):

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

This works and the text is inserted into my table. But when I pull it back out and bind it to a DataGridView I see this:

I can tell you that this won

and that's it (or I see some wierd formatting or something).

==SUMMARY==

I know it has something to do with the apostrophe not being escaped. So my questions are:

1) In C#, is there a way to go through the entire text in textBox1 and replace all special characters (') with escapes (\') so that they show correctly when pulled from the database?

2) I read about some people using stored procedures or parameters in the INSERT statements. Would that work for me and how exactly would I do that (I'm having some trouble finding examples of my specific case)?

Thanks for any help, ideas, links, etc.

Solution 1

If you are using Mysql client library, You 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)+"');";

Solution 2

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

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

And then something along the lines of:

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

Solution 3

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

Check here.

Something like:

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

Solution 4

You can use Regular expression to replace special chars. Also .Net provide RegEx class which has Escape method to replace special chars which Escapes a minimal set of characters (\, *, +, ?, |, {, [, (,), ^, $,., #, and white space) by replacing them with their 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

Solution 5

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

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

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

lblescape.Text = value.Trim(charsToTrim);

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

View more solutions