CODEWITHSUNDEEP
mysql
charan
charan

Reputation: 186

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 actual text: Women's Development & Empowerment, Youth Affairs.

Upvotes: 11

Views: 93179

Answers (6)

Surya Pratap Singh
Surya Pratap Singh

Reputation: 1

I am trying process some files and push some data into mysql. I have written a small shell script for this.

SELECT *
  FROM tableName
 WHERE columnName REGEXP '[^a-zA-Z0-9]'

Upvotes: 0

Harish
Harish

Reputation: 2324

What is the server side languge you using?

there are many methods through which you can escape string , i'll prefer that!

Eg: you can use mysql_escape_string the data you are entering in query so it will automatically escape charecters like " ' "

mysql_escape_string is a php function provided you are using php

Upvotes: 0

RobinG
RobinG

Reputation: 196

You will have to escape the input strings before passing them to MySql.

The list of escape characters is: 

Character   Escape Sequence
\0  An ASCII NUL (0x00) character.
\'  A single quote (“'”) character.
\"  A double quote (“"”) character.
\b  A backspace character.
\n  A newline (linefeed) character.
\r  A carriage return character.
\t  A tab character.
\Z  ASCII 26 (Control-Z). See note following the table.
\\  A backslash (“\”) character.
\%  A “%” character. See note following the table.
\_  A “_” character. See note following the table.

Upvotes: 3

borrible
borrible

Reputation: 17346

You need to escape your quote. You can do this by doubling it in your insert query; i.e. use '' rather than '. That is two single quotes rather than a single double quote.

Upvotes: 1

sissonb
sissonb

Reputation: 3780

Yo need to escape the ' character with a backslash \

Women\'s Development & Empowerment, Youth Affairs

Upvotes: 1

Sebastian Paaske Tørholm
Sebastian Paaske Tørholm

Reputation: 50858

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:

Upvotes: 19

Related Questions