Curse You, Spammers!

Posted by Passive Income Online on 1 April 2011 filed in Blogging



Wow, it's been a while since I posted - or at least feels like it!

When I logged into my WordPress Dashboard (start your own WordPress blog) I was pleasantly greeted by 10,265 comments that are being held for moderation:

So, I have Captcha From ProtectWebForm plugin installed and I haven't figured out if it's broken, or the spammers got an automated software that solves captchas.

What I can gather is that there will be a burst of comments coming in, one comment for each of my blog post.  Although each comment has a different IP, it appears to be automated based on the timestamp (or the marketer hired people in India to do this manually, no stereotyping intended).

In any case, I want a quick way to moderate the 10,000+ comments.  I want a quick way to differentiate between legitimate comments and spam comments.

If you've had a blog for a while, you will quickly see that spammers will usually leave a website URL when they submit comments.  That's the first sign that a comment could be spam.

More sophisticated spam comments will insert the website URL in the comment body itself, making it less obvious.

OK, the discussion below is somewhat technical, but you can follow along if you've got a mess at your hand like I do (did).

For my webhost I have cPanel, and the database admin tool is phpMyAdmin.

Pop open phpMyAdmin and open the wordpress database.  In there you will find a table called wp_comments.

Inside that table is a field called comment_author_url.

comment_author_url will be anything but "http://" if a website URL is filled in during a comment submission.

So, here is the MySQL query that you can use that will return all comments that have a website URL filled out, AND that's not already approved:

SELECT * FROM `wp_comments` WHERE `comment_author_url`!='http://' AND `comment_approved` != '1';

Note the use of comment_approved field.  It is set to '1' if it's an approved comment.
From here you can then either just delete these comments, or set the comment_approved to 'spam'.
That's it!  Next step for me is to figure out how to fight off these automated comment submissions.  Let me know if you know of any good ways or another plugin that works better.

What's on your mind?

 

||