Use $wpdb or other PHP script method to find/replace in WP database

Question

I'm hoping to push a bug fix for a custom WordPress plugin my sites use.

The plugin registers a shortcode with various attributes.

However, there's a bug where one of the attributes is incorrectly called, so it is never being rendered in the HTML.

Since I have a lot of users that use this plugin, it's possible that they uknowingly have this attribute set in their shortcode on production pages—meaning pushing a fix could cause these pages to then render incorrectly.

I essentially want to run a script that "resets" all instances of the shortcode attribute to the default value ('', in this case).

I want to regex find all instances of:

\[shortcode_name(.*?)(attribute_name=['"]\S*['"])

and replace it with:

[shortcode_name $1 attribute_name=''

in wp_posts.post_content

I know older versions of MySQL (< 8.x) don't have a native REGEXP_REPLACE() function, so I want something that is compatible between the myriad widely used versions of MySQL and MariaDB that are out there. I'm not really sure how to approach this with PHP and/or the $wpdb class (or if this is even a good idea, at all).

According to PHPCS, "PHP system calls are often disabled by server admins", so I don't want to use shell_exec() to call a WP-CLI script.

Answer 1

wpdb doesn't have a built in regex replace function. The easiest solution would be to loop through all of their posts, check the contents for the incorrectly formatted shortcode, and perform the regex replace on the post contents. It might not be as fast as a pure-SQL solution, but it would be less prone to error. You could make it run once when the updated version of the plugin is activated.

You can use WP_Query to iterate through all posts and check post contents, then wp_update_post to make changes to post contents.

Alternatively, if you wanted to ensure that users are aware of the changes being made, you could check all of their posts for instances of incorrectly formatted shortcodes, and if any are found, you could add an admin notice letting them know that a database update needs to be done. Then you can add a recommendation to take a backup first, and a button to make the post changes to fix the shortcodes automatically.