WordPress comments got out of hand?

December 15, 2013
JNH Solutions

comment-spamI recently had to fix a client’s WordPress site that had completely got out of hand. The problem started when they set comments to auto-approve and never bothered to setup Askimet.

The result: 90,000+ comments which led to every comment spammer in the world lining up to add a comment! This almost brought the web server to a complete halt as the amount of connections to the database and the bandwidth that had been used. The below picture will show you just how much of a bandwidth hog this site became from leaving comments open…

comments-bandwidth

For well over a fortnight this website was using around 4GB+ of bandwidth a day! Look at the difference after the comments were disabled and removed. Back to about 30MB a day of bandwidth after discovering the issue on the 30th of November.

Anyone who has ever gone through this will tell you once the damage has been done, it’s too late to run over to Askimet to save you.

So how do you get rid of bulk comments? Answer: PHPMyAdmin.

Now most of your spam comments will have links in the comment, so once you log into PHPMyAdmin you can navigate to the offending database and run this SQL query:

delete from wp_comments where comment_content like ‘%http://%’;

removing-spam-comments-wordpressThat should remove most of the spam. You may also need to run one for https:// links. You could also use this query to remove comments with Russian or Chinese symbols etc.

I also would run another query for the people commenting and adding their website URL. Warning this could delete non-spam comments but you have to weigh up what is more important, the time it would manually take to remove them vs the non-spam comments lost.

delete from wp_comments where comment_author_url like ‘%http://%’;

And if you are finding that the bulk of the spam left over is from hotmail email accounts you could run:

delete from wp_comments where comment_author_email like ‘%hotmail%’;

It can be a bit of trial and error to whittle away the spam comments but it will restore your blog from being targeted by spammers.

Hope this helps someone who gets themselves in this situation 😉

Facebook Twitter Linkedin Plusone Pinterest Delicious Reddit Stumbleupon Tumblr Email

Related Posts

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *

three × 5 =

Help-Desk