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

Over 16,000 Spam Comments

Recently One of my WordPress blogs received 16000+ SPAM Comments in a span of just over a month.

Statistics / Graphs

Traffic Count
Notice the spike in bandwidth (green) and then the drop when the spam techniques were implemented.

Trafic Stats
Again, notice how much bandwidth is used before and after spam techniques were implemented.

The Damage Spam Can Do

Clearly it’s a case of spam bots / links exchange programs. Nearly 100% of comments were genuine-looking with a single purpose of obtaining links, even though they’re “nofollow” links.

The WordPress installation was default with no comments related WordPress plugins. Each comment was automatically approved. Within days, the number of comments increased, so did the size of each post, accounting for huge daily data transfer. As you can see the Traffic (Hits) increased nearly 5 times, whereas Traffic (Bandwidth) increased nearly 50 times! That can cause an serious increase in server load.

1. How to Remove 16,000+ Comments

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

DO NOT install a plugin like Disqus. It will retrieve all the existing comments 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 posts will not change. You need to reset the comment count in WordPress. Create a new php file and name it whatever you want, like updatecount.php. Paste the following code into this new file and place it in your WordPress’ root then navigate to the file and 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
        }
    }
 
updateCount();
?>

2. Reduce Spam with WordPress Plugins

WordPress Plugins are an excellent way to stop comment spam.

  • Akismet – The Best one ever! Installed by default, but you need to register before actually start using it. This plugin track backs all the spam on various Blogs and blocks it globally.
  • DisqusIntenseDebateFacebook Comments are good alternate comments system. These load the comment dynamically from their server, thereby reducing the load from database / your hosting server.
  • Math CaptchareCaptcha are worth considering 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 to Reduce Spam

  • Require an approved comment. While it will require more time and effort to manage your blog, requiring users to have at least one approved comment will further reduce the number of spam comments that get through.
  • Close comments on Older Articles – You can choose to block comments on all posts (say 30 days old). 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

While these techniques did not reduce the number of hits on my blog. the number of spam comments getting through reduced drastically after installing Akisment. But it still let through roughly 5% of the SPAMs. After installing Math Captcha, the number of comments reduced to roughly 10% of post-akismet-installation spam. Number 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.

This is a guest post written by Vineet Dhanawat

Twitter: @vineetdhanawat

Vineet Dhanawat's Website

Submit a Guest Post

« Back to Blog