Menu
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 actual text: Women's Development & Empowerment, Youth Affairs.
521
Use mysqli_real_escape_string() to Insert Special Characters Into a Database in PHP. To get user input with special characters from the form fields, we use the mysqli_real_escape_string() function. We need the following parameters: database connection and the strings we want to escape.
INSERT() function The original string, the string which will be inserted, a position of insertion within the original string and number of characters to be removed from the original string - all are specified as arguments of the function. Original string. Position of insertion within the original string.
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:
167
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
