By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,795 Members | 1,666 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,795 IT Pros & Developers. It's quick & easy.

MySQL Insert ... Select special character problem

P: 2
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,
Jul 14 '08 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 785
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\"";
Jul 15 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.