Tag: spam

Dealing with Drupal comment spam

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%"

WordPress Mu spammed, Chickenfoot to the rescue

I found out last night that I had forgotten to turn off new blog registration on my WordPress Mu instance and that over 500 spammers had created new blogs on my site.  The admin interface allows you to bulk delete blogs but requires that you check the checkbox next to each blog to select it for deletion.  This was getting pretty tedious.  Though I’m sure I could have delved into the internals of WordPress and figured out how to delete these blogs at the database level, this was a little scary and I didn’t really want to spend the time to do the research necessary to feel confident about this method.

So, I installed the Chickenfoot Firefox extension and wrote this simple script that would check every checkbox on the administration page.  This was made easy because of the fact that the only checkboxes on this particular page were ones associated with blogs that I wanted to delete.  This reduced the number of clicks to delete blogs significantly and it only took me a few minutes of manual clicking to delete the hundreds of spam blogs.

for (chk = find(new XPath("//input[@type='checkbox']")); chk.hasMatch; chk = chk.next) {
  check(chk);
}

Photo by loveï½¥janine via Flickr.