Database Search and Replace Script in PHP

Search Replace DB version 3.1.0 allows you to carry out database wide search/replace actions that don't damage PHP serialized strings or objects with a user friendly interface and experience.


search-replace-mobile-screenshot
Now acts like a web app!

Installation & Use

Please read all instructions and familiarise yourself with the risks of using this tool first, downloading of this tool signifies your acceptance of the risks and problems associated with the script.

To use the script, download the zip file from below, extract the folder called search-replace-db-master (assuming you are using the 3.1.0 version), renaming it to something secret of your choosing, upload it via FTP, SFTP or SCP to your web server’s public facing directory, then navigate to that folder in your browser. The script will automatically attempt to find and populate the database field but you must check that the details are correct and that it is for the database you wish to carry out a search/replace operation on.

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

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

IMPORTANT: Do not place the contents of the script’s folder into your root directory! It isn’t designed or supposed to run from there and, if you use the auto-delete functionality, it will delete everything in that folder, which could wipe out your site. I can’t stress this enough. If this isn’t clear for you it would be a good idea to seek expert help – hire a developer for an hour or two to come and do this for you.

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.

IMPORTANT: This code is supplied with no warranty or support implied. You use it entirely at your own risk. The code is licensed under the GPL V3 and you should be aware of its limitations and how they affect you and your use of this code. Please also read the instructions and warnings provided and always take a verified backup of your database before starting.

Download v 3.1.0

Older server? Try v 2.1.0

Ad

Please beware of adverts that offer a download button!




Why is there an ad here? Because the small amount of revenue this advert might bring us helps to go towards the not inconsiderable cost of running the search/replace project. It’s in the order of thousands of pounds, believe it or not, and we’re working to a new version with better compatibility and this will help.

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 public github repository. Please come on over – you’ll be more than welcome!

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. Amazing! Thank you!
    It only took milliseconds !

  2. Hi, thank you so much for this script. I used it for my first migration from a dev site to a prod site, and it worked wonderfully – the prod site is up and the client loves it.

    Here is where I’m struggling: I found that the site was very slow (I know I still need to update permalinks to pretty ones, do redirects for the old ones, and do caching for the site), so we started looking through the theme settings first, and we found that not all the URLs were changed. We had used the back button and run it several times, and yesterday I ran it again several times, including changing the GUID columns, but that didn’t get everything – there are still many left.

    Here are some examples of the old URL left in from options table:
    http:\\/\\/www.wpwebs.tk\\/bily\\/wp-content,
    newsletter@wpwebs.tk
    Amd from the SlimStats plugin table:
    http://www.wpwebs.tk

    The newsletter one I could see that maybe we need to change our search criteria to leave out the “http://www,” but the others I am really stumped about. I’ve been tempted to run a manual S&R, but then thought perhaps a plugin may be safer. (I’m thinking maybe having the old URLs are sending it to the old site first before the new one loads those parts. Have you any clue why some URLs were left unchanged, or if there is something I might be doing wrong? Any help is certainly appreciated. Thank you.

  3. For some reason this made my site performance faster??? Is it just me?

  4. Changelog:

    // Version 2.1.0:
    // – Changed to version 2.1.0
    // * Following change by Sergey Biryukov – merged in and tested by Dave Coveney
    // – Added Charset Support (tested with UTF-8, not tested on other charsets)
    // * Following changes implemented by James Whitehead with thanks to all the commenters and feedback given!
    // – Removed PHP warnings if you go to step 3+ without DB details.
    // – Added options to skip changing the guid column. If there are other
    // columns that need excluding you can add them to the $exclude_cols global
    // array. May choose to add another option to the table select page to let
    // you add to this array from the front end.
    // – Minor tweak to label styling.
    // – Added comments to each of the functions.
    // – Removed a dead param from icit_srdb_replacer

    // Version 2.0.0 – returned to using unserialize function to check if string is serialized or not
    // – marked is_serialized_string function as deprecated
    // – changed form order to improve usability and make use on multisites a bit less scary
    // – changed to version 2, as really should have done when the UI was introduced
    // – added a recursive array walker to deal with serialized strings being stored in serialized strings. Yes, really.
    // – changes by James R Whitehead (kudos for recursive walker) and David Coveney 2011-08-26

    // Version 1.0.2 – typos corrected, button text tweak – David Coveney / Robert O’Rourke
    // Version 1.0.1 – styling and form added by James R Whitehead
    // Version 1.0.0 – original version by David Coveneysadfasdfasdf

  5. I could not install the plugin into wordpress.

  6. Thank you very much for providing this great tool.

    It was easy to use, and just saved me a lot of time as I was able to use it to perform a search and replace accross my entire site to change the URL’s of image paths.

    Kind Regards, Ash

  7. Yes first of all i would like to say Very Very Thank you for providing such awesome script with good functionality. It clears my lots of doubts but still i have one which is digging in my mind. My Question is its ok for to change paths like site url and others like http://example.com to http://newdomain.com it works great for that. But wordpress storing some paths like absolute urls e.g templates for theme are stored like /home/public_html/template_name.php etc. Now is there any way to identify and replace such paths? I would be happy to hear if any great trick you have for this. Again thanks for great project. 🙂

Comments are closed.