MySQL Query to Bulk Delete Spam Wordpress Users

Question

My membership website has been inundated with spam sign ups. One thing I noticed is a lot of the spammers emails are @hotmail.com.

What I want to do is delete all users who are subscribers and have a @hotmail email address.

The users data are in two tables wp_users and wp_usermeta and as far as I understand, I need to delete the data in both tables to effectively remove the users. I haven't managed to find a query that can delete all users data from mysql across the two tables.

I can delete the users in the wp_user table with this query

DELETE 
FROM  wp_users 
WHERE  user_email LIKE  "%@hotmail%"

But I also need to delete the data from the wp_usersmeta table and also make sure I am only deleting subscribers (meta_key = wp_capabilities and meta_value = subscriber).

Any ideas how I can do this? Are there users data in any other tables that I'm missing? Subscribers do not have any posts associated with them.

I've seen some plugins for spam sign ups but they are preventative. Right now, I need a way to get rid of these annoying spammers emails.

Answer 1

You can use a INNER JOIN when using DELETE in MySQL.

DELETE
FROM  wp_users 
INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
WHERE  wp_users.user_email LIKE  "%@hotmail%" AND [etc, etc.]

This solution gives you two (or maybe more ;-)) problems: 1) you can't reassign posts and links (if you want) and 2) you have to deal with JSON values in MySQL.

A better way is to use WordPress functions for the job. You can use get_users to search for users and wp_delete_user to delete the user.

<?php $args = array(
    'blog_id'      => $GLOBALS['blog_id'],
    'role'         => 'subscriber',
    'search'       => '*@hotmail.com'
 );

$blogusers = get_users($args);
    foreach ($blogusers as $user) {
        wp_delete_user( $user->ID, $reassign );
    }
?>

Please read the Function Reference: it will help you solve this problem and future problems.