Listing ip addresses of WordPress comment spammers easier and faster

Last updated on November 28, 2011. Tags: blog comment spam, MySQL database, phpMyAdmin

These past few weeks, I noticed a surge in the number of visitors of Codegrad (I will not give exact numbers but it seems that the number of visitors has doubled). However, I also noticed that the number of spammers had increased and the bounce rate in my Google analytics is very high. I was thinking of IP banning all spammers to test if they are indeed the cause of high bounce rate.

Now you are probably wondering why I don't just ban the spammers' IP addresses outright. Well, I use to IP ban them until I figured out how Akismet work and it is very efficient in sending all spam comments to the spam bin (where I can delete them all in one click). Moreover, IP banning could also prevent legitimate visits from those who happen to share IP address with spammers or those infected with a malware being used to send spams from other computers.

Note: This method will not work if your Akisment is currently active. It is necessary to tag certain comments as spam.

Why would I need this method? Can't I just copy and paste IP addresses from the comment list?

With this is in mind, I decided to temporarily implement IP banning once again. This may or may not be permanent depending on what I will observe from metrics such as the rate the comment spams arrive and changes in visitor statistics as seen through Google Analytics. There is one problem though, with the increase in the number of visitors, the number of spam comments also increased drastically.

wp dashboard spams

The screenshot above shows how the list of comment spams appears in your WordPress dashboard (this particular example is version 2.8.5). The information are arranged in columns. With this structure, you cannot copy the IP addresses in just one swoop. If you attempt to highlight two consecutive IP address, you will still highlight parts of the comment and other information you don't need in IP banning. In the dashboards, the only way to obtain a list of IP addresses for IP banning is to copy them one by one.

Copying them one by one is fine if you have only 20 or less spam comments; perhaps this method will work with more than 20 spam comments depending on your perseverance and luxury of time, but if you have 200 or more comment spams, you will definitely look for a more efficient method. This blog, for example, receives more than 600 spam comments in a week.

One efficient method I came up is by acquiring the list of spamming IP addresses from the MySQL database.

Accessing the database

This method will work if you are using CPanel to manage your site and within it, you can open your database using phpMyAdmin. The icon that you must click will look like the one encircled in red below:

If you are not using CPanel, but you know for the fact that your WordPress-powered website is using MySQL, the phpMyAdmin might be accessible in a different manner but the way of opening your database will be the same (or almost the same) as what I will discuss in the following paragraphs.

Once you logged-in to phpMyAdmin, you will see a list of the databases that you use throughout your site on the right side. Select the one that you use for the WordPress-powered website.

phpmyadmin-database-list

If you do not know the database that contains the data for your WordPress website, I suppose you know how to find it. If you don't know how, it means your expertise is not sufficient for this kind of task and you should probably not access the database. However, if you insist on accessing the database: look for the line define('DB_NAME', 'database_name'); in the wp-config.php file found in your root directory or in whatever folder you installed WordPress. The part database_name is simply replaced with the actual name of the database in your wp-config.php.

After selecting the database, you will see a list of all the tables under it. Click on wp_comments to open it. On the upper part of the interface, you will see tabs labeled as Browse, Structure, SQL, Search and so on. Click on Structure and you should arrive to something like this:

WordPress comment spam database

Check the tables comment_author_IP and comment_approved. Then, click the Browse icon located at the bottom of the tables as shown below (encircled in red):

You should arrive in a table similar to what is shown below:

WordPress comment author IP MySQL

If your WordPress has hundreds of spam comments, which is the reason why you need to do this instead of just copying the IP addresses from the Dashboard, what you see is just few of the comments. The values that you might see in the comment_approved column are 0 for unapproved, 1 for approved and spam for spam. Click on the comment_approved header to sort the rows based on these values (this step - sorting - is very important).

Scroll down at the bottom of this table and you will see a header Query results operations and the options under it are Print view, Print view (with full texts), Export and CREATE VIEW. Click on Export and you will be forwarded to the Export Options interface.

Exporting the list of spammers' IP addresses

I'm not really familiar with the checkboxes here so I just retained the default setting. To export the IP addresses, scroll down to the bottom, Uncheck the Save as File, and click Go. You will be forward to another page containing a large text area contaning the following on top:

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 17, 2010 at 05:03 AM
-- Server version: 5.0.91
-- PHP Version: 5.2.9

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

Scroll the text area down and you will eventually arrive to a list containing the IP addresses together with indicators 0, 1 and spam similar to what is shown below.

('91.214.45.184', 'spam'),
('94.142.134.36', 'spam'),
('62.122.211.4', 'spam'),
('212.95.58.209', '0'),
('212.95.56.209', '0'),
('203.87.195.234', '1'),
('203.84.195.234', '1'),
('115.241.174.96', '1'),

This list could be very long, which is the whole point of why we have to do this instead of just copying the IP addresses from the Dashboard. However, if you sorted them in the PhpMyAdmin earlier by clicking on the comment_approved header, you will see that the spams, 0's and 1's are grouped together.

Highlight all rows starting from the first spam up to the last spam. Copy them and paste them on notepad, any HTML editor or any word processor.

In the notepad or the editor where you pasted the list, do a search and replace (CTRL + H in Windows). Copy the part ', 'spam'), that is on the right side of every row and paste it in the Find what field. Leave the Replace with field blank. Click on the Replace All button. This will cause everything at the right of each IP address to be deleted.

Then, repeat the same process on the left side of the IP addresses, but this time placing (' in the Find what field. If you need a pure list of spamming IP addresses. Leave the Replace with field blank.The final list should look like this.

91.214.45.184
94.142.134.36
62.122.211.4

However, if you intend to use it to IP ban the spammers, put 'deny from ' in the Replace with field(without the single quote and yes, with one character space after 'from').Your list will look like this:

deny from 91.214.45.184
deny from 94.142.134.36
deny from 62.122.211.4

Then, proceed as how to ban spammers using their IP addresses.

Posted by Greten on July 16, 2010 under Server Configuration, WordPress

Share and Enjoy:

Post Comments

Comment Rules and Reminders

The links to the commentator's e-mail do not have nofollow tag. However, I will be very strict in approving comments.
When you comment, please say something that indicates that you indeed read my post. If your comment is a general statement that can fit to any blog post about any topic, it will be regarded as spam.
What you write in the name field may include keywords to your website provided that (1) it's only up to four words long and (2) at least one of these four words is your first name or nickname. I rather reply to Bob or to Joe Smith than to Online Marketing Tips.
Please double check your comment before clicking the "Post" button. Once you clicked it, there will be no way for you to edit your comment.
Fields marked with asterisks (*) are required. Your email will never be displayed in public.