08 Mar 2009
How to list comment spam IP addresses

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:

  1. SELECT count(comment_author_IP) AS 'Count', comment_author_IP AS 'Address'
  2.  FROM wp_comments
  3.  WHERE comment_approved = 'spam'
  4.  GROUP BY comment_author_IP
  5.  ORDER BY comment_author_IP
  6.  ;

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

  1. $ 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):

  1. Count   Address
  2. 1       163.27.200.117
  3. 9       174.37.16.234           174.37.16.234 – Rome
  4. 7       194.8.74.220            194.8.74,75.* – British Virgin Islands
  5. 1       194.8.75.145
  6. 1       194.8.75.149
  7. 9       194.8.75.163
  8. 1       194.8.75.40
  9. 1       194.8.75.52
  10. 1       207.58.181.182
  11. 1       208.53.137.178
  12. 1       212.227.114.150
  13. 1       212.235.92.134          212.235.92.* – Israel
  14. 1       212.235.92.142
  15. 1       212.235.92.144
  16. 1       212.235.92.146
  17. 1       212.235.92.158
  18. 1       212.235.92.171
  19. 1       213.163.65.73
  20. 1       213.8.173.83
  21. 1       67.159.45.96
  22. 1       69.36.184.177
  23. 1       69.64.74.67
  24. 1       72.167.164.80
  25. 1       72.167.232.50
  26. 1       72.167.36.70
  27. 4       78.110.175.31           78.110.175.* – Moscow
  28. 1       84.60.108.90
  29. 1       89.185.88.237
  30. 1       92.241.160.25
  31. 1       94.23.121.249
  32. 1       95.24.70.126

Finally, a sample stanza from .htaccess:

  1. order allow,deny
  2. deny from 78.110.175.
  3. deny from 174.37.16.234
  4. deny from 194.8.74.
  5. deny from 194.8.75.
  6. deny from 212.235.92.
  7. 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]

Category: meta
Tags: , ,
(comments closed) | (trackbacks closed) | Permalink | Subscribe to comments |

One Trackback

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

Site last updated 2015-01-12 @ 13:31:07; This content last updated 2010-01-11 @ 16:08:09