charan charan - 4 months ago 17
MySQL Question

how do i insert a special character such as ' into mysql

I am executing the insert query from a shell script which reads data from multiple files. Some of the data to be inserted contains ' in the text and mysql keeps giving errors

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's Development & Empowerment, Youth Affairs
','
Himachal Pradesh ' at line 1


This is the text actual text:"Women's Development & Empowerment, Youth Affairs"

Answer

You need to escape the quote, like so:

'Women\'s Development & Empowerment, Youth Affairs'

Note, that if you're generating the SQL statement from a language like PHP, there are functions available to do this for you.

In PHP, for instance, there is mysql_real_escape_string, which takes care of it for you. Note, that prepared statements are to be prefered over this, as it's harder to get those wrong.

See also:

Comments