A series of queries to anonymise WordPress/WooCommerce users and their orders

anonymise-wordpress-woocommerce.sql

/**
 * Author: Dean Appleton-Claydon
 * Date: 02/05/2020
 *
 * A script to anonymise users and orders from a WordPress/WooCommerce website
 * For use on development/staging/testing website, NOT suitable for production use!
 *
 * Read all queries in full to understand how the anonymisation process works.
 * All WordPress/WooCommerce databases are different so extra care should be taken to ensure all personal data is anonymised.
 *
 * PLEASE MAKE DATABASE BACKUP BEFORE RUNNING THIS SCRIPT
 * Use at your own risk.
 *
 * Adapted from: https://bealers.com/randomise-personal-data-in-a-wordpress-database/
 */

/**
 * Update all passwords with random MD5
 *
 * Update ID to your admin user ID thus not to reset your password
 */
UPDATE wp_users SET user_pass=MD5(RAND()) WHERE ID != 2;

/**
 * Anonymise generic WordPress information
 *
 * Update ID to your admin user ID thus not to reset your details
 */
UPDATE wp_users SET
user_email = CONCAT(SUBSTRING(MD5(RAND()), -10), "@", SUBSTRING(MD5(RAND()), -10), ".com"),
user_pass = CONCAT("pass_", SUBSTRING(MD5(RAND()), -10)),
user_nicename  = CONCAT("nice_", SUBSTRING(MD5(RAND()), -10)),
user_login = CONCAT("login_", SUBSTRING(MD5(RAND()), -10)),
display_name = CONCAT("display_", SUBSTRING(MD5(RAND()), -10))
WHERE ID != 2;

/**
 * Review customer reviews and blog comments
 */
UPDATE wp_comments SET
comment_author_email = CONCAT(SUBSTRING(MD5(RAND()), -10), "@", SUBSTRING(MD5(RAND()), -10), ".com"),
comment_author = CONCAT("author_", SUBSTRING(MD5(RAND()), -10));

/**
 * Anonymise specific WooCommerce information
 *
 * Update ID to your admin user ID thus not to reset your details
 *
 * Note: other plugins may add other meta rows, please take care to check all meta
 */
UPDATE wp_usermeta SET
meta_value = SUBSTRING(MD5(RAND()), -10)
WHERE (user_id != 2 AND (
  meta_key = 'last_name'
  OR meta_key = 'first_name'
  OR meta_key = 'nickname'
  OR meta_key = 'billing_first_name'
  OR meta_key = 'billing_last_name'
  OR meta_key = 'billing_company'
  OR meta_key = 'billing_address_1'
  OR meta_key = 'billing_address_2'
  OR meta_key = 'billing_city'
  OR meta_key = 'billing_postcode'
  OR meta_key = 'billing_country'
  OR meta_key = 'billing_state'
  OR meta_key = 'billing_phone'
  OR meta_key = 'billing_email'
  OR meta_key = 'shipping_first_name'
  OR meta_key = 'shipping_last_name'
  OR meta_key = 'shipping_company'
  OR meta_key = 'shipping_address_1'
  OR meta_key = 'shipping_address_2'
  OR meta_key = 'shipping_city'
  OR meta_key = 'shipping_postcode'
  OR meta_key = 'shipping_country'
  OR meta_key = 'shipping_state'
  OR meta_key = 'googleplus'
  OR meta_key = 'twitter'
  OR meta_key = 'facebook'
  OR meta_key = 'instagram'
  OR meta_key = 'linkedin'
  OR meta_key = 'myspace'
  OR meta_key = 'pinterest'
  OR meta_key = 'soundcloud'
  OR meta_key = 'tumblr'
  OR meta_key = 'youtube'
  OR meta_key = 'wikipedia'
));

/**
 * Remove order notes that may have personal information
 */
UPDATE wp_posts SET
post_excerpt = ''
WHERE post_type = 'shop_order';

/**
 * Update all order post meta
 *
 * Note: no need to join with posts table to limit by post type since these meta keys are limited to WooCommerce
 */
UPDATE wp_postmeta SET
meta_value = SUBSTRING(MD5(RAND()), -10)
WHERE (
  meta_key = '_customer_ip_address'
  OR meta_key = '_billing_first_name'
  OR meta_key = '_billing_last_name'
  OR meta_key = '_billing_company'
  OR meta_key = '_billing_address_1'
  OR meta_key = '_billing_address_2'
  OR meta_key = '_billing_city'
  OR meta_key = '_billing_postcode'
  OR meta_key = '_billing_country'
  OR meta_key = '_billing_state'
  OR meta_key = '_billing_phone'
  OR meta_key = '_billing_email'
  OR meta_key = '_shipping_first_name'
  OR meta_key = '_shipping_last_name'
  OR meta_key = '_shipping_company'
  OR meta_key = '_shipping_address_1'
  OR meta_key = '_shipping_address_2'
  OR meta_key = '_shipping_city'
  OR meta_key = '_shipping_postcode'
  OR meta_key = '_shipping_country'
  OR meta_key = '_shipping_state'
  OR meta_key = '_billing_address_index'
  OR meta_key = '_shipping_address_index'
);

/**
 * Other tables containing customer data
 */
TRUNCATE TABLE wp_wc_customer_lookup;
TRUNCATE TABLE wp_woocommerce_payment_tokenmeta;
TRUNCATE TABLE wp_woocommerce_payment_tokens;