How can I replace ” with " in a mysql Query?

Question

I am trying to run this SQL from ASP.NET 2005 but am getting an invalid SQL error because it has a weird character ” in it.

In my code I am trying to replace ” with " but it is not doing it as the special character in the replace command is changing to ".

Query:

insert into userquery(description)
  values ('In fact, Topeka Google Mayor Bill Bunten expressed it best: “Don’t be fooled. Even Google recognizes that all roads lead to Kansas, not just yellow brick ones.”')

If I copy and execute this in mysql it is working good.

How can I solve this problem?

Please see the weird character in the Title. – vamsivanka Apr 02 '10 at 18:33 — vamsivanka, Apr 02 '10 at 18:33
Can you post the exact error message? – Andomar Apr 02 '10 at 18:40 — Andomar, Apr 02 '10 at 18:40

Joel Coehoorn · Accepted Answer · 2010-04-02T19:11:07.703

You're building your query wrong. You didn't say c# or vb.net on the client, but either way your sql string should look more like this:

string query = "insert into userquery(description) values (@description)";

And then you set your description value like this:

using (var cn = new MySqlConnection("... your connection string here..."))
using (var cmd = new MySqlCommand(query, cn))
{
   /* THIS IS THE IMPORTANT PART */
   cmd.Parameters.Add("@description", SqlDbType.VarChar).Value = MyDescriptionVariable;
   /*****************************/

   cn.Open();
   cmd.ExecuteNonQuery();
}

And the vb version:

 Using cn As New MySqlConnection("... your connection string here..."), _
       cmd As New MySqlCommand(query, cn)

   ''# THIS IS THE IMPORTANT PART
   cmd.Parameters.Add("@description", SqlDbType.VarChar).Value = MyDescriptionVariable
   ''############################

   cn.Open()
   cmd.ExecuteNonQuery()
End Using

This will make sure your parameter is properly escaped, no matter what odd characters you have.

score 4 · Answer 2 · edited May 23 '17 at 11:55

You should write the query like this, using N before the text:

insert into userquery(description) values (N'In fact, Topeka Google Mayor Bill Bunten expressed it best: “Don’t be fooled. Even Google recognizes that all roads lead to Kansas, not just yellow brick ones.”')

And ensure that the field is nvarchar o ntext (the latter has been deprecated in favor of nvarchar(max) btw.).

Anyway, you should follow Joel Coehoorn recommendation an use parametrized queries to avoid these kind of problems and many others (like SQL injection) because the strings pulled into the database as parameters are sanitized and escaped before reaching the db.

score 0 · Answer 3 · edited May 23 '17 at 10:32

The actual error message would be helpful. if it's a unicode issue you could try:

Inserting an N:

insert into userquery(description)
  values (N'In fact, Topeka Google Mayor Bill Bunten expressed it best: “Don’t be fooled. Even Google recognizes that all roads lead to Kansas, not just yellow brick ones.”')

Or see: replace MSWord smart quotes in asp.net webform

mystring.Replace("\u201C","\"").Replace("\u201D","\"")

score -1 · Answer 4 · answered Apr 02 '10 at 18:48

-1

The ASCII value of that double quote is 147.

Good Ol' VB6 Immediate Window:

?asc("“")
 147

You can do a replace on Chr(147), with an escaped double-quote: Chr(34) & Char(34) for "".

answered Apr 02 '10 at 18:48

HardCode

6,497
4
31
54

There's no such thing as ASCII character 147. – Josh Lee Apr 02 '10 at 18:52
Tell that to VB6. It reports 147. – HardCode Apr 05 '10 at 20:43

How can I replace ” with " in a mysql Query?

4 Answers4