Blog Spammer Shootin’ Match

The Stage

For some reason….. this blog — THIS blog! …..gets a crap ton of spam. So I went looking. I figured I’d share the bulk of what I found.

mysql> update wp_comments set comment_approved='spam' where comment_approved='0' AND comment_content LIKE "%China%";
Query OK, 969 rows affected (0.46 sec)
Rows matched: 969  Changed: 969  Warnings: 0
 
mysql> update wp_comments set comment_approved='spam' where comment_approved='0' AND comment_content LIKE "%obama%";
Query OK, 119 rows affected (0.09 sec)
Rows matched: 119  Changed: 119  Warnings: 0
 
mysql> update wp_comments set comment_approved='spam' where comment_approved='0' AND comment_content LIKE "%nofollow%";
Query OK, 412 rows affected (0.09 sec)
Rows matched: 412  Changed: 412  Warnings: 0


Yep. If you’re getting crazy spam, watch out for China, obama, and nofollow. They. Will. Destroy. Your. Sanity.

The Apology

If you legitimately are trying to comment and noted something about any of the three mentioned above, I owe you an apology. I’m sorry I shot your comment in the spam box with glee and a special kind of whimsy.

Looking at the Data, Again!

I kinda enjoy using this as a psuedo “comment honeypot” because it’s just so darn entertaining. Here are some other fun ways to look at your comment spam:

By number of comments posted per IP:

mysql> select comment_author_IP, count(comment_author_IP) as count from wp_comments where comment_approved='0' group by comment_author_IP order by count desc;
+-------------------+-------+
| comment_author_IP | count |
+-------------------+-------+
| 123.45.67.89      |    15 |
| 123.45.67.89      |    14 |
| 123.45.67.89      |    10 |
| 123.45.67.89      |     3 |
| 123.45.67.89      |     3 |
| 123.45.67.89      |     2 |
| 123.45.67.89      |     2 |
| 123.45.67.89      |     2 |
| 123.45.67.89      |     1 |
| 123.45.67.89      |     1 |
+-------------------+-------+
10 rows in set (0.00 sec)

Original IP’s censored b/c I don’t wanna be a jerk to anyone but the guy at 123.45.67.89!

By my patented, foolproof, never wrong Offender Rating System®:

mysql> select comment_author_IP, (count(comment_author_IP)+SUM(LENGTH(comment_content))) as offender_rating from wp_comments where comment_approved='0' group by comment_author_IP order by offender_rating desc;
+-------------------+-----------------+
| comment_author_IP | offender_rating |
+-------------------+-----------------+
| 123.45.67.89      |           10051 |
| 123.45.67.89      |            3907 |
| 123.45.67.89      |            2367 |
| 123.45.67.89      |            1159 |
| 123.45.67.89      |             836 |
| 123.45.67.89      |             811 |
| 123.45.67.89      |             677 |
| 123.45.67.89      |             653 |
| 123.45.67.89      |             328 |
| 123.45.67.89      |             297 |
+-------------------+-----------------+
10 rows in set (0.00 sec)

Again, original IP’s censored b/c I don’t wanna be a jerk to anyone but the guy at 123.45.67.89!

…fin!

Wasn’t that fun?! =D

Don’t spam me, bro!