As with most individuals that manage a blog, I get inundated with comment spam. Based on some research I did, it looks like almost all of the blog spam occurs for older posts, especially those over 30 days old. Based on this information, I decided to disable comments for all old posts older than 30 days. It turns out that you can’t do this directly from wordpress without a plugin, so I decided to adjust the “comment_status” column in the wp_posts table directly. To remove comment spam for all posts older than 30 days, I first used the GNU date utility to find the date 30 days ago:
$ date --date="30 days ago" "+%Y-%m-%d"
2006-11-29
Once I had the date string from 30 days ago, I connected to the MySQL database that hosts my blog, and ran the following SQL query to disable posts for all entries greater than the date returned form GNU date:
$ mysql -umatty -p -h mysql.prefetch.net
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16155903 to server version:
4.0.27-standard-log
Type ‘help;’ or ‘\h’ for help. Type ' mysql> use matty Database changed
mysql> UPDATE wp_posts SET comment_status = ‘closed’ WHERE post_date < ‘2006-11-29’ AND post_status = ‘publish’
mysql> quit Bye
Now that old posts don’t have comments enabled, I am curious to see how the comment spammers react.