LLLLLLL Posted February 4, 2013 Share Posted February 4, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/273999-importing-a-quoted-db-into-a-non-quoted-db/ Share on other sites More sharing options...
kicken Posted February 4, 2013 Share Posted February 4, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/273999-importing-a-quoted-db-into-a-non-quoted-db/#findComment-1409976 Share on other sites More sharing options...
LLLLLLL Posted February 4, 2013 Author Share Posted February 4, 2013 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? Quote Link to comment https://forums.phpfreaks.com/topic/273999-importing-a-quoted-db-into-a-non-quoted-db/#findComment-1409978 Share on other sites More sharing options...
kicken Posted February 4, 2013 Share Posted February 4, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/273999-importing-a-quoted-db-into-a-non-quoted-db/#findComment-1409984 Share on other sites More sharing options...
Barand Posted February 4, 2013 Share Posted February 4, 2013 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 Quote Link to comment https://forums.phpfreaks.com/topic/273999-importing-a-quoted-db-into-a-non-quoted-db/#findComment-1409998 Share on other sites More sharing options...
trq Posted February 4, 2013 Share Posted February 4, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/273999-importing-a-quoted-db-into-a-non-quoted-db/#findComment-1410000 Share on other sites More sharing options...
LLLLLLL Posted February 4, 2013 Author Share Posted February 4, 2013 I believe you mean the programmer who added magic_quotes to PHP was horrible. It's the most absurd "feature" in any programming language. Quote Link to comment https://forums.phpfreaks.com/topic/273999-importing-a-quoted-db-into-a-non-quoted-db/#findComment-1410015 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.