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

[LLLLLLL]

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.

[kicken]

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.

 

[LLLLLLL]

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?

[kicken]

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.

[Barand]

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

[trq]

The original web host IS horrible, so yes, the data has slashes in it.

 

You mean the original programmer was horrible. This has nothing to do with the hosting.

[LLLLLLL]

I believe you mean the programmer who added magic_quotes to PHP was horrible. It's the most absurd "feature" in any programming language.