WordPress Spam BuddyPress Easy MySQL Spam Filter Query

WordPress spam sign ups are a real pain. If you need to do some work on the database to do a mass clean-up of spam users. I created this little MySql query which basically counts the number of full-stops or dots in an email address. Having recently done this for a client, I noticed a pattern especially in @gmail accounts where the same word is used as an email address but has full stops positioned in different places in the email address. To the human eye this sticks out like a sore thumb. So in order to check via a query I created this query.

Its looking for the first part of email addresses up to the ‘@’ symbol, with more than 3 dots in it. Now most humans would have 3 maximum thats the way we do things. But you could change to 4 to be ultra sure. I think that anything above 6 is a definite spam bot. But proceed with caution and goes without saying backup your database.


SELECT wp_users.ID,
wp_users.user_login,
wp_users.user_nicename,
wp_users.user_email,
(SELECT SUBSTRING_INDEX(wp_users.user_email,'@',1)) as 'email_part_1',
ROUND (
(
LENGTH((SELECT SUBSTRING_INDEX(wp_users.user_email,'@',1)))
- LENGTH( REPLACE ( (SELECT SUBSTRING_INDEX(wp_users.user_email,'@',1)), ".", "") )
) / LENGTH(".")
) AS 'counter',
wp_users.display_name
FROM wp_users
WHERE
ROUND (
(
LENGTH((SELECT SUBSTRING_INDEX(wp_users.user_email,'@',1)))
- LENGTH( REPLACE ( (SELECT SUBSTRING_INDEX(wp_users.user_email,'@',1)), ".", "") )
) / LENGTH(".")
) > 3
order by ROUND (
(
LENGTH((SELECT SUBSTRING_INDEX(wp_users.user_email,'@',1)))
- LENGTH( REPLACE ( (SELECT SUBSTRING_INDEX(wp_users.user_email,'@',1)), ".", "") )
) / LENGTH(".")
) DESC