How to use Regex with WP CLI to Search & Replace WordPress Database

WP CLI database search replace regex

WP CLI is the command-line tool of choice when it comes to managing and administering tasks within WordPress. Combined with the power of regex (a sequence of characters defining a search pattern), it enables powerful search and replace functions.

WP CLI documentation on executing a search and replace on the database using regex can be confusing. Here’s a one liner that works and can eliminate some confusion. Feel free to tweak and play (on a sandbox install).

Table of Contents

  1. Why Choose WP CLI For Search & Replace?
  2. Prerequisites for Utilizing WP CLI & Regular Expressions
    1. Breakdown of What WP CLI Search Replace Command
  3. Safety With WP CLI & Regex
    1. Bonus:
    2. References:
    3. See Also:

Why Choose WP CLI For Search & Replace?

  1. Data-Integrity: Data within the WordPress database is serialized. Altering this data will break the database integrity.
  2. Serialized Data: WP CLI deserialization the data before altering it and serializes it again before saving it.
  3. Efficiency: Enables quick, efficient and precise operations.
  4. Flexibility: Tailors search-patterns to specific needs.
  5. Safety: Dry-Run allows you to test commands and gives verbose output before you decide to commit.

Prerequisites for Utilizing WP CLI & Regular Expressions

Before embarking on this task, you must ensure access to a WordPress installation and WP CLI. It is safer to perform these operations on a sandbox install to mitigate risks. See also: Download WP CLI Temporarily & Using With Custom PHP Versions

You also need to have some understanding of Regular Expressions.

Do not forget to replace the regex with your own.

This is the first thing you should try before trying something crude like replacing via PHP.

wp search-replace '<script.*?217bc9f0d28b2da070\.js.*?<\/script>' '' --all-tables --dry-run --report-changed-only --precise --regex --regex-delimiter='/'

Breakdown of What WP CLI Search Replace Command

–all-tables runs the search-replace on all WordPress tables in the database.
–dry-run only shows what will be changed without actually changing anything.
–report-changed-only reports changed fields only.
–precise forces the use of PHP (instead of SQL) which is more thorough, but slower.
–regex tells WP CLI to search using the regular-expression we provided.
–regex-delimiter sets the regular-expression delimiter to ‘/’ which is the standard way of using regular-expressions in PHP.

Safety With WP CLI & Regex

  • Dry Run: Utilize the --dry-run option.
  • Backup: Do a quick database backup with wp db export.
  • Understanding: You can use online tools like RegExr 3 to understand your regex pattern.

Bonus:

Additionally, you can also pass PCRE modifiers to regex search-replace (e.g. ‘i’ for case-insensitivity) using the syntax: [--regex-flags=].

Did you know you can deploy Malcure Advanced Edition en-mass? Check this: Deploying Malcure Advanced Edition With Cron, WP-CLI for Automatic Periodic Scans

References:

  1. WP CLI Search Replace Command
  2. WP CLI
  3. RegExr ^

See Also:

Written by
Principal Security Researcher, Malcure Web Security

Shiv has worked in security and infrastructure since 2002, with hands-on experience across enterprise network security, incident response, problem coordination, triage management, Windows and Linux systems provisioning, scripting automation, Nginx, ModSecurity, reverse proxies, web application firewalls, WordPress malware removal, malicious redirect cleanup, SEO spam remediation, WP-CLI workflows, vulnerability response, and website hardening. His research informs Malcure’s malware detection, cleanup, and hardening methodology.

Need help with restoring your hacked site?

Our dedicated incident response team, state-of-the-art technology, and excellent customer service makes sure that your website is 100% clean and secure.

Request Malware Cleanup →