What to do in a WordPress spam storm

Yesterday my MacTips website suffered a spam attack. Obvious spams were arriving every couple of minutes. Luckily they were being marked as spam and not in any danger of being published, but I still had to clear them from the spam folder in Comments.

Pages and pages of spam.

I hate to just empty the Spam folder without at least glancing at it to see if a legitimate comment has been misdirected. I faced the prospect of looking through dozens of separate pages of listings.

My screenshot caught a moment where there were 164 comments in spam — that’s 9 pages worth.

Quick database access with Sequel Pro

While the WordPress web interface works nicely for many things, this was a time to call in Sequel Pro (for Macs) to talk directly to the database. If you don’t use a Mac try PHPMyAdmin or look for software similar to Sequel Pro for your Operating System.

I used Sequel Pro to open a connection to my MacTips database, and then entered this SQL query:

SELECT * FROM `wp_comments` WHERE `comment_approved` LIKE 'spam' ORDER BY `comment_date` DESC LIMIT 0,1000;

Spam comments as seen in Sequel Pro.

If you use this command for your site, first check the prefix for your tables. In my site the prefix is wp_ and so I was looking in the table called wp_comments. If your prefix is different you will need to change that portion of the query.

Note: I made the two screenshots at different times so the spam comments don’t match up.

My Sequel Pro screenshot shows only a tiny portion of what I could see. In fact it displayed all the spam comments on one screen, with columns for the author name, the full comments, dates and so on.

By clicking on a column header I could sort the column and easily see if anything was a duplicate. I was specially looking for duplicate IP addresses.

Akismet reports on the Spam storm.

Turn off comments

A quick moment of scanning all the comments made it clear they were all spam, with no genuine comments mixed in. I went back to the web interface and clicked the Empty Spam button.

Then I did something stupid: I didn’t change anything.

The spam kept coming all day and from time to time I’d delete it. Finally, at the end of the day it dawned on me that I should close off all comments until the thing was over. I expected it to last around 24 hours.

I closed comments, and added a note below each Post, using the Custom Functions feature in Thesis:

<p class="alert">Comments are temporarily closed, thanks to a spam attack on this site. I hope to re-open comments soon. </p>

This morning I’ve reopened comments and so far all seems to be well. I’m watching for signs of the same storm on my other WordPress sites.

The Daily Filth

You know, I realised yesterday that every morning as I check the spam folder on my email and the spam comments on my blogs I’m being exposed to all kinds of stuff I really don’t want to see. Even the Subject lines are often offensive, yet I need to scan them in case legitimate mail or comments have been wrongly categorised.

Blog comments are even worse, because by default we see the whole darn thing: pages and pages of filth and garbage scrolling by.

I think it would be a good change if the WordPress web interface were to show us just the first couple of lines by default and allow us to reveal the full comment with a hover. I may just send that feedback…

5 comments

Tagged as: attack, database, spam, thesis, WordPress

You may also like -

The iPod touch is hot nowMake a WordPress image GalleryMake MacTips Magic month 2012MacTips.infoFarewell 2011; thanks for all the MacTips support; welcome 2012MacTips.infoRecover lost data in LionMacTips.info