MySQL Insert ... Select special character problem

[bdparnes]

I have a project where it is necessary to copy rows (and change a few pieces of information); however some of the rows may contain special characters such as an apostrophie or quotes. I am using the INSERT INTO ... SELECT statement to accomplish the copy fairly quickly... however for those rows with special characters, I get a Syntax error. Is there an easy way around this, or do I need to abandon the INSERT INTO ... SELECT shortcut?

ie: "INSERT INTO `table` (field1,field2,field3) SELECT field1,field2,field3 FROM `table` WHERE field0=1" returns a syntax error if any of the fields contain a special chracter.

Thanks,

[chaarmann]

Set all fields of the destination table to UTF-8 character set (or better: the character set you used in your source table), then you should have no problem with copying the way you described, that means from one table to another.
But if you want to insert/search for values given as constant string in your program, then you should escape these characters. Just replace:

• - single quotation marks with 2 single quotation marks (helps against malicious code insertion attack, too)
  - backslashes with 2 backslashes
  - special characters with a backslash and the special character

Example: search for "mum's C\A_R smiles"
string q = "select * from table1 where column1 like \"mum''s C\\A\_R smiles\"";