wordpress replacing localhost urls inside base encoded strings saved by theme, pluigns etc

1 Views

For hosting wordpress site from localhost to live server require to change all the localhost urls to the live domain urls. Done and site also loads fine but none of the media items are showing as they are inside base encoded strings.

BUT How to replace urls inside the base encoded strings saved by the theme and other plugins?

Read More

In my case i am using BeTheme and visual composer which contains the actual page content and saved base encoded in the db.
They contains the hardcoded full urls of a lot of media items.

I took the db dump in a .sql file and find and replaced all localhost to live domain url… But this is where i stuck!

Post Views: 1

Related posts

5 comments

  1. I encountered the same problem and found a way to do this.
    My example is for wp_postmeta table and some theme from Muffin

    Requirements:

    • some search-replace plugin (e.g https://wordpress.org/plugins/search-and-replace/ )
    • MySQL version that has from_base64() function (5.6.1 and higher )
    • (probably optional) some meta_key to identify which rows to decode, because decoding base64 for huge table makes db servers sad .

    Steps:

    1. Backup your database(!!!)

    2. Create a table with same layout as wp_postmeta: 

      CREATE TABLE wp_base64_dec LIKE wp_postmeta

    3. Insert into new table base64_decoded values selected from wp-postmeta 

      INSERT INTO wp_base64_dec(`meta_id`, `post_id`,`meta_key`,`meta_value`)
SELECT `wp_postmeta`.`meta_id` AS `meta_id`,
  `wp_postmeta`.`post_id` AS `post_id`,
  `wp_postmeta`.`meta_key` AS `meta_key`,
   FROM_BASE64(`wp_postmeta`.`meta_value`) AS `meta_value` 
FROM `wp_postmeta` WHERE 
  ((`wp_postmeta`.`meta_key` = 'mfn-page-items') AND
  (FROM_BASE64(`wp_postmeta`.`meta_value`) LIKE '%domain.net%')) ;

    4. Use search-replace plugin in wp to replace “domain.net” with “domain.com” in wp_base64_dec table .

    5. Replace the altered columns in the original table:

      REPLACE wp_postmeta
SELECT 
  `meta_id`,`post_id`,`meta_key`,
   TO_BASE64(`meta_value`) as `meta_value`
FROM `wp_base64_dec`

  2. The short answer is: you can’t. Visual Composer, in an attempt to be helpful, base64 encodes URLs in the database. Search and replace (including wp-cli’s version) doesn’t work on these. It’s a huge limitation, and WPBakery is aware of it–but hasn’t offered a solution.

  4. Thanks to @frater_sourcecode for the perfect solution.
    I adopt it for WPML plugin which does not respect RELOCATE directive.

    Here is the code:

    1. create new table
    CREATE TABLE wp_icl_translate_base64_dec LIKE wp_icl_translate;
    1. copy+decode base64 coded records
    INSERT INTO wp_icl_translate_base64_dec(`tid`,`job_id`,`content_id`,`timestamp`,`field_type`,`field_wrap_tag`,`field_format`,`field_translate`,`field_data`,`field_data_translated`,`field_finished`)
SELECT
`wp_icl_translate`.`tid` AS `tid`,
`wp_icl_translate`.`job_id` AS `job_id`,
`wp_icl_translate`.`content_id` AS `content_id`,
`wp_icl_translate`.`timestamp` AS `timestamp` ,
`wp_icl_translate`.`field_type` AS `field_type`,
`wp_icl_translate`.`field_wrap_tag` AS `field_wrap_tag`,
`wp_icl_translate`.`field_format` AS `field_format` ,
`wp_icl_translate`.`field_translate` AS `field_translate`,
FROM_BASE64(`wp_icl_translate`.`field_data`) AS `field_data`,
FROM_BASE64(`wp_icl_translate`.`field_data_translated`) AS `field_data_translated`,
`wp_icl_translate`.`field_finished` AS `field_finished`
FROM `wp_icl_translate` WHERE
(`wp_icl_translate`.`field_format`= 'base64');
    1. run in the console url change
    /usr/local/bin/wp search-replace --all-tables "https://example.com" "https://newsite.com" --dry-run

    (!! –dry-run I use only for demo here !!)

    1. Update records in the original WPML table
    REPLACE `wp_icl_translate`
SELECT `tid`,`job_id`,`content_id`,`timestamp`,`field_type`,`field_wrap_tag`,`field_format`,`field_translate`,
TO_BASE64(`field_data`) as `field_data`,
TO_BASE64(`field_data_translated`) as `field_data_translated`,
`field_finished`
FROM `wp_icl_translate_base64_dec`;
    1. Drop temporary table wp_icl_translate_base64_dec

Comments are closed.