Download

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.

Installation & Use

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

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.

David Coveney is the project lead.
James Whitehead added the nice UI in v2.0+.
Robert O’Rourke created version 3.0.0 with its AJAX UI and massive improvements.

Comments
  • gal 26 / May / 2011 at 9:07 pm

    Hi david,

    Thank you for the reply. I’m using utf8 coding, as far as i recall. After encountering the problem, i’ve asked my employee to change the url’s to english.

    I think (and hope) the problem will be solved after completing the changes.

    Hopefully, you’ll fix the problem until the next time i’ll use the WSART.

    thanks for answering…

    Gal

  • Ran 26 / May / 2011 at 8:13 pm

    Hi!
    First I want to thank you for this great tool, when it works it can save so much “hard work”. I want it very much to work!!!
    I have 3 questions:
    1) how do you “Ensure UTF8 encoding is enforced” -what is the code we need to put (exactly if it’s possible :)) and where to put it in the script.

    2) I worked with the script, and it seems that he did replaced part of URL’s but also created a little “junk” or extra characters, since I got this ERROR massage:

    Warning: Cannot modify header information – headers already sent by (output started at /home/sp1980/public_html/sacramentolocksmith.co/wp-content/themes/showtime/freshwork/freshpanel/freshpanel.php:2621) in /home/sp1980/public_html/sacramentolocksmith.co/wp-includes/pluggable.php on line 890

    Any idea what happened?

    3) with the “old way” I used to find-replace 4 elements:
    a) http://www.old-domain.com
    b) http://old-domain.com
    c) old-domain.com
    d) old-domain
    The reason is that there are different forms and parts of the main domain in the database.
    Does the script do all this at once or I nedd to run it 4 times?
    Agian, thank you so much for the support and this great tool!

    Ran

  • Tom Kaminski 26 / May / 2011 at 4:17 am

    Founds a couple of problems:
    1. Version 1.0.2 still contains references to 1.0.1. Confusing.
    2. Critical Bug: The code should use is_serialized() instead of is_serialized_string(). Currently, the search and replace routine will break all serialized data that is, for example, an array of strings. To fix, just copy the is_serialized() function from wordpress into this script and update the code to use that instead of is_serialized_string().

    • Markus 30 / May / 2011 at 12:08 pm

      My script does not run at all if I do this at my webhotel. Just a blank page. No error. Even if htaccess is:
      php_flag display_errors on
      php_flag display_startup_errors on
      php_value error_reporting 1024

    • Mark Costlow 24 / Aug / 2011 at 1:43 am

      I ran into the is_serialized_string problem too. I tweaked is_serialized_string to fix it before realizing there was one I could lift from WP. Anyway, here is a quick diff of the fix in case it’s useful:

      if ( preg_match( ‘/^[idbsa]:[0-9]+:.*[;}]$/s’, $data ) ) // this should fetch all serialized strings

  • charlie 25 / May / 2011 at 5:49 am

    it made almost 5000 changes to tables, but it also gave me an error :

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1

    HELP?

  • Gal 16 / May / 2011 at 6:54 am

    what about foreign url’s?
    I’ve encountered a problem when working on an Hebrew blog with Url’s containing hebrew letters – it seems that it has some kind of a problem.

    Does anyone know what could solve it?

    Thanks,

    Gal

    • David Coveney
      David Coveney 18 / May / 2011 at 10:59 am

      Hi Gal – interesting. This may be related to the encoding problem some other people have experienced. In theory, it should Just Work, but obviously it doesn’t for everybody. We’re looking to do a future release which should address problems like this.

      Are you using encoding other than UTF8?

  • Brad 2 / Apr / 2011 at 2:03 am

    Could this script be modified to work on a Drupal installation? This is exactly what I’m looking for to bring sites out of development

    • David Coveney
      David Coveney 18 / May / 2011 at 10:58 am

      Yes – it could, but as we don’t know Drupal we couldn’t really do that for you. Any suggestions?

  • Raina 26 / Mar / 2011 at 1:47 pm

    Thank you very much! I found your video in Youtube and proceeded to download this script. Your tutorial is flawless!

  • Paul 7 / Mar / 2011 at 9:29 pm

    Yes, thank you very much, this plugin did save my arse too.

  • Mike D 17 / Feb / 2011 at 7:39 pm

    Thank you so much… this saved my arse!

  • Rus Miller 17 / Jan / 2011 at 4:51 pm

    Thanks! An awesome solution to a head-scratching PITA problem. This would be a great plugin.

  • mets 12 / Jan / 2011 at 2:39 pm

    excellent work !!!!!!!!!!!

  • Raviraj Deora 12 / Jan / 2011 at 2:22 pm

    Superb script. Works like a charm…Thanks a lot!!!!!!!!!!!!!!!!!

  • Tsultrim 5 / Jan / 2011 at 1:09 pm

    I used this script for a local installation that I wanted to actually host i.e. be viewable by others online. It worked excellently for what I wanted although knowing as little as I do I did make some mistakes which I have yet to work out, such as all new media that I insert have duplicate domains in the location string url. But in any case the script definitely does what it says. Thank you.

  • Cory 29 / Dec / 2010 at 1:58 am

    Merry Christmas to me! Have to launch by the new year… saved me a bunch of time. Thanks!!

  • glitch 25 / Nov / 2010 at 7:22 am

    Great script! Saved me a lot of headaches and time. Thank you so much.

  • yes456 16 / Nov / 2010 at 10:42 am

    thanks u save lot of time 🙂

  • Matt 16 / Sep / 2010 at 3:04 pm

    Looks like most of my example was stripped out. What I meant was on the “search_text” text input (on line 135), inside *value=””* PHP snippet, instead use:

    echo $search_for == ” ? (function_exists(‘get_bloginfo’) ? get_bloginfo(‘home’) : ”) : $search_for;

  • Matt 16 / Sep / 2010 at 3:00 pm

    Such a great script! Saves so much headache.

    Can I make a suggestion? On line 135, perhaps the script should check for the WordPress function ‘get_bloginfo()’ before calling it? I say this because I use this script for other databases than just WordPress (i.e. Drupal).

    How does this look instead?

    <input id=”search_text” type=”text” name=”search” value=”” class=”text”/>

    Thanks again!

  • Bill 25 / Aug / 2010 at 3:33 am

    As the documentation notes, leaving the script in place is a huge security hole.

    I added this line to line 71 of the script – to check if the user is a Site Administrator:

    if (!is_site_admin() ) { echo “Sorry. You’re not a site admin.”; die; }
    // Replace options

    thanks!

    (edited to remove the backticks)

  • Michael Atkins 6 / Aug / 2010 at 12:25 am

    Very useful Dave – Thanks

    I have just moved a local installation of WP to a webhost & made good use of this.

    PS – it was good to meet you at WordCampUK 2010

  • David McDonald 12 / Jul / 2010 at 6:51 am

    OK, after looking into it further, the script is working properly – it was my error.

  • David McDonald 12 / Jul / 2010 at 6:33 am

    This script, in it’s previous versions, has been a life save for me. However, with this version, I notice that it is not actually replacing all the instances of the string within the WP database.

    For example, the following tables:

    – wp_options
    – wp_rg_form_meta
    – wp_rg_lead_detail_long

    still have the old values after running the script. I’m using WordPress 3.0 with mySQL 5.1.41

  • wjm 11 / Jun / 2010 at 8:18 am

    This was a night saver!
    unfortunatly i had to spend a couple of hours figuring out what was wrong..
    ironically, a script that was supposed to avoid data corruption was corrupting data,

    if you are running this script as a stand alone (as i was running version 1.0 http://www.davesgonemental.com/mysql-database-search-replace-with-serialized-php/ )
    you will need to add
    mysql_set_charset(‘utf8’);
    bellow mysql_connect()
    otherwise you will get wrong lenghts in the serialized data.

    another issue has to do with Error Notices.
    there will be so many that you script will die beofre it finishes if your time limit is 30 secs.
    those notices some from using unserialize() with no serialized data.
    to avoid that use this conditional

    $unserialized = @unserialize( $data_to_fix );
    if ( ‘b:0;’ === $data_to_fix || false !== $unserialized ) {

    one last thing,
    this script wont run if its not installed in the root wordpress folder,
    because get_bloginfo() wont be a registered function.
    to solve that,,

    use this

    <input id=”search_text” type=”text” name=”search” value=”” class=”text”/>

    Thanks a lot for your work.
    just a little back in return
    – wjm

  • Sam 10 / May / 2010 at 10:55 am

    Hi Dave

    Really keen to use this script. I have copied it to the root folder (httpdocs) but when I run it I still only get the WordPress Fatal Error screen – no sign of your script.

    Am I missing something?

    Cheers

    Sam

  • […] a good chance that careless use could lead to a lot of damage. Treat it with respect and care! Read more and download the WordPress Database Search and Replace Tool. Interconnect IT WordPress Developers | Spectacula GPL Themes Club | […]

1 2 3 54