Remove Spammers from WordPress Database

Oct 212014

Posted by Ciprian on 21/10/2014 How-To's Add comments

Few weeks ago I was called to clean a server for my former employer. For that company I made almost 50 WordPress sites based on various themes. Some of them with open registration, open comments or multisites which permit registration of users or sub-sites. All these sites was hosted on a VPS with a generous configuration (100 GB disk space, 2 GB Real Memory, 4 GB Virtual Memory), self-managed with the help of Virtualmin+Webmin.

After a year when nobody watch these sites, the server was flooded by spammers, thousands of users was registered and published sub-sites, comments or spammy posts. The space on disk was filled, databases becomes huge and the server was down for weeks. The hosting company has disabled even the scheduled back-ups because the backups don’t had space to run.

In short I was called to clean the server, to remove the spammers and their posts. After restoring the basic functionality of the server by deleting some old unfinished backups I started to remove users from WordPress Dashboard. What a nightmare!!! For about 33000 spammers probably it take a life to delete them from Users menu… It take about 30 minutes to remove 20 users and I saw WSOD at each click…

So, I start thinking to remove the users directly from database. But how to remove users and their associated posts? Searching the internet I found this solution from Tripwire Magazine which seems to help in my case. But I don’t need to remove posts from specific categories, but for specific users. Which users? Looking in the users list I saw that spammers used specific emails – like freemail.hu, mail.ru, mail.bg etc. So, if I can select users with “spamming” emails and remove them and their posts I solve the problem.

Using WordPress Database ER Diagram I found the relations between users and posts and I found the answer for my issue. Here are the steps:

make a backup of your database – if something goes wrong you can restore the database and start over.
log into PhpMyAdmin and select desired database from the left panel
select SQL tab and paste the next script in the textarea. Run the query by pressing “Go” button. Re-run the query multiple times replacing @freemail.hu with each offending email address. Don’t remove the wildcard “%” which stay for “any string”.

DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id ) 
LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id ) 
LEFT JOIN wp_users d ON ( d.ID = a.post_author ) 
WHERE (
d.user_email LIKE '%@freemail.hu'
);
DELETE a
FROM wp_usermeta a
LEFT JOIN wp_users d ON ( a.user_id = d.ID ) 
WHERE (
d.user_email LIKE '%@freemail.hu'
);
DELETE d
FROM wp_users d
WHERE (
d.user_email LIKE '%@freemail.hu'
);

DELETE a,b,c

FROM wp_posts a

LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id )

LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )

LEFT JOIN wp_users d ON ( d.ID = a.post_author )

WHERE (

d.user_email LIKE '%@freemail.hu'

);

DELETE a

FROM wp_usermeta a

LEFT JOIN wp_users d ON ( a.user_id = d.ID )

WHERE (

d.user_email LIKE '%@freemail.hu'

);

DELETE d

FROM wp_users d

WHERE (

d.user_email LIKE '%@freemail.hu'

);

WARNING! BACKUP YOUR DATABASE PRIOR OF USING THIS SCRIPT! USE AT YOUR OWN RISK.

After running the script the database was clean, with only few hundreds of spammers left because they used common addresses like yahoo or gmail, used also by “good” users. But now is simple to delete the spammers manually from Users panel.

Finally I removed the pictures left unattached (uploaded by spammers to their posts which not exist anymore) and I optimized the database (there are a lot of plugins for that).

I did this for all spammed sites. Now the server is up and running, with only 36 GB filled, with scheduled backups and all functionalities back

https://drafie-design.nl/remove-spammers-from-wordpress-database/trackback/Trackback URI:

Ciprian

Experienced web developer and graphic designer. Former consultant for Suffusion theme and former moderator on Suffusion Support forum. I teach others while learning myself.

Google+ Profile

Google+ Page

This site uses Akismet to reduce spam. Learn how your comment data is processed.

One Response to “Remove Spammers from WordPress Database”

Comments (1)

Wow! Thanks … saved me from a lot of work! I was looking for a solution to remove spam users for quite some time. Thanks

Paul

04/12/2017 at 13:34

Ciprian

Leave a Reply

One Response to “Remove Spammers from WordPress Database”