How to list comment spam IP addresses

Posted by hornlo on March 8th, 2009 filed in meta

As sort of a followup to my Comment Spam post, here’s a quick way to get a list of spammers to deny via your .htaccess file (or whatever method you chose to ban IP addresses).

First, the SQL statement to get the list; put it in a file called list-spammers.sql:

SELECT count(comment_author_IP) as 'Count', comment_author_IP as 'Address'
 FROM wp_comments
 WHERE comment_approved = 'spam'
 GROUP BY comment_author_IP
 ORDER BY comment_author_IP
 ;

You can execute it from the command line or a script like so:

$ mysql -h MYSQL.MYSITE.DOM BLOGNAME -pPASSWORD 

Which produces a list like this (the locations were added by hand just because I was curious):

Count   Address
1       163.27.200.117
9       174.37.16.234           174.37.16.234 - Rome
7       194.8.74.220            194.8.74,75.* - British Virgin Islands
1       194.8.75.145
1       194.8.75.149
9       194.8.75.163
1       194.8.75.40
1       194.8.75.52
1       207.58.181.182
1       208.53.137.178
1       212.227.114.150
1       212.235.92.134          212.235.92.* - Israel
1       212.235.92.142
1       212.235.92.144
1       212.235.92.146
1       212.235.92.158
1       212.235.92.171
1       213.163.65.73
1       213.8.173.83
1       67.159.45.96
1       69.36.184.177
1       69.64.74.67
1       72.167.164.80
1       72.167.232.50
1       72.167.36.70
4       78.110.175.31           78.110.175.* - Moscow
1       84.60.108.90
1       89.185.88.237
1       92.241.160.25
1       94.23.121.249
1       95.24.70.126

Finally, a sample stanza from .htaccess:

order allow,deny
deny from 78.110.175.
deny from 174.37.16.234
deny from 194.8.74.
deny from 194.8.75.
deny from 212.235.92.
allow from all

Note I'm taking out not just particular addresses but in some cases entire ranges. Sometimes it sucks to be you when you share a network with spammers.

[if you'd like to comment, use the contact form; this post is closed]


One Response to “How to list comment spam IP addresses”

  1. How to list comment spam IP addresses | Isotropic | dsecure.net Says:

    […] See the rest here:  How to list comment spam IP addresses | Isotropic […]

Leave a Comment

Site last updated 2019-11-06 14:26:55; This item last updated 2010-01-11 16:08:09