How to: Take control of your Drupal site back from the comment spammers

I'm always the first to admit when I make a mistake. And hoo boy, did I ever make one here. I recently created a website for a friend using Drupal. This friend didn't use the site for a few weeks, and when she went to make a blog posting, we discovered that the site had been overrun with comments advertising Viagra, Paxil, Tamiful, "h5n1" masks, and kamagra jelly gel (whatever that is). Overrun to the tune of 48,495 comments in the span of 1 month, to be exact. Yikes!

Rather than have her website become a perpetual illicit online drug store, I quickly fixed my mistakes to deter the comment spammers. Since I feel strongly that one should always learn from his/her mistakes, here's what I did to stop the flood of comment spam so you don't have to learn the hard way like I did.

Step 1: Lock Down Commenting

The biggest mistake I made with this site was to allow anonymous users the ability to post comments without moderation. The first step I took in fixing this problem was to take away that privilege. Anonymous commentators will still be able to leave comments, but you will have to approve them before they can be posted to the website (see our earlier post on how to be notified when new comments are posted).

Do this by going to Administer > User Management > Access Control. Scroll down to the Comment Module, and uncheck the box next to "post comments without approval." Scroll down to the bottom of the page, and click Save Permissions.

Step 2: Prevent Bots from Posting Comments

This is the meat-and-potatoes of this fix: you'll want to make it as hard as possible for bot or other automated scripts from posting comments. The best way I've found of doing this is requiring a CAPTCHA before visitors can submit their comment. Here's how to enable CAPTCHAs with Drupal:

1. Get the modules: Download the following modules and FTP them to your site in the /modules folder: CAPTCHA, reCAPTCHA.
2. Enable the modules: Go to Administer > Site Building > Modules, and enable the CAPTCHA and reCAPTCHA modules. Click Save Configuration to save your changes.
3. Apply for a reCAPTCHA key: Since reCAPTCHA is administered through a 3rd party service, you'll need to register with them to be able to access the CAPTCHAs. Once you have a reCAPTCHA account, go to My Account > My Sites > Add a New Site. Enter the site's address, and click Create Key. You're be presented with a public and private key. Somehow note these keys, you'll need them in the next step.
   
4. Configure the CAPTCHA module: Back on your site, go to Administer > User Management > CAPTCHA, and click on the reCAPTCHA link in your tabs. Copy the public and private keys into the appropriate text boxes, and click Save Configuration.
   
5. Specify where to put CAPTCHAs: Go back to the CAPTCHA home at Administer > User Management > CAPTCHA, and scroll down to the Challenge type per form area. On the dropdown next to comment_form, select reCAPTCHA (recaptcha). I also like to further lock down my site by adding CAPTCHAs to the contact page and password reset process. Click the Submit button at the bottom of the screen to save your changes.
   
6. Test your CAPTCHA: Log out of your site and go back to a blog posting. Scroll down to the comment form, and you should see a nice shiny CAPTCHA there. Go ahead and post a test comment (it won't show up on the site because you disabled anonymous commenting in step 1 above).
   
   Did you get a message saying your comment has been queued for moderation?Then you're good!

Step 3: Delete the Already-Posted Spam Comments

Warning: the information posted in this step has the potential to completely hose up your website. Please backup your database before trying and proceed with caution!

Now that you've prevented new comment spam from being posted, you'll want to remove the existing spam messages. Log into your website's web control panel, and get to the MySQL admin section (my host uses phpMyAdmin). Find the table named "comments", and browse it. Do you have about a bajillion spam comment records? Good, then you're in the right place.

We're going to run a SQL query to delete all those comment records from the database, so this means you will lose your non-spam comments too. I'd suggest wiping the comment table clean then manually inputting any good comments you have.

1. Delete the published comments: Click on the SQL tab. Copy and past the following code into Run SQL& queries...text box.

   delete from comments where status = '0'

   

   Click the Go button on the lower right of the screen. The page will refresh, and you'll get a message saying how many rows were deleted. All my comments were unpublished, so none were deleted.
   

2. Delete the unpublished comments: repeat the same process, only change the status to 1. Cope and paste this code into the text box, and run the query.

   delete from comments where status = '1'

   This time there should be significantly more records deleted.
   
    

3. Confirm deletion: Log back into your Drupal admin interface, and go to Admin > Comments > Approval Queue. Do you have 0 comments awaiting approval? Then you're done and you've successfully wrestled control of your site back from the evil comment spammers.