After examining a customer’s 150,000+ row wp_comments table at work yesterday, I realized that they’d managed to let WordPress approve a massive amount of spam.  Since there was no way I was going back thru all of that by hand, I knew that I had to come up with something clever.

Fortunately, running your already approved comments thru Akismet is pretty easy.  Well, that might be a bit disingenuous. It’s easy for the geeky types that are comfortable with the MySQL command line and raw queries.  So if you manage to fall into that category, feel free to give this a go…

1. Fire up your favorite MySQL management tool and feed the following command to your WordPress database:
   update wp_comments set comment_approved=’0′ where comment_approved=’1′;
   This tells WordPress to take any comment already flagged as “approved” and set it to “pending”.
2. Visit “Comments” in your WordPress dashboard.  You should notice that you’ve got a bunch of comments under “Pending”.
3. So long as you have Akismet installed, you should have a button marked “Check for Spam”.  Click it.
4. This step is going to require some patience.  You’ll need to wait while Akismet does its thing.  This means chilling out while watching your browser’s “loading” animation spin for a little bit.
   1. If you have a lot of comments — and we’re talking about thousands — you might run into your server’s PHP execution timeout. You’ll know this has happened when you see either a 404 or aren’t redirected back to the “Comments” page.  Don’t panic.
   2. If you run into a timeout, simply press “Back” in your browser and click “Check for Spam” again.  When the number of comments listed under “Pending” stops decreasing, you’re really close to being done!
5. Go back to the MySQL management tool you used in step one and give it one last command:
   update wp_comments set comment_approved=’1′ where comment_approved=’0′;
   This takes the “pending” comments and sets them back to “approved”.
6. Congratulations!  Your comments are now much tidier and you’ve helped stamp out the spammers who’ve gotten past your defenses.  Since your copy of Akismet has just done a bunch of heavy lifting, you might want to consider giving it a bit of a rest by implementing something like Hashcash as your first line of defense. When it comes to fighting spam, they’re a great combination.

If I can hack together a way to work around the PHP execution timeout issue, I’ll do my best to make this into a simple to use plugin.  Since I’ve got a lot on my plate right now, I’d prefer it if the lazyweb could beat me to getting that done.  Any takers?

Have you recently found yourself editing a post on your WordPress install only to find yourself facing the following prompt?

The server at Magic requires a username and password.

Well my friend, you’ve been hacked.  Apparently this has something to do with the cross-site scripting (XSS) bug addressed with the WordPress 2.8.2 and 2.8.3 updates.

I’ve uncreatively dubbed this little baddie “The Magic Hack” and there appears to be a simple way to clear it up.  As it stands, the only file that I’ve seen get affected by this is in “wp-includes/vars.php”.  So if your copy of that file looks nothing like the one available over in the WordPress subversion repository, replace yours with a fresh copy, stat.

In fact, it’d probably be a better idea to upgrade your blog to the most recent version of WordPress using the extended upgrade instructions over on the WordPress Codex.  So yeah, do that instead.

Oh, and if you’re still seeing that prompt after updating “wp-includes/vars.php”, let me know and I’ll update the post when I dig up some more info.

Update: Some people are seeing the hack showing up outside of “wp-includes/vars.php”. If you have SSH access to your server, you should be able to pick out the infected files rather quickly by doing a recursive grep from your site’s root directory:

grep -r -l gzinflate .

This will show you just the filenames where the string “gzinflate” is found. If you want to see the code that grep finds — to provide yourself with a little context — just leave the “-l” switch off of the command.

Should you not have SSH access to the server where your copy of WordPress is installed, I suggest writing your host’s support team. Any host worth their salt wouldn’t mind running the command above and giving you the results.

And if you host your WordPress sites in a Windows based server environment — which doesn’t normally allow for commands like grep — do yourself a favor and go get a real host… ;)