Going bulk on site spam

10 Jun 2013

Got spam? Sure you do. I use the Mollom module and service to control spam on Drupal sites, including the (now Drupal 7!) Drupal Ace. Mollom reports heavy action on the front lines: It's blocking over 1,000 spam attempts a day on this site, and on one day late last year, whacked over 16,000 urgent missives concerning Air Jordan-wearing Russian brides refinancing viagra at Canadian pharmacies.

Still, during a preiod of recent lax settings, I amassed a crop of about 3,500 messages requiring approval. Poking into these revealed very little chance that any were legitimate comments. Deleting them via Drupal's comments administration screens, though, at 50 messages per screen, would require crawling through some 70 screens. (That chore is made even worse by one of the many bugs I'm experiencing since upgrading to D7: my Comment admin screen shows no link to the "Approve comment" screen, and I need to get there by using the path admin/content/comment/approval.)

How to delete 3,500 spammy comments in seconds? One method involves the Views Bulk Operations module, a powerhouse solution for vetting and handling comments in bulk. Web searching will find you many more ideas, invoking other modules and/or techniques. Feeling weary of installing and configuring modules, though, I looked for something more fell-swoopy. Wouldn't some SQL command do the trick? Something that, say, let me perform mass bulk of all comments for date ranges in which only spam comments appear to exist?

I ended up using an even simpler SQL command, as seen at VICtheme (among many other sites):

DELETE FROM `comment` WHERE `status` = 0

In that command, comment (note: not "comments") is the table where Drupal 7 stores node comments. Published comments are status 1, unpublished ones status 0. So the command simply deletes all unpublished comments. Done!

Now, there's of course the risk that I deleted proper comments hidden within there, though again, inspecting a good sample of the unpublished comments suggested all or nearly-all spam. Inspecting all 3,500 just wasn't feasible; if you had left a legitimate comment that is now nuked, I do apologize.

To prevent the future need for such drastic bombings, I've set Mollom to check more strictly and to use CAPTCHA challenges more. I really dislike the imposition on well-meaning commenters, but until we can program the aerial drones to target spam mavens, every site owner has to do something to keep comments requiring moderation down to a level a human administrator can actually handle.

Is there a module that will trace spammers and dispatch tranquilizer-equipped drones their way? Something to build into Drupal 8 core, maybe?


paul's picture

I will absolutly give Mollom a try. Even if its does nothing
else but provide a CAPTCHA with a desent interface I'll be happy.

Add new comment