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.

Answer 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)+"');";

Answer 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);