Tutorial :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?


Solution:1

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.


Solution:2

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.


Solution:3

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","\"")  


Solution:4

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 "".


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »