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

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.

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

  1. The script is not working for me. My aim is to replace the old domain name to new domain. After i filled the necessary and i hit live button and it runs without changing anything. Any help give me necessary step to follow?

    1. No one to help me here?

    2. There are a great many reasons why it might not work – security, connection, server config and so on.

      You can get a developer to help you look into this if you need it, but they’ll usually charge. It should just work by following the on-screen instructions. You could always try an older version of the script.

    3. It’s quite tricky – you don’t give a lot of information. For example, which version are you running, what does the dry run look like, and what are the strings you’re using?

  2. Awesome script!
    I had to move a wordpress installation with domain-change. The script did exactly what to do and everything works after the search-and-replace.

    Thank’s a lot.
    Chris

  3. GREAT TOOL! I tried to use the 3.1 version in an Azure environment and it shows an AJAX error, I tried with the 3.0 and worked perfectly. Any idea?

    1. Occasionally people do experience AJAX errors – we suspect mostly it’s a time out but sometimes there are messages in the system log hinting at this.

      What happens if you do one table at a time?

  4. I’ve been using your script for many years but I never thought of dropping a line to let you know you are awesome. So here you go:

    YOU ARE AWESOME!

    Grazie mille!

  5. This work for me! thank you.

  6. thanks alot for this script.
    Helped me change all my domain url easily. Had been pulling my hairs to do this task but this tool made it so simple
    thanks again and you are awesome bro

  7. This is the best way to change files routes when you move your wp site, thank you very much!

  8. Thanks!!!! 🙂

  9. I like this one and use it very often.

  10. it working great. thanks for the tool

  11. ¡EXCELENTE! Una magnífica herramienta que me ayudó a solucionar el problema que tenía un cliente por una base de datos con urls diferentes. Son unos cracks! Muchas gracias. Saludos desde México

  12. We really love this tool ! One click and all entries are changed easily.

    We appreciate your hard work interconnectit team !

  13. Hi, great tool!

    Question, if you have a dump, can we still use this script?

    1. No – it works against running databases. So you can load the dump into a new DB and then run the script, but not run it against the text.

  14. Why are you base64’ing code in your script? mod_security is flagging it as malicious. Seems to be a new issue.

    1. It’s not code that’s base 64’d but images. The idea is to show branding without fetching it from a server. To be fair, given that it’s no longer a single file application that could now be included in the checkout. So I’ll add a case to fix that. Thanks for highlighting the issue.

      1. Thanks! Looking forward to a fix!

  15. What a great tool! So nice of you to make this available for free. It works really well. Thank-you so much!

  16. Hi
    Thanks for that tool!!!
    I’ve updated a WP installation with Avada theme and got the following message 8 times (second update from .dev. to .staging):

    2:Class__PHP_Incomplete_Class has no unserializer in /home/glppanar/www/staging.glp.ch/snr/srdb.class.php in line 735

    But everything looks ok …
    Any idea what happen?
    Thank you
    H

  17. Hi,

    The file “srdb.class.php” appears to be missing from the package today

    Thanks for the script.

    1. I see it in the src/ directory.
      But get this error when running from the command line:

      Warning: require_once(/home/xxxx/bin/srdb.class.php): failed to open stream: No such file or directory in /home/xxxx/bin/srdb.cli.php on line 14

      Fatal error: require_once(): Failed opening required ‘/home/xxxx/bin/srdb.class.php’ (include_path=’.:/usr/lib/php:/usr/local/lib/php’) in /home/xxxx/bin/srdb.cli.php on line 14

      1. I’ve temporarily copied src/srdb.class.php one directory up to get it going

    2. Apologies – a change got released by accident. Should be fine now.

  18. Hi, I don’t have too much experience with databases and I’m having a difficult time with what this seems to solve. How do I access the folder from my browser exactly?

  19. I am using Visual Composer to create my sites, but it seems that the script is not replacing links that I created inside Visual Composer. Do I have to change them manually?

    1. Hi, I’m having the exact same problem with my site, I’m using Velvet Blues Update URLs plugin and it replaces all links in my site except for the ones in buttons I’ve created with Visual Composer of which there are quite a few. I’m desperately trying to find a solution as manually changing the links is quite a mission – did you find a solution?

      1. Same problem here! Do you found a solution?

  20. Hi guys

    Just wanted to say thanks so much for this marvelous tool – as a noob theme developer it’s saved my sanity on multiple occasions now and I don’t know what I’d do without it.

    Much love!

  21. The package could not be installed. No valid plugins were found.

    Plugin install failed.

    1. It’s not a plugin. Please read the instructions. Or get someone who’s used to this kind of work.

  22. With newest version (4.6.5.2) of phpmyadmin this tool doesn’t work. Desecription in on image: https://s27.postimg.org/rp4p6lr9v/db_replace_issue.png

    1. Hi – this script has nothing to do with phpMyAdmin.

      Make sure you’ve followed the instructions correctly when installing.

  23. Thanks a lot!
    Very good tool!

  24. hi. thanks for this nice tool. i have two questions. how can i search & replace a multi line string (e.g. paragraph)? is this tool allows just one line?

  25. Inspiring work! This solves a very pervasive problem with many CMS migrations.

  26. Just wanted to say thanks for this amazing tool. You rock.

  27. how come you guys don’t have a hover effect on the submit button below…jk Thanks for this sweet tool.

  28. Thank you very much for this great script. It was a life saver for me during a wordpress multisite migration. You guys rock 😉

  29. Great tool, especially the ‘dry run’, to test before you do.

  30. Hi there, thanks so much for this tool, it’s really a great help.

    One thing – does it work if the database’s table names have been changed from the default wp_ ?

    Thanks

    1. Yes. It works on any table, including non-WP ones.

    2. I had the same. I think the problem was that I had capital letter in my prefix. Just changed it to small one in wp-config 🙂

  31. This is such a great tool, thanks a lot !!! It saved me a lot of time and headaches…

  32. I read about this script from our host provider and just did our 1st run after a site migration. Pretty awesome! And the dry run feature is very cool. Nice job!

  33. Great tool!

    Just a heads up on some records it seems not to be able do to the replaces:

    – table wp_options, _wc_session_* records: https://snag.gy/xIV25L.jpg https://snag.gy/5V09ny.jpg

    – table wp_followup_customer_carts: https://snag.gy/mBW317.jpg

    1. There’s no particular reason why it wouldn’t work for this other than, perhaps, the row being locked when you tried to update. Is this on a live website? You may have to close it for maintenance first.

  34. I’m getting 6 false positives on wp_options and 41 on wp_postmeta whenever i search for a string that doesn’t exist. viewing the details shows the same before and after with no instance of the string. any ideas?

    1. Sounds peculiar – what sort of string are you searching for?

  35. escape characters? I’m trying to replace (really just get rid of) a string that includes a call to a script that’s all over my wordpress pages/posts … there’s single quotes, slashes, etc. Do I have to escape characters to get it to work? It seems to do a good dry run if I search for just part of the url.

    1. The search is rather absolute in how it works. If you search for & it won’t find the HTML & – it’ll just find the first character.

  36. Fantastic tool! Works like a dream… saved me hours.

  37. Hi is this possible to replace links with multiple database?

  38. Been using the tool for years. For the most part it has been bulletproof, Thanks for that! However just came across a situation where it is failing. Working on a Drupal DB. Need to update a single table/field ‘field_data_body’ / ‘body_value’ . Search/Replace strings ‘src=”/sites/default/files’ | ‘src=”/subfolder/sites/default/files’. The result states that 377of533 rows have been updated, however the reality is ALL 533 ‘body_value’ fields (the entire table) have been replaced with identical data that appears to be from one of the other rows. Thanks for any assistance.

    1. That’s strange. I mean, searching for things with a slash in is common and normal.

      Is there anything particularly strange about the data or table format? We get a lot of queries, but rarely a database dump to work with.

  39. Bless you for offering this fantastic tool for free. 😀

  40. First of all, congratulations on the tool, but I have to say that when I give Live Run, it does not update any entries, it does the same as when I give a dry run. Am I doing something wrong?

  41. Hi, I’m using v2.0.1 of your script with Server version: 5.6.24 MySQL and getting the following error.

    Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 5927928 bytes) in /home10/sunpatil/public_html/searchreplacedb2.php on line 204

    Please guide me.
    Thank you.

  42. Hi,

    Thanks for the tools.
    i’m working on wordpress and first of all i want to have a full control of the environment.
    For that purpose, i try to transfert a WP environment and db fron server on line on my local machine.
    And … trouble
    When I launch the program from the following dir
    Applications/XAMPP/htdocs/locdb/Search-Replace-DB-master/
    php srdb.cli.php -h localhost -n locdb -u user -p pwd -s “mydb.com” -r “localhost/locdb”
    I get :
    db: SQLSTATE[HY000] [2002] No such file or directory

    And we’re done!

    But nothing change in db

    I don’t find any response to this trouble on the web.
    Iuse :
    XAMPP for OS X 7.0.13
    PHP Version 7.0.13
    Apache/2.4.23 (Unix) OpenSSL/1.0.2j PHP/7.0.13 mod_perl/2.0.8-dev Perl/v5.16.3

    Thanks in advance for your help

    Claude

  43. Hello,

    it works for prestashop ?

    Thanks,

  44. Great tool. I have just bought the Performance profiler to say thank you. I hope it works as well as S&R does 🙂

    DW

  45. I get this error while running searchreplacedb, how to fix this?
    mysql_set_charset() expects parameter 2 to be resource, boolean given in …/…/…/searchreplacedb2.php on line 455

  46. Thanks for this! It’s been very helpful on many occasions.

    Question: How can I get the Replace feature to add a newline character to the database?

  47. Hey thank yu for this tool, but the live run isn’t making any changes to the database. Could you help please? It says 0 cells were changed. 0 db updates were performed.

Comments are closed.