Jump to content

Importing a "quoted" DB into a non-quoted DB


Recommended Posts

I need to import a customer database from their site -- with magic quotes enabled -- to my system, where magic quotes are disabled.


When I export the customer DB from their site, obviously all the data contains the magic quotes.


What is the best way to get rid of the quotes?

1) Import into my system and then run [something]?

2) Do some massaging of the export text file prior to import?

3) Something else?


For what it's worth, I'm using a standard cPanel with PHP MySQL Admin tools.

Link to comment
Share on other sites

Having magic quotes on doesn't affect the data returned from the database (unless magic_quotes_runtime is on which is uncommon), it only affects data posted to a script.  Unless the database actually contains slashes in it's data (meaning they originally code it wrong) there shouldn't be any issue pulling the data out as an export then importing it to another DB.


If it was done wrong originally and there are slashes in the data, then have a PHP script generate a .sql file and run the fields though stripslashes before sticking them into the output SQL.


Link to comment
Share on other sites

The original web host IS horrible, so yes, the data has slashes in it. The horrible host is why the customer is switching in the first place.


It will really, really suck to have a script go through each column of each row of each table. Is there no "magic" way to do that?

Link to comment
Share on other sites

You could do something like a SELECT ... INTO OUTFILE and use a REPLACE on any affected fields to remove the extra slashes but it'd probably be just as much work to do that as it would be to write a PHP script (unless it's a small number of table/fields).  Assuming selecting to an outfile is an option for you, which if it's a shared host it probably isn't.


Might be able to do a dump file and do a find-replace using your text editor to remove the slashes also.  Probably something like find \\\' and replace with just \'


Someone else may know of a better solution if it wait a bit for some more responses.

Link to comment
Share on other sites

For the benefit of others with a similar problem, the erroneous sequence of events with magic quotes ON is usually


1. User enter a name like "O'Brien"


2. Magic quotes sends "O\'Brien" to PHP


3. Data is then escaped (without first checking for magic quotes and stripping slashes if it's ON) before inserting in DB so query becomes

INSERT INTO tablename (name) VALUES ('O\\\'Brien')

instead of the correct

INSERT INTO tablename (name) VALUES ('O\'Brien')


4. "O\'Brien" is stored in the name field.


I agree that search and replace in a dump file would be the quickest, correcting the values as shown above

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.