matthewwhitworth.com

• Published:January 21st, 2008
• Comments:No Comments
• Category:Diary, Tech

Kid Amnesiac, Jessica’s blog about Simon, has always been cursed with lots of comment spam, which I had never really bothered to investigate. It was easier to simply mark the offending comments as spam every time I logged in and move on, especially since the spam comments always required moderation and never actually made it out to the viewable part of the blog.

When I had originally built Kid Amnesiac, I had required all comments to come from registered users and forced moderation on every registered users’ first posts. This seemed the most flexible balance between making it easy for users to post comments and not being overrun with spam. Since all of the comment spam was ending up in Moderation I assumed that the spam bots were actually registering as users before they posted, but a quick review of the Users page made it obvious that that was not the case. A few days ago it occurred to me that the spam must be Trackbacks, so I went into the Discussion Options configuration page and disabled them.

But the spam kept coming. In fact, in just the last three days it had dramatically increased. So this morning I decided to check how trackbackability was enabled in the database. “DESCRIBE wp_posts;” showed that there was a column named ping_status, described as “enum(‘open’,’closed’)”. Armed with that information it was easy to see what was going on.

mysql> SELECT post_title, post_date
-> FROM wp_posts
-> WHERE ping_status = 'closed'
-> ORDER BY post_date ASCENDING;
+-----------------------------------+---------------------+
| post_title                        | post_date           |
+-----------------------------------+---------------------+
| Fifteen Months and a Growth Spurt | 2008-01-18 12:19:38 |
| These Shoes Were Made for Walking | 2008-01-19 21:43:23 |
+-----------------------------------+---------------------+
2 rows in set (0.00 sec)

The configuration change I had made had only affected the posts that were published after the modification, so now it was up to me to change the ping_status for all of the previous posts in the database.

mysql> UPDATE wp_posts
-> SET ping_status = 'closed'
-> WHERE ping_status = 'open';
Query OK, 299 rows affected (0.01 sec)
Rows matched: 299  Changed: 299  Warnings: 0

mysql> SELECT post_title, post_date
-> FROM wp_posts
-> WHERE ping_status = 'open';
Empty set (0.00 sec)

Perfecto! Remember, kids, always back up your WordPress database before monkeying around in it. If you screw it up, your wife may stab you.