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 <list-spammers.sql
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 Trackback
[...] See the rest here: How to list comment spam IP addresses | Isotropic [...]