BlueDogRanch BlueDogRanch - 3 months ago 17
MySQL Question

Find https:\/\/ and replace with http:\/\/ using phpMyAdmin

I've got an issue with a find/replace failing in phpmyadmin. I'm thinking this must have to do with the backslashes escaping the slashes in the URL in the database and I'm not correctly accounting for or escaping the backslashes in my query.

The data is stored like this in the

wpiv_postmeta
table in the
meta_value
column, with the slashes in the URL escaped:

(more data) [{"ddl-repeat-id":"4","logo":"https:\/\/example.com\/uploads\/2016\/01\/mypng.png"} (more data)


I need to remove the
s
in the URL. I'm using

UPDATE wpiv_postmeta SET meta_value = replace(meta_value, 'https:\/\/example.com', 'http:\/\/example.com');


but I get no matched rows when I simulate it in phpMyAdmin.

And I don't want to run this because it's too greedy:

UPDATE wpiv_postmeta SET meta_value = replace(meta_value, 'https:', 'http:');


How do I escape or unescape the slashes in my UPDATE query?

Answer

You have to double-escape, since the SQL parser removes one level of escapes while processing the query string:

SELECT ... WHERE foo LIKE '%\a%' // find any 'a' in the db
SELECT ... WHERE foo LIKE '%\\a%' // find any '\a' in the db

So you'd need

... REPLACE(meta_evalue, 'http:\\/\\/', 'http://') ...