Drupal Tip

The Druplicon, Drupal's mascot

Deleting masses of unapproved spam comments in Drupal

I originally set this site up with anonymous comments enabled and a CAPTCHA-type module to prevent spam. Well, it failed. It seems that spambots have crossed some lesser Turing threshold, or at least been hard-coded to answer simple mathematical questions. That left me with over 10,000 unapproved comments in the queue, and the Drupal 7 interface lets you delete them 50 at a time, with a few clicks. You can do the math...

Some others have suggested installing the VBO module, or modifying the database directly. I thought that left me with two less-than-simple options, with my hosting provider, but it turns out that phpmyadmin, a common webhost administration tool, lets you browse and run SQL commands directly on your site databases.

All i had to do was browse into the database for this site and execute the SQL command:

DELETE FROM `comment` WHERE `status` = 0

Et voilĂ , gone!

PS. I have since switched the site to using mollom for comment screening, and the volume of spam has been reduced by several orders of magnitude.