I use mysqldump to backup my mysql database. The problem is that the sql file generated by mysqldump doesn't escapes single quotes properly.

Here is an example of the mysqldump generated sql script :

INSERT INTO `someTable` VALUES (1,'This ain\'t escaped correctly');

That single quote escaping in "ain't" doesn't work and it makes the rest of the script being inside that string. Is there a way around this?

migrated from stackoverflow.com Nov 30 '12 at 4:13

This question came from our site for professional and enthusiast programmers.

  • 1
    that is properly escaped. but remember that escapes are removed at each level of processing. you may need double-or-more escaping, depending on what you're doing to it. – Marc B Nov 29 '12 at 21:26

That output of mysqldump is working as designed, and it is properly escaped, unless you try to restore the dump on a MySQL instance with SQL_MODE=NO_BACKSLASH_ESCAPES set.

There's an outstanding feature request to make mysqldump use a pair of single-quotes to escape literal single-quotes, as per ANSI SQL. See http://bugs.mysql.com/bug.php?id=65941

In the meantime you might be able to convert from backslash-singlequote to pair-of-singlequotes with a command line this:

mysqldump test | sed -e "s/\\\'/''/g" > test-dump.sql

I tried that out briefly by creating a dummy table in my test database and inserting the string "O'Hare" into the table. But that's hardly a comprehensive test -- I take no responsibility for this suggestion working in all cases.

  • You're right it's escaping correctly, I thought it wasn't because in notepad++ the rest of the text is gray after that line. The problem is that it didn't generate the "use database" so it gives error no database selected – user1864537 Nov 29 '12 at 21:42
  • If you name a database when you create the dump, or if you use --all-databases, it should include the "use" commands. – Bill Karwin Nov 29 '12 at 22:29

Bill Karwin's answer is really good, and even I was using a similar solution, until yesterday when I got a message field which has a "\" character, which was escaped with "\" and as it was the last character of the field, the next character was a single-quote, marking the end of the string field content. Now, here's the string a the solution below, I used Perl regex "look-behind":

INSERT INTO "My_table" VALUES ('Table_1','316947','pradeep@yahoo.com','Richard','asha@pradeep.net.in','Skip\\','HAPPY VISHU<br></b>\r\n\r\n\r\n')

Solution: with 2 examples:

[pradeep@ubuntu ~]# cat /tmp/a.txt
INSERT INTO "My_table" VALUES ('Table_1','316947','pradeep@yahoo.com','Richard','asha@pradeep.net.in','Skip\\','HAPPY VISHU<br></b>\r\n\r\n\r\n')
INSERT INTO "My_table" VALUES ('Table_1','316947','pradeep@yahoo.com','Richard','asha@pradeep.net.in','Pradeep\'s Skip\\','HAPPY VISHU<br></b>\r\n\r\n\r\n')
[pradeep@ubuntu ~]# cat /tmp/a.txt | perl -pe 's/(?<!\x5c)\x5c\x27/\x27\x27/g;' | perl -pe 's/(\x5c\x5c)+\x5c\x27/$1\x27\x27/g;'
INSERT INTO "My_table" VALUES ('Table_1','316947','pradeep@yahoo.com','Richard','asha@pradeep.net.in','Skip\\','HAPPY VISHU<br></b>\r\n\r\n\r\n')
INSERT INTO "My_table" VALUES ('Table_1','316947','pradeep@yahoo.com','Richard','asha@pradeep.net.in','Pradeep''s Skip\\','HAPPY VISHU<br></b>\r\n\r\n\r\n')

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.