11

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.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
charan
  • 186
  • 1
  • 2
  • 11
  • this thread may help you : [Escaping MYSQL command lines via Bash Scripting](http://stackoverflow.com/questions/4383135/escaping-mysql-command-lines-via-bash-scripting) – jujule Jan 26 '11 at 10:17
  • Its better if you escape ' character with ' than \. Its because some db ex: sqllite show error with \ escape character. You can write: "Women''s Development & Empowerment, Youth Affairs." – YROjha Sep 15 '17 at 07:25

6 Answers6

19

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:

Community
  • 1
  • 1
Sebastian Paaske Tørholm
  • 49,493
  • 11
  • 100
  • 118
  • 1
    and use [mysqli_real_escape_string](http://dk2.php.net/manual/en/mysqli.real-escape-string.php) for newer versions – otaku May 02 '18 at 18:36
3

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.
RobinG
  • 196
  • 6
  • I use mysql 5.7 and \' have error in long text with collation uft8mb4, but \" has not error !!!!!!!! – MrSalesi Aug 02 '19 at 17:47
1

Yo need to escape the ' character with a backslash \

Women\'s Development & Empowerment, Youth Affairs

sissonb
  • 3,730
  • 4
  • 27
  • 54
  • I have around 30k files and I do not want to use sed to have to do a find and replace. I would rather like to handle at the mysql end if it is possible. – charan Jan 26 '11 at 10:20
  • You'll need to use a programming language or a do a find and replace over the file to replace ' with \' – sissonb Jan 26 '11 at 10:27
1

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.

borrible
  • 17,120
  • 7
  • 53
  • 75
0

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

Harish
  • 2,311
  • 4
  • 23
  • 28
  • I am trying process some files and push some data into mysql. I have written a small shell script for this. – charan Jan 26 '11 at 10:27
0

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]'
buddemat
  • 4,552
  • 14
  • 29
  • 49
Surya Pratap Singh
  • 1
  • 2