Code, Web

How to Remove 16,000+ Comments and Combat SPAM on your WordPress Blog

Recently one of my Friends Blog received 16000+ SPAM Comments in a span of just over a month.

Jan 1: Checkpoint
Jan 23: Spam at its Peak
Jan 24: Corrective Measure Taken
Jan 31: Checkpoint
WordPress 3.0 – No Plugins, Default Installation.

Statistics / Graphs

Lets look at the different Web Stats for the domain.
Notice the spike in bandwidth (green) and then the drop when the spam techniques were implemented.
Again, notice how much bandwidth is used before and after spam techniques were implemented.

The Damage SPAM Can Do

Clearly its a case of spam bots / links exchange program run via softwares like XRumer etc. Nearly 100% of comments were genuine-looking with a single purpose of posting their links as username. Without any plugin or rel=”nofollow” attribute, it acts as a back-link and helps boost their Google PageRank.

As you can see the Traffic (Hits) increased nearly 5 times, whereas Traffic (Bandwidth) increased nearly 50 times!

This was mainly because of spam. The wordpress installation was default with no comments plugin. Each comment was approved and loaded as part of post in HTML Format. With days, no of comments increased an so did the size of each post, accounting for huge daily data transfer.

Measures Taken

1. Old SPAM?

Well the million dollar question is – “What will you do with 16000+ approved comments (spam)?”

DO NOT directly install a plugin like Disqus. It will retrieve all the existing plugins and upload, making them almost impossible to remove. You must get rid of them before proceeding.

As of now WordPress does not allow to delete ALL Blog comments at once.

Login to your cPanel -> phpMyAdmin -> SQL . Execute the following queries there.

delete from wp_comments where comment_approved="0";
delete from wp_comments where comment_approved="spam";
delete from wp_comments where comment_approved="1";
Now, you will face one more issue. The displayed comments count on each of your post will not change. You need to reset the comment count in wordpress. Copy the following code into something like updatecount.php and run it on your blog web folder. Make sure you delete the file when done.
<?php include('wp-config.php'); // Needed for login details to WordPress database to make necessary changes

function updateCount()
        $posts = mysql_fetch_row(mysql_query("SELECT ID FROM wp_posts ORDER BY ID DESC LIMIT 1")); // Fetch row in WordPress database containing information about post data
        for ($i = 1; $i < ($posts[0] + 1); $i++)

     $comments = mysql_query("SELECT SQL_CALC_FOUND_ROWS comment_ID FROM wp_comments WHERE comment_post_ID = '$i' AND comment_approved = 1;") or die("Failed to calculate number of approved comments"); // Calculate the number of approved comments for a post and store in a variable. If unsuccessful, end program.

     mysql_query("UPDATE wp_posts SET comment_count = '".mysql_num_rows($comments)."' WHERE id = '$i';") or die("Failed to update the number of comments calculated"); // Update the comment count using the comment number fetched earlier. If unsuccessful, end program

     echo "Updated Post #$i - ".mysql_num_rows($comments)." comments <br />"; // Display message to user for each post comment count successfully updated


2. Install Plugins

Plugins are a definite way to stop blog comments spam.

  • Akismet The Best one ever! Installed by default, but you need to register before actually start using it. This plugin trackbacks all the spam on various blog and blocks it globally. These load the comments dynamically from their server, thereby reducing load on your database / hosting server.
  • Disqus, IntenseDebate, Facebook Comments are good alternate comments system.
  • Math CaptchareCaptcha are must-install in case you want to stick to WP Native Commenting system. Not all comments are from Bots, but few are actually done by Human Farms for backlinks. Nevertheless, it will reduce spam drastically.

3. Other Measures

  • Close Comments on Older Articles – This one is NOT recommended, but you know your readers better. You can choose to block comments on all posts (say older than 30 days). Go to wp-admin >> Settings >> Discussion >> Automatically close comments on articles older than (tick).
  • Ban IP Address – This should be your Last Option. You can either see the stats for Hits v/s IP log via Awstats in your cPanel or you can directly see most common IPs in your Comments Logs. You can now Block certain IPs directly from cPanel or in case you love using WordPress Plugins use WP-Ban or Perishable Press 5G Blacklist.

4. Conclusion

These did not reduce the no of hits on my blog. After installing Akismet approved comments reduced drastically but it still let through roughly 5% of the SPAMs.

After installing Math Captcha, the no of comments now reduced to roughly 10% of post akismet spam. (Intelligent bots? Or Human Farms)

No of daily hits are still the same. I guess the bots havent learnt yet! It will take time, definitely. Meanwhile it just messes up your Google Analytics.

Update: This Post has been Featured at wpLifeGuard.

Tagged , , , , , , , , , , , , , , ,