[wp-hackers] Clearing out bot users

Kevin Newman CaptainN at unFocus.com
Tue Feb 15 21:55:14 UTC 2011


I'd like to clear out all those thousands of bot users. I came up with 
this SQL statement to list all the spam users:

SELECT * FROM wp_users AS u
JOIN wp_usermeta AS mv ON mv.user_id = u.ID
WHERE mv.meta_key = 'wp_capabilities' AND mv.meta_value LIKE '%subscriber%'
AND ( SELECT count(*) FROM wp_comments AS c WHERE c.user_id = u.ID ) <= 0
AND ( SELECT count(*) FROM wp_posts AS p WHERE p.post_author = u.ID ) <= 0

This could be converted into a delete statement to nuke any user that 
has no posts or comments and is a subscriber.

Did I miss anything?


Kevin N.

More information about the wp-hackers mailing list