How to Search and Replace in a WordPress Database

3. Using  interconnect/it’s “Database Search and Replace Script”

interconnect/it is a well-respected company in the WordPress space and their free PHP script called Search Replace DB is a highly effective search and replace tool. It’s also 100% free, which is pretty awesome.

They actively keep it up-to-date, and in our testing this has actually been the most reliable of all of the methods outlined here. Search Replace DB has been able to catch things that other methods have missed.

The problem is that it’s inherently insecure, in fact, you’ll receive this message in the download email:

Important: Search Replace DB is a professional developer tool, usually for helping with migrations where command line tools are not available. If you do not understand that this tool can easily damage your database then please do not use it and hire a professional. You should ensure that the script is placed in an obfuscated subdirectory that cannot easily be reached from the front end. If you wish to keep the script online permanently for some reason, make sure that as a bare minimum you set up http authentication for the folder in which it resides, otherwise, you will leave your site open to hackers. And we don’t want that, do we?

So we’re also going to look at remedying that before we add it to our server. To do this, we’ll do exactly what they suggest and add HTTP authentication to what will be the scripts URL.

Once we’ve ran our search and replace, we’ll then delete the folder and its contents from our server.

STEP 1. SECURE SEARCH REPLACE DB WITH HTTP AUTHENTICATION

First, we’ll need to SSH into our server. See the links above to get started.

Once inside, we’re going to create an Nginx configuration using the *-main-context.conf.

We’ll call it srdb-main-context.conf.

Create the file with the following command (switch out “site.url” with your websites domain name):

nano /var/www/site.url/nginx/srdb-main-context.conf

For example:

nano /var/www/vCanopy.com/nginx/srdb-main-context.conf

Here, we need to customize the following code block:

location /{custom location here} {
satisfy any;
auth_basic "Restricted Area";
auth_basic_user_file /etc/nginx/.htpasswd;
# Allowed IP Address List
allow 127.0.0.1;
deny all;
}

To customize this, we need to change the top line to a real location. I’m going to upload the scripts on my website to a folder called “super-secret-folder”, so my code looks as follows:

location /super-secret-folder {
satisfy any;
auth_basic "Restricted Area";
auth_basic_user_file /etc/nginx/.htpasswd;
# Allowed IP Address List
allow 127.0.0.1;
deny all;
}

Now we just need to save the file. Writing to and saving a file in nano is a three-step process:

• Hit CTRL-O to write to the file
• Hit Enter to confirm
• Hit CTRL-X to exit the file

STEP 2: TEST YOUR NGINX CONFIGURATION

Next you must test the Nginx config and reload if there are no errors. Do NOT proceed to step 4 if any errors are present and reach out to support.

nginx -t

STEP 3: RELOAD NGINX THEN TEST YOUR HTTP AUTH IS WORKING

gp ngx reload

You should now check your HTTP auth is working. Navigate to the page – this is your website.url/folder-name
For example:

You should see a HTTP auth box:

STEP 4. DOWNLOAD THE FILES FROM INTERCONNECT/IT AND UPLOAD THEM TO YOUR SERVER

Head on over to the interconnect/it website and download the files:
https://interconnectit.com/products/search-and-replace-for-wordpress-databases/

STEP 5. CONNECT TO YOUR SERVER OVER SFTP

Now we’ve downloaded the files, we need to upload them to our server. To do this we need to connect via SFTP:

Connect to a vCanopy Server by SFTP as the Root User

Connect to a vCanopy Server by SFTP as a System User

STEP 6. UPLOAD THE FILES

Extract the zip files and rename the folder to whatever location you added in step 1. I named my folder “super-secret-folder”.
Upload your folder in: www > site.url > htdocs
You’ll see your wp-admin, wp-content, and wp-includes directories in this location.

STEP 7. LOGIN THROUGH HTTP AUTH

We’re now ready to use the tool. First, navigate to the page – this is your website.url/folder-name
For example:

Here you’ll need your HTTP auth login details. To locate them, head over to the sites page inside your GridPane account, and click the log icon:

The username is always “gridpane” and you’ll see a line like below to find your password:

STEP 8. RUN YOUR SEARCH AND REPLACE

The script interface looks like this:

Enter your database credentials. You can find these inside your wp-config.php file.

Back inside your GridPane account, open up your wp-config.php file by opening up your websites configuration modal and clicking “Display wp-config” button:

Now test your connection.
If all is well you can do a search and replace dry run.
Now you can run the full one.

STEP 9. DELETE THE SEARCH REPLACE DB SCRIPTS FROM YOUR SERVER

Please delete the scripts once your finished. Leaving them here is an unnecessary security risk. You can always re-upload them again in the future if needed.
You can click the handy “delete me” button to delete the scripts. I’d highly recommend making sure the files are no longer on your server.