Dealing with Drupal comment spam

December 12th, 2010  |  Published in hacks  |  1 Comment

I got word that the Pages to Prisoners website was drowning in comment spam.  I locked down permissions, installed the AntiSpam and reCAPTCHA modules and wrote up some quick docs about my approach to solve it before stumbling on this really helpful how-to post, How to: Take control of your Drupal site back from the comment spammers, which outlines most of the steps that I took.

In the final step of this how-to, the author recommends bulk deleting spam comments in SQL.  The example SQL query in the how-to is pretty intense, deleting all comments.  I found that by looking at the comments, I could identify frequently used phrases in the subject that clearly had nothing to do with content on the site.  I could then compose a SQL query that would blow away these comments in a way that felt safer than deleting everything.  This is the query that I used which took me about 15 minutes to blow away over 3000 comments:

DELETE FROM `comments` WHERE `subject` LIKE "%boot%" OR `subject` LIKE "%jewelry%" OR
`subject` LIKE "%ugg%" OR `subject` LIKE "%watches%"  OR `subject` LIKE "%handbag%" OR 
`subject` LIKE "%cartier%"  OR `subject` LIKE "%jordan%" OR `subject` LIKE "%vuitton%" OR 
`subject` LIKE "%replica%" OR `subject` LIKE "%Louboutin%" OR `subject` LIKE "%MOD%"  OR 
`subject` LIKE "%OEM%" OR `subject` LIKE "%viagra%" OR `subject` LIKE "%coach%" OR 
`subject` LIKE "%chanel%"  OR `subject` LIKE "%tiffany%"  OR `subject` LIKE "%armani%"  OR 
`subject` LIKE "%windows 7%"  OR `subject` LIKE "%bvlgari%" OR `subject` LIKE "%chanel%" OR 
`subject` LIKE  "%The whirlwinds Mens%"  OR `subject` LIKE "%footwear%"  OR 
`subject` LIKE "%gucci%" OR `subject` LIKE "%BALENCIAGA%" OR `subject` LIKE "%jersey%" OR
 `subject` LIKE "%shoe%" OR `subject` LIKE "%jacket%" OR `subject` LIKE "%vibram%" OR 
`subject` LIKE "%mobile phone%" OR `subject` LIKE "%cell phone%" OR `subject` LIKE "%burberry%" OR
`subject` LIKE "%blu ray%" OR `subject` LIKE "%blu-ray%" OR `subject` LIKE "%phone%" OR 
`subject` LIKE "%nike%" OR `subject` LIKE "%abercrombie%" OR `subject` LIKE "%dress%" OR 
`subject` LIKE "%charm%" OR `subject` LIKE "%jewellery%"  OR `subject` LIKE "%london%" OR 
`subject` LIKE "%www.%"  OR `subject` LIKE "%video game%" OR `subject` LIKE "%bag%" OR 
`subject` LIKE "%wedding%" OR `subject` LIKE "%rosetta stone%"