Dealing with Oodles of Comment Spam with MySQL



July 24, 2007

I keep track of a lot of sites, and at one point I needed to disable the CAPTCHA on one of them and forgot to re-enable it. Needless to say the spam bots had a field day. I don't check the site very often as I don't have much time to add new content, but when I was there last I realized that there were over 1000 comments waiting for approval. Way too many to scan through 20 at a time to see if there might be some actual people responding. I wanted to review as much as I could, so I took the following approach. I used phpMyAdmin to selectively remove comments.

A lot of spam recently just has the title 'people', so this was a sure bet:

DELETE FROM `comments` WHERE `subject` = "people";

There were a lot of comments all from the same IP address, so this was easy too:

DELETE FROM `comments` WHERE `hostname` = '';

You can search within the comments for specific words or phrases. Most of them are pretty common. I hit a couple like this one:

DELETE FROM `comments` WHERE `comment` LIKE '%master%';

Finally though there were still over 800 in the table, so I gave up and just deleted all of the unapproved ones:

DELETE FROM `comments` WHERE `status` = 1;

Then I made sure to install the ReCaptcha module to slow them down.

About The Author

Mike Gifford is the founder of OpenConcept Consulting Inc, which he started in 1999. Since then, he has been particularly active in developing and extending open source content management systems to allow people to get closer to their content. Before starting OpenConcept, Mike had worked for a number of national NGOs including Oxfam Canada and Friends of the Earth.