A client of mine had an old website that grew uncontrollably large with thousands of spam registrations, spam posts, spam submissions and other database bloat.
I took several steps to clean it up.
Everything below requires access to the database and the website’s files, in order to either run MySQL queries or add custom code to a custom plugin.
Table of Contents
- How to remove spam users based on username
- How to remove spam users based on email domain
- How to remove rogue pages
- How to delete orphaned user meta
- How to delete user meta based on meta key
- How to delete spam users based on a domain blacklist
- How to remove inactive users
- How to remove spam users based on multiple custom criteria
How to remove spam users based on username
This step is necessary when there are spam users with known usernames, such as the ones below. In my example, I am getting all users from the database that match the specific username, then I check if they have published any posts or other custom post types, and, if not, I delete them.
Note that I use cpt for my custom post type, and I echo the results, so I know who was deleted and why.
$results = $wpdb->get_results(
"SELECT * FROM `wp_users` WHERE
user_login LIKE '%3gianna%' OR
user_login LIKE '%3evan%' OR
user_login LIKE '%3gabriella%' OR
user_login LIKE '%3harper%' OR
user_login LIKE '%3jasmine%' OR
user_login LIKE '%3jordan%' OR
user_login LIKE '%3lydia%' OR
user_login LIKE '%3madison%' OR
user_login LIKE '%3mia%' OR
user_login LIKE '%3mila%' OR
user_login LIKE '%3olivia%' OR
user_login LIKE '%3sophie%' OR
user_login LIKE '%4adrian%' OR
user_login LIKE '%4alice%' OR
user_login LIKE '%4andrew%' OR
user_login LIKE '%4audrey%' OR
user_login LIKE '%4ava%' OR
user_login LIKE '%4camila%' OR
user_login LIKE '%4charlotte%' OR
user_login LIKE '%4claudia%' OR
user_login LIKE '%4emma%' OR
user_login LIKE '%acrep%' OR
user_login LIKE '%agroh%' OR
user_login LIKE '%agrip%' OR
user_login LIKE '%airlines%' OR
user_login LIKE '%airport%' OR
user_login LIKE '%4evan%'",
ARRAY_A
);
echo '<ol>';
foreach ( $results as $row ) {
$post_count = count_user_posts( $row['ID'], [ 'post' ] );
$cpt_count = count_user_posts( $row['ID'], [ 'cpt' ] );
echo '<li><code>ID: ' . $row['ID'] . '</code> <code>[' . $post_count . '] [' . $cpt_count . ']</code> <b>' . get_the_author_meta( 'user_login', $row['ID'] ) . '</b> (' . $row['user_email'] . ')</li>';
if ( (int) $post_count === 0 && (int) $cpt_count === 0 ) {
wp_delete_user( $row['ID'] );
}
}
echo '</ol>';How to remove spam users based on email domain
Similar to the code above, the code below checks for specific email domains. The list below is only a sample. Some of those email domains may be legitimate, but I am further checking for any published posts or custom post types.
$results = $wpdb->get_results(
"SELECT * FROM `wp_users` WHERE
user_email LIKE '%hideemail.net%' OR
user_email LIKE '%appliancescitywide.com%' OR
user_email LIKE '%gmailvn.net%' OR
user_email LIKE '%mailres.net%' OR
user_email LIKE '%bell.net%' OR
user_email LIKE '%orange.net.il%' OR
user_email LIKE '%icanav.net%' OR
user_email LIKE '%comcast.net%' OR
user_email LIKE '%BELLSOUTH.NET%' OR
user_email LIKE '%att.net%' OR
user_email LIKE '%talktalk.net%' OR
user_email LIKE '%earthlink.net%' OR
user_email LIKE '%in4mail.net%' OR
user_email LIKE '%cox.net%' OR
user_email LIKE '%optonline.net%' OR
user_email LIKE '%sbcglobal.net%' OR
user_email LIKE '%3sinformatique.net%' OR
user_email LIKE '%mail4edu.net%' OR
user_email LIKE '%optimum.net%' OR
user_email LIKE '%aiesec.net%' OR
user_email LIKE '%yandex.ua%' OR
user_email LIKE '%verizon.net%' OR
user_email LIKE '%solarunited.net%' OR
user_email LIKE '%onet.pl%'",
ARRAY_A
);
echo '<ol>';
foreach ( $results as $row ) {
$post_count = count_user_posts( $row['ID'], [ 'post' ] );
$cpt_count = count_user_posts( $row['ID'], [ 'cpt' ] );
echo '<li><code>ID: ' . $row['ID'] . '</code> <code>[' . $post_count . '] [' . $cpt_count . ']</code> <b>' . get_the_author_meta( 'user_login', $row['ID'] ) . '</b> (' . $row['user_email'] . ')</li>';
if ( (int) $post_count === 0 && (int) $cpt_count === 0 ) {
wp_delete_user( $row['ID'] );
}
}
echo '</ol>';How to remove rogue pages
Note that this is a specific example and, in my case, I had thousands of child pages that I did not need. This is easy, because I can use the code below to delete all child pages of a specific parent (or parents). I knew the title and the parents. You can adjust the code to only get the child pages (regardless of title).
$parent_ids = [ 1, 2, 3, 4, 5 ];
$args = [
'post_type' => 'page',
'post_title' => 'My Title',
'post_parent__in' => $parent_ids,
'posts_per_page' => -1,
];
$query = new WP_Query( $args );
$count = $query->found_posts;
echo '<p>Number of pages found: ' . $count . '</p>';
if ( $query->have_posts() ) {
while ( $query->have_posts() ) {
$query->the_post();
$page_id = get_the_ID();
wp_delete_post( $page_id, true );
}
wp_reset_postdata();
echo '<p>Success: All matching pages have been deleted.</p>';
} else {
echo '<p>Note: No pages found to delete.</p>';
}
How to delete orphaned user meta
With tens of thousands of users, the user meta table can get really bloated. For each user, there are 10 or 20, or even 100 user meta keys. After identifying the offending user meta keys, I used the code below to delete them. Here is a good plugin to identify orphaned user meta.
I have also used batches of 1000 users, so that my script would not time out.
$meta_key = 'my_orphaned_user_meta';
$batch_size = 1000;
$user_ids = get_users(
[
'fields' => 'ID',
]
);
// Split the user IDs into batches
$user_batches = array_chunk( $user_ids, $batch_size );
foreach ( $user_batches as $user_batch ) {
foreach ( $user_batch as $user_id ) {
delete_user_meta( $user_id, $meta_key );
}
}
echo '<p>User meta deletion completed!</p>';How to delete user meta based on meta key
The script below will delete all user meta keys (orphaned or not) based on a string. The example below checks for a string containing “yoast”, as after removing this plugin *, and switching to Rank Math SEO, I got lots of unused meta keys.
$user_ids = get_users(
[
'fields' => 'ID',
]
);
foreach ( $user_ids as $user_id ) {
$user_meta = get_user_meta( $user_id );
// Iterate through the user meta fields
foreach ( $user_meta as $meta_key => $meta_value ) {
// Check if the meta key contains the string "yoast"
if ( strpos( $meta_key, 'yoast' ) !== false ) {
// Delete the user meta with the matching key
echo "<p>Delete user meta for user ID $user_id: $meta_key</p>";
delete_user_meta( $user_id, $meta_key );
}
}
}* No offence to Yoast SEO.
How to delete spam users based on a domain blacklist
The functionality below is implemented in our Lighthouse plugin.
We are maintaining a blacklist file based on 400+ websites receiving daily spam registration attempts. If you have your own list, you can use the code below to remove users based on blacklisted email domains.
// Check external blacklist and get the contents of the txt file
$ch = curl_init();
curl_setopt( $ch, CURLOPT_URL, 'https://path/to/blacklist.txt' );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, true );
$blacklist_data = curl_exec( $ch );
curl_close( $ch );
// Split the contents into an array of domain names
$blacklist_domains = explode( "\n", $blacklist_data );
$blacklist_domains = array_map( 'trim', $blacklist_domains );
$blacklist_domains = array_filter( $blacklist_domains );
$args = [
'meta_query' => [
'relation' => 'OR',
[
'key' => 'user_email',
'value' => $blacklist_domains,
'compare' => 'REGEXP', // Use REGEXP for partial matching
],
],
];
$user_query = new WP_User_Query( $args );
$users = $user_query->get_results();
if ( ! empty( $users ) ) {
foreach ( $users as $user ) {
echo '<p>Found user ID: ' . $user->ID . ' with email: ' . $user->user_email . '</p>';
wp_delete_user( $user->ID );
}
} else {
echo '<p>No users found with matching email addresses.</p>';
}
How to remove inactive users
Inactivity is based on each website’s profile. In my case I had both websites with a time limit of 12 months and with a time limit of one month.
The example below gets all users that last logged in (or registered) more than 12 months ago. If yes, they are deleted.
Note that I am using a custom user meta to detect last login date. This needs to be implemented before running this script. It should be a WordPress core feature.
Make sure the last login date and the registration date exist, and they work as expected. If not, please adjust the code to use your values.
$twelve_months_ago = strtotime( '-12 months' );
$formatted_twelve_months_ago = date( 'Y-m-d H:i:s', $twelve_months_ago );
$args = [
'role__not_in' => [ 'administrator' ], // Exclude admin users
];
$inactive_users = get_users( $args );
foreach ( $inactive_users as $user ) {
$last_login = get_user_meta( $user->ID, 'last_login', true );
$registration_date = $user->user_registered;
$formatted_registration_date = date( 'Y-m-d H:i:s', strtotime( $registration_date ) );
if ( ( empty( $last_login ) || strtotime( $last_login ) <= strtotime( $formatted_twelve_months_ago ) ) && strtotime( $formatted_registration_date ) <= strtotime( $formatted_twelve_months_ago ) ) {
// User matches the criteria (last_login older than 12 months or no last_login key and registration date older than 12 months)
// Perform actions on the user
$user_roles = $user->roles; // Array of user roles
$user_role = reset( $user_roles );
echo '<div>User ID <code>' . $user->ID . '</code> - <b>' . $user->user_email . '</b> (' . $user_role . ')<br>Registration date: ' . $user->user_registered . ' | Last login date: ' . get_user_meta( $user->ID, 'wppd_last_login', true ) . '</div>';
wp_delete_user( $user->ID );
}
}How to store last login date
The code below updates a custom meta key for each user on every login.
/**
* Update last login user meta
*
* @param mixed $user_login
* @param mixed $user
* @return void
*/
function update_last_login( $user_login, $user ) {
update_user_meta( $user->ID, 'last_login', current_time( 'mysql' ) );
}
add_action( 'wp_login', 'update_last_login', 10, 2 );
How to remove spam users based on multiple custom criteria
Another heavy-lifting process to remove spam users for my specific case was to check for several criteria for each user. For example, I needed users to not be administrators, to not have any posts, pages or custom posts published, and several other details specific to my platform.
For these specific details, I had to code my own logic.
My example below implements a function with a configurable offset parameter, so that I can skip the first X users, in case the script would time out. I also had user likes and votes, so I had to count those too. You can have separate functions that return a value, and then check against that value (if it’s not 0, then delete the user).
Having all these checks made the website time out too often, so I had to change the number of users returned on each query from 200 down to 50.
Calling the function uses an offset, so I can move through all users:
delete_users( 400 );
In this case, I am starting with the 400th user, and I am requesting 200 users. That means from user 400 to 600. Next time, I’ll change the offset to 600, so I can get users from 600 to 800. And so on.
function delete_users( $offset = 0 ) {
$users = get_users(
[
'fields' => [ 'ID' ],
'orderby' => 'ID',
'order' => 'DESC',
'offset' => $offset,
'number' => 200,
'role__not_in' => [ 'administrator' ], // Exclude admin users
]
);
foreach ( $users as $user ) {
global $wpdb;
$args = [
'author' => $user->ID,
'post_type' => 'post',
'posts_per_page' => -1,
];
$current_user_posts = get_posts( $args );
$total_posts = count( $current_user_posts );
$args = [
'author' => $user->ID,
'post_type' => 'page',
'posts_per_page' => -1,
];
$current_user_posts = get_posts( $args );
$total_pages = count( $current_user_posts );
$args = [
'author' => $user->ID,
'post_type' => 'cpt',
'posts_per_page' => -1,
];
$current_user_posts = get_posts( $args );
$total_cpt = count( $current_user_posts );
// Get all user custom details
// Custom logic to get other user-specific details, such as user comments or even use the inactivity code above
if ( $total_posts === 0 && $total_pages === 0 && $total_cpt === 0 ) {
echo '<p>Delete user <code>' . $user->ID . '</code> (' . get_the_author_meta( 'user_email', $user->ID ) . ')</p>';
wp_delete_user( $user->ID );
}
}
}
delete_users( 400 );And that is all! The code above helped me clean up more than 20,000 users, along with associated spam content (avatars, comments, bios and so on).
