So before I left for China this summer, I wrote an article about how to turn your Nintendo DS into a dictionary.  Well the post got picked up on Lifehacker, a productivity blog that’s entirely more popular and prominent than my own.  Needless to say, I was very grateful for the exposure it gave me on the web.  However, an unintended consequence of this recognition was that it opened up my blog to spam attacks.  I hadn’t gotten around to dealing with all the spam until recently.  So I was a little upset that approximately 300 comments had slipped into the NDS post itself, while there was about another 300 awaiting moderation.  That meant I had a two part problem on my hands:

1. Delete all the spam from my PHP comments database.

2. Prevent this from happened in the future.

Deleting all the comment spam turned out to be pretty simple.  I went through and manually cleaned up the spam comments that had actually been posted on my site.  However, doing the same for all the comments awaiting moderation would’ve been a waste of time.  I just assumed that all the comments in that queue would be spam.  So in phpMyAdmin, I entered the following SQL query into my blog’s databate:

delete from comments WHERE comment_approved =”0″

It just found all the comments in the database whose approval field was null, and then deleted them.  So although I had to get my hands a bit dirty (there’s no way to do this through the Wordpress administration panel), it was relatively painless.

Now, I just had to make sure this didn’t happen again.  I decided to see what plugins were available to prevent comment spam, and settled on two different plugins.  The first, Akismet, actually came pre-installed on my Wordpress blog; I just wasn’t smart enough to turn it on.  It seems to do a good job of identifying any spammer who tries to post a comment.  The second plugin I’m using is called Bad Behavior.  It acts more like a gatekeeper, and analyzes all HTML headers on any comments being posted; if it seems suspicious, it won’t even let the spam bot/user make the post.  So both Akismet and Bad Behavior should work together to effectively stop all comment spam.

Now this means there’s a chance that anyone posting here might get rejected.  However, if you do, you should get an automatically generated e-mail that’ll tell you have to get approved, and stop this from happening in the future.  If you’re having particular problems posting, just let me know.