How can I automatically escape quotes in content when exporting from MySql to an sql file?

We have a Joomla website which we want to copy over to another host website. So far everything has been copied except for the database. The problem we're experiencing is as follows:

After exporting the database information from our old website into an .sql file and trying to import it into an empty database on our new host, it seems text (content) on the website seems to break off at various points. I've found out that it's because of the single quotes ' in the text, such as "You're welcome". Instead of those two words, it only says You. I know that the quotes break up the SQL queries (like in an SQL injection), but how would I go about properly importing the database? Or do I have to change the way I export it first?

W开发者_如何学Pythone are using phpMyAdmin to export (and import) our information.

I realize this is an old post but there is also a fairly simple solution for a find/replace.

If you use a text editor that supports regex find/replaces (I use Sublime Text 2), you can quickly throw something together to match a pattern. This probably won't work for all data, but it has worked for me in instances I have had this issue.

You'll want to search for:

(\w)'(\w)

and replace with:

$1\\'$2

This will find any word character with a single quote and another word character next to it and replace with an escaped version. In most cases I deal with the problems are in text with English contractions so this works out for this scenario. It may not work in your case.

I've experienced the same thing, I think it's a bug in the phpMyadmin export function. There is no good way that I know of to clean up the dump due to the fact that you can't do a simple search and replace to escape the quotes.

There is a very simple solution however. Use JoomlaPack (now called Akeeba Backup) when you need to move sites. It packs the entire site and DB in to a nice neat ZIP file and it includes a quick installer. Shouldn't take more than a few minutes to move an entire site and be up and running.

http://extensions.joomla.org/extensions/access-a-security/site-security/backup/1606