Better communicate failure to search-replace JSON-serialized URLs

[danielbachhuber]

From Post Status Slack:

javorszky [5 hours ago] Gravity forms confirmation: goes to https://livesite.com/confirmation-page
I'm on dev. Dev is https://site.test
`wp search-replace https://livesites.com https://site.text`
It does not turn gravity form's form confirmation setting
javorszky [5 hours ago] because the setting is stored in the `rg_form_meta` table in the `confirmations` column as `json`, so the url will look like
javorszky [5 hours ago] `https:\/\/livesite.com\/confirmation-page`

We should better communicate this scenario in our documentation. In fact, it may be time for a dedicated document on search-replacing URLs in the database. Or even, create a dedicated wp search-replace url command, because replacing URLs is such a common use case.

cc @javorszky

[javorszky]

Hm, would it be something that could be actually replaced by the command? I know it already handles serialised data, so (famous last words incoming), how hard would it be to replace the json one?

The good thing is that it doesn't depend on string length, so theoretically both the from and to can be escaped and replaced again. Though that would mean twice as long pass along the entire dbase.

[danielbachhuber]

I know it already handles serialised data, so (famous last words incoming), how hard would it be to replace the json one?

With the current command implementation, you'd need to use regex to also search-replace slashed URLs.

With this being said, if you're only replacing the domain (which it looks like you are in your example), you could drop the https:// from both strings and solely search-replace the domain.

[javorszky]

This just occurred to me:

you could drop the https:// from both strings and solely search-replace the domain.

It will break accounts if I'm signing in via email, because without the protocol, I'm also replacing livesite.com to staging.com in here: gabor@livesite.com -> gabor@staging.com.

[javorszky]

however during the search-replace loop we could check whether the data is json by checking whether it successfully decoded the data and then looping over and setting a json flag, so decode -> replace -> recode -> store

[Pushplaybang]

@javorszky - the user table is a good edge case, and one I think most developers forget about, it'd be great to specifically warn when affecting user data, though that might be out of scope for this issue.

[devkinetic]

UPDATED 12/5/19

From my testing, to properly handle changing a url, you need more information than just the TLD. To your point @danielbachhuber, with the right regex that matches every way a url can be (within reason) it can (mostly) be handled all at once.

DOMAIN=site.com
NEW_DOMAIN=newsite.com
PREFIX=http

wp search-replace --all-tables --regex "https?(:?(%3A%2F%2F)?(\\\/|\/)*)(www\.)?$DOMAIN" "$PREFIX\$1$NEW_DOMAIN"

• Match against http and https
• Match every way :// can be stored in the database. This is based on the sites I've personally come across, and could be expanded. Whatever this ends up being is stored in $1 for use later
  • url encoded
  • escaped slashes
  • unescaped slashes
• Match with or without www
• Match the searched TLD

In the replace portion I reconstruct the url using:

• Normalized prefix
• The matched encoding of ://
• The new domain

@javorszky I then run a follow up command to replace any instance of the domain which isn't an email address!

wp search-replace --all-tables --regex "(?<!@|%40)$DOMAIN" "$NEW_DOMAIN"

These two commands need to be run for each url on the website, so that means if multisite is enabled, it must be run with those urls as well:

DOMAIN=multisite.newsite.com
# first command
# second command

Of course now after reading this thread I now also have to think about user data as well! (thanks @Pushplaybang) I really welcome any feedback and hopefully this can become an easier process through some logic additions to the search-replace command.