Database Search and Replace Script in PHP

Search Replace DB version 3.1.0 is a user-friendly, front-end tool for developers that allows them to carry out database wide search/replace actions that don't damage PHP serialized strings or objects.

Preamble

It has come to our attention that some users have been leaving this script on their servers in spite of advice to the contrary. Due to the very real dangers it can present when used that way we now ask that you complete a form where we make sure you’re aware of these risks in order to receive the download link. Do also carefully read the installation instructions below.

Installation

Download the script from the link you received by email, and install it to a secret folder with an obfuscated name. Your server should also not be set to provide directory lists.

A typical WP install with this script would have the folders as follows:

/your-secret-search-replace-folder
/wp-admin
/wp-content
/wp-includes

Do not install Search Replace DB to the root folder or in WP’s own folder hierarchy or you risk all sorts of potential problems. Just don’t. It must run in its own folder.

To see how you can use this tool to aid migrations, check out our article on WordPress migrations or visit the WP Tuts+ article that mentions this script.

If you are in any doubt whatsoever about how to use this standalone script, then please consider getting an expert in. It’s a really powerful bit of code that if used badly can damage a WP install beyond repair. If you want help, get in somebody like us, for example, or any of the other great guys listed over at CodePoet.

License

The code is supplied under the GPL V3 and is fully open source. Do be aware that this means people can change this code and offer it up, and that other versions may be worse… or better. It is code for developers, by developers, and you should only use code from sources you trust.

Ad

Please beware of adverts below that offer a download button, but adverts do allow us to partly cover the cost of running this project.




Changelog:

Test 20160504

  • Version 3.1.0 (Web UI and CLI versions) tested against PHP 7.0.6 and functioned correctly.
  • Version 2.1.0 confirmed to not work with PHP 7.0.6 but is kept for use on older servers.

Version 3.1.0:

  • Safety checks to prevent deletion when installed incorrectly. However, you should still take care when dealing with files on your server.
  • JavaScript popup confirmation on ‘Delete Me’.
  • Port number option in both the GUI and CLI. Use –port nnn to set a non-default MySQL port.
  • Fixed Drupal bootstrap behaviour. Start up of script uses Drupal data as guide, no longer relies on a full successful Drupal initialisation before script will allow you to proceed.
  • Driver selection improved so that PDO will be attempted first if PDO+mysql is available, with mysqli being used as a fallback. This fixes ‘driver not found’ errors
  • Removed mysql_ functions and replaced with mysqli_.
  • Improved JS preview overlay for dry runs. This means that the right pane will always show the most accurate data possible. If serialised strings are present, highlights are not displayed.

Version 3.0.0:

  • Major overhaul
  • Multibyte string replacements
  • UI completely redesigned
  • Removed all links from script until ‘delete’ has been clicked to avoid security risk from our access logs
  • Search replace functionality moved to it’s own separate class
  • Replacements done table by table to avoid timeouts
  • Convert tables to InnoDB
  • Convert tables to utf8_unicode_ci
  • Use PDO if available
  • Preview/view changes
  • Optionally use preg_replace()
  • Scripts bootstraps WordPress/Drupal to avoid issues with unknown serialised objects/classes
  • Added marketing stuff to deleted screen (sorry but we’re running a business!)

Version 2.2.0 (never formally released but patched into v3.0.0):

  • Added remove script patch from David Anderson (wordshell.net)
  • Added ability to replace strings with nothing
  • Copy changes
  • Added code to recursive_unserialize_replace to deal with objects not just arrays. This was submitted by Tina Matter.
  • ToDo: Test object handling. Not sure how it will cope with object in the db created with classes that don’t exist in anything but the base PHP.

For changes prior to v2.2.0 please refer to index.php where you will find a complete changelog. You can also browse the project on github.

To Be Done

  • Ensure UTF8 encoding is enforced (see comments). Added in v2.1.0
  • Self deletion or security system to prevent accidental security risks. Added in v3.0.0
  • Release CLI version for use on non-WP sites, or for other purposes (already supports use on any MySQL DB.) Added in v3.0.0
  • Change to GPL V3. Added in v3.0.0
  • Eliminate warnings and remove deprecated function calls. Added in v2.1.0
  • Add facility to subscribe to interconnect/it Newsletter. Added in v3.0.0
  • Confirm deletion has actually happened.
  • Add old versions for download to this page.

Contributions

We’d love to get contributions, bug reports and more on the Search Replace DB github repository. Please come on over – you’ll be more than welcome but you will need to request access by emailing hello@interconnectit.com

Donations

We’ve been asked a lot in the comments box below about accepting donations. But you can’t believe what a headache that is from an accounting and tax perspective.

Consequently all we can say is that if you wish to you can buy a personal gift for the key developers from one of the wishlists below – especially given that it’s a spare time project. If others who have contributed wish to provide us their wishlist links then we’d be more than happy to add them.

1,562 responses to “Database Search and Replace Script in PHP

  1. Is there a way to run this tool to just find the # of occurences of a string in the db without replacing anything? If I try to set search and replace strings to the same value, the tool complains and won’t run, and if I leave replace string empty, my expectation would be that all occurrences of search string will be replaced with the null string. Just getting a count would be very useful in many situations.

    1. Good idea – because of the MySQL MyISAM setup you can’t run in a non-transaction mode easily, so the code has to be a little more careful to not run updates. But I’ll make sure to pencil this in for a future version of the plugin.

  2. Reported a bug about standard wordpress text/html widget yesterday, not sure if it was saved on your site, did not see “Comment awaiting moderation.” message yesterday. If it was saved, ok, delete this, if not, please reply here, then I post again.

  3. Found another bug. In an utf-8 database, if a field in a serialized array contains a german “umlaut” like ÄÖÜäöü, the search-term gets replaced ok, but the length of s is not updated. WP 3.2.1, Simple Local Avatar plugin, ‘umlaut’ in filename, table: user_meta ,field meta_value, search-term: /wordpress/ replace: /

    Before:
    a:5:{s:4:”full”;s:81:”http://www.domainxxx.com/wordpress/wp-content/uploads/2011/09/Xxxxxx-Önxxxxx.jpg”;i:96;s:87:”http://www.domainxxx.com/wordpress/wp-content/uploads/2011/09/Xxxxxx-Önxxxxx-96×96.jpg”;i:200;s:89:”http://www.domainxxx.com/wordpress/wp-content/uploads/2011/09/Xxxxxx-Önxxxxx-200×200.jpg”;i:32;s:87:”http://www.domainxxx.com/wordpress/wp-content/uploads/2011/09/Xxxxxx-Önxxxxx-32×32.jpg”;i:120;s:89:”http://www.domainxxx.com/wordpress/wp-content/uploads/2011/09/Xxxxxx-Önxxxxx-120×120.jpg”;}

    After:
    a:5:{s:4:”full”;s:81:”http://www.domainxxx.com/wp-content/uploads/2011/09/Xxxxxx-Önxxxxx.jpg”;i:96;s:87:”http://www.domainxxx.com/wp-content/uploads/2011/09/Xxxxxx-Önxxxxx-96×96.jpg”;i:200;s:89:”http://www.domainxxx.com/wp-content/uploads/2011/XX/Xxxxxx-Önxxxxx-200×200.jpg”;i:32;s:87:”http://www.domainxxx.com/wp-content/uploads/2011/XX/Xxxxxx-Önxxxxx-32×32.jpg”;i:120;s:89:”http://www.domainxxx.com/wp-content/uploads/2011/XX/Xxxxxx-Önxxxxx-120×120.jpg”;}

    1. Thanks – this will be fixed in the next release.

  4. David,

    Thanks for the response. I agree. It generally does just work. The only strange thing that I sometimes find is that the id numbers of the records in the wp_options table end up changing around a bit. I think that is a result of the fact that the autoincrement value on the source and target DB is not the same and new records are therefore getting added with different id numbers, but like I said, it all seems to work.

    I have considered reseting the autoincrement value on the target DB whenever i import new records, but i havent had a chance to play with it yet.

    Thanks again,
    -J

  5. This is a great tool! It has been saving me a lot of time automating many of the steps that I had been doing manually to migrate my data.

    I am currently using this tool as part of my migration process when I move new releases from dev->test->prod. The process I’ve set up only migrates the database changes as opposed to the entire database. This way I am able to keep all of the existing content in each environment untouched. I find that Toad works really well for comparing the two database instances and makes it very easy to determine what records need to move. For most releases, the only table that is effected is the wp_options table.

    In any case, I was wondering if you had any experience in this area and if so I was looking wondering how you handled the autoincrementer and transient records? I typically leave those objects untouched and things seem fine, but I was curious what others were doing if anything.

    Thanks!
    -J

    1. Generally it should ‘just work’ – I like to clear transients out on a migrate, but I’ve not experienced any significant issues. Where we did, we modded the code to handle the fact that we need to recursively run through our search and replace as transients lead to the bizarre case of serialized PHP strings within, uhm, serialized PHP strings. That caused problems but is solved.

  6. Deprecated: mysql_db_query() [function.mysql-db-query]: This function is deprecated; use mysql_query() instead
    This error is shown when I try to replace with new word.I think mysql script is not run by me.Host provides me the script.Will I tell my host provider to update their script or any other solution Can you give me a suggestion …….

    1. Hi – this problem will be fixed in the next release of the plugin. I know that’s not much help right now, of course, but it’s not far away.

  7. Wow.. This script has saved me HOURS over the last few months. I really appreciate you making this publically available.

  8. I absolutely love this tool. It has worked very well for me, so thank you!

    I didn’t see you mention the WordPress GUID database column anywhere though – the GUID table (inside wp_users) contains the URL when the post was published but is only used as a unique identifier for that post. It should never be changed. Perhaps your script could ignore this column? If someone uses your script on a live site, all previous posts will be sent out to everyone’s feed readers, etc.

    From http://codex.wordpress.org/Changing_The_Site_URL : “Never, ever, change the contents of the GUID column, under any circumstances.”

    Paul

    1. Correction: It’s in the wp_posts table, not wp_users

    2. Hi Paul – good point. If you’re launching a site from a localhost install then, indeed, you want the GUIDs to change, but if you’re simply switching domains then you wouldn’t want this to change.

      It’s a fair bit of work to change and test this because of the automated way that the script works, so we won’t be able to include this on the forthcoming release, but I’ll pencil it for the release after that.

      1. That would be lovely, thanks for the reply and consideration.

  9. Thank you for this!

  10. Are there any issues with this and doing a find and replace in wp_postmeta? For some reason it works on all other tables, except postmeta.

    1. Should just work, but as with everything – there are exceptions. We’re rolling out some new fixes shortly and are in the process of patching in contributions and testing. May be worth trying out the new version when it comes – hopefully next week or thereabouts.

Comments are closed.