{"id":2217,"date":"2010-12-12T16:35:31","date_gmt":"2010-12-12T21:35:31","guid":{"rendered":"http:\/\/blogs.terrorware.com\/geoff\/?p=2217"},"modified":"2010-12-12T16:39:53","modified_gmt":"2010-12-12T21:39:53","slug":"dealing-with-drupal-comment-spam","status":"publish","type":"post","link":"https:\/\/blogs.terrorware.com\/geoff\/2010\/12\/12\/dealing-with-drupal-comment-spam\/","title":{"rendered":"Dealing with Drupal comment spam"},"content":{"rendered":"

I got word that the Pages to Prisoners website<\/a> was drowning in comment spam.\u00a0 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<\/a>, which outlines most of the steps that I took.<\/p>\n

In the final step of this how-to, the author recommends bulk deleting spam comments in SQL.\u00a0 The example SQL query in the how-to is pretty intense, deleting all comments.\u00a0 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.\u00a0 I could then compose a SQL query that would blow away these comments in a way that felt safer than deleting everything.\u00a0 This is the query that I used which took me about 15 minutes to blow away over 3000 comments:<\/p>\n

\r\nDELETE FROM `comments` WHERE `subject` LIKE "%boot%" OR `subject` LIKE "%jewelry%" OR\r\n`subject` LIKE "%ugg%" OR `subject` LIKE "%watches%"  OR `subject` LIKE "%handbag%" OR \r\n`subject` LIKE "%cartier%"  OR `subject` LIKE "%jordan%" OR `subject` LIKE "%vuitton%" OR \r\n`subject` LIKE "%replica%" OR `subject` LIKE "%Louboutin%" OR `subject` LIKE "%MOD%"  OR \r\n`subject` LIKE "%OEM%" OR `subject` LIKE "%viagra%" OR `subject` LIKE "%coach%" OR \r\n`subject` LIKE "%chanel%"  OR `subject` LIKE "%tiffany%"  OR `subject` LIKE "%armani%"  OR \r\n`subject` LIKE "%windows 7%"  OR `subject` LIKE "%bvlgari%" OR `subject` LIKE "%chanel%" OR \r\n`subject` LIKE  "%The whirlwinds Mens%"  OR `subject` LIKE "%footwear%"  OR \r\n`subject` LIKE "%gucci%" OR `subject` LIKE "%BALENCIAGA%" OR `subject` LIKE "%jersey%" OR\r\n `subject` LIKE "%shoe%" OR `subject` LIKE "%jacket%" OR `subject` LIKE "%vibram%" OR \r\n`subject` LIKE "%mobile phone%" OR `subject` LIKE "%cell phone%" OR `subject` LIKE "%burberry%" OR\r\n`subject` LIKE "%blu ray%" OR `subject` LIKE "%blu-ray%" OR `subject` LIKE "%phone%" OR \r\n`subject` LIKE "%nike%" OR `subject` LIKE "%abercrombie%" OR `subject` LIKE "%dress%" OR \r\n`subject` LIKE "%charm%" OR `subject` LIKE "%jewellery%"  OR `subject` LIKE "%london%" OR \r\n`subject` LIKE "%www.%"  OR `subject` LIKE "%video game%" OR `subject` LIKE "%bag%" OR \r\n`subject` LIKE "%wedding%" OR `subject` LIKE "%rosetta stone%" \r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
I got word that the Pages to Prisoners website was drowning in comment spam.\u00a0 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… Continue reading Dealing with Drupal comment spam<\/span><\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[27],"tags":[20597,20596,263,763,20595],"class_list":["post-2217","post","type-post","status-publish","format-standard","hentry","category-hacks","tag-administration","tag-comments","tag-drupal","tag-spam","tag-sql","entry"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p4wnIz-zL","_links":{"self":[{"href":"https:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/posts\/2217","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/comments?post=2217"}],"version-history":[{"count":4,"href":"https:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/posts\/2217\/revisions"}],"predecessor-version":[{"id":2220,"href":"https:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/posts\/2217\/revisions\/2220"}],"wp:attachment":[{"href":"https:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/media?parent=2217"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/categories?post=2217"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/tags?post=2217"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}