Search and Replace of json arrays in the database – switching URL to HTTPS

  • On an E-Commerce site that I admin, we have moved the whole site to https. This has always been a straight forward thing to do except we ran into an issue today.

    Two of the plugins in use store data in the DB as json arrays. Such data can contain URL’s (for images or anchors).

    The string we need to change is in the form:

    http\:\/\/example\.com

    (note the forward slashes)

    The problem: when executing an SQL query, you would need to escape some special characters with the \ escape character. When trying to do a search and replace of the above string, we get an error.

    Since there is a move to json and many plugins thus store that data in the DB, how do we fix this? How do we search and replace

    http\:\/\/example\.com

    with

    https\:\/\/example\.com

    Note: We cannot just search and replace http with https as there are very many external links in posts and comments.

Viewing 4 replies - 1 through 4 (of 4 total)
  • Moderator Steven Stern (sterndata)

    (@sterndata)

    Volunteer Forum Moderator

    Do not do a search/replace like that with a query because if there’s a change in a serialized array, you’ll corrupt things.

    Use this instead. It knows about serialized data and won’t require entering escape chars.

    https://interconnectit.com/products/search-and-replace-for-wordpress-databases/

    Thread Starter Pioneer Web Design

    (@swansonphotos)

    Stern, it’s not serialized data. It’s a json array.

    Thread Starter Pioneer Web Design

    (@swansonphotos)

    It’s longtext and here is an example:

    
{"533f4ef8c31b1":{"isActive":true,"id":"533f4ef8c31b1","name":"Contact Notification","event":"form_submission","to":"{Email:2}","toType":"email","bcc":"[email protected]","subject":"Contact Request Confirmation from Company","message":"<a href=\"http:\/\/domain.com\"><img class=\"alignnone size-full wp-image-736\" src=\"http:\/\/domain.com\/wp-content\/uploads\/2014\/08\/file.png\" alt=\"text\" width=\"300\" height=\"66\" \/><\/a>
//much more

}}
    Thread Starter Pioneer Web Design

    (@swansonphotos)

    Once again, a ‘mod’ posts an incorrect answer and no one else replies as this is now a very buried post. May I re-post without a mod requesting not to?

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Search and Replace of json arrays in the database – switching URL to HTTPS’ is closed to new replies.