Anonymise personal data in a WordPress database

Are you about to get a freelancer to work on your existing WordPress site for you or one of your clients?

Here’s a simple SQL script you can run against your dev database to randomise all the user information EXCEPT a test login before you send it over to them. It will also set that test login’s password to ‘password’.

/********************************************************
MAKE A FULL BACKUP OF YOUR DATABASE BEFORE RUNNING THIS SCRIPT
*********************************************************/

/** Test login is wp_users.ID = 383 */
SET @SIFT_ID=xxx;

/** Set the password to 'password' */
UPDATE wp_users SET user_pass='$P$BdpHKeNPW6usjHE/nwSE.nG1Y5Couw0' WHERE ID=@SIFT_ID;

/** obfuscate user table data */
UPDATE wp_users SET 
/** email address format */
user_email = CONCAT(SUBSTRING(MD5(RAND()), -10), "@", SUBSTRING(MD5(RAND()), -10), ".com"),
/** random strings */
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))
/** except our test login */
WHERE ID != @SIFT_ID;

UPDATE wp_usermeta SET 
/** random strings */
meta_value = SUBSTRING(MD5(RAND()), -10)
WHERE (user_id != @SIFT_ID AND (meta_key='last_name' OR meta_key='first_name' OR meta_key='nickname'));Code language: PHP (php)

All you need to do is get a value for the test user's ID and replace xxx in @SIFT_ID=xxx with it above.

There are a couple of simple ways to figure out the ID for your test user:

SQL

For the user with username login_022b366d32

SELECT ID, user_login FROM wp_users WHERE user_login='login_022b366d32';Code language: JavaScript (javascript)

The ID field is the one you want.

URL

Go to the user’s screen in wp-admin and mouse over ‘capabilities’, you should see the user_id value in your status bar: