My technique for coping with spam comments on WordPress.com

Monty Python – Spam

‘Wibble’ began in 2007; a lifetime ago in Internet terms (and of course also for those now aged 15 and under, duh). In all those years, I only rarely had any spam comments to deal with here on Wibble, and I handled them in the way that defenders of UCE often advocated in the early days, which is to say: I ‘just hit delete’.

Of course, the flaw with ‘just hit delete’ is that it becomes a full-time job when a trickle turns into a flood. And that’s just what I’ve been seeing in recent months; I’m getting swamped with the blessed things. Maybe that’s due to the Covid pandemic, or perhaps <spit> Brexit. Or it could even be because Wibble is attracting more attention than it used to (chance would be a fine thing). Whatever the reason for this upsurge, it soon became clear that I needed a better solution than ‘just hit delete’.

This is what I came up with, and I share it in the hope that you may find it of use.

Two strikes and yer out!

I’m not exactly fond of maintenance. Washing dishes, dusting, vacuuming; that sort of thing. I’m not much of a gardener, either. But I do recognise that a small regular time investment helps prevent molehills becoming mountains.

I like to consider myself a fair-minded person. And I think it’s always a good idea to guard against the possibility of making mistakes. So, I figure that if someone leaves a comment that looks like spam just once, well, I can shrug that off (and maybe they’ll give up after the first attempt anyway). But if they do it a second time, I reckon it’s worth putting in that little extra effort to bolt the door, as it’ll save me time in the future.

1. I regularly check the ‘Spam’ sections in both the ‘Comments’ and ‘Feedback > Form Responses’ areas in wp-admin.
2. If a message is a genuine one, I set it as ‘Not Spam’.
3. If it’s clearly spam, I copy-paste the commenter’s IP address into my ‘Wibble spammers’ spreadsheet (see below).
4. If that IP address is already listed in my spreadsheet, that means I’ve had spam from that address before, so I add it to the ‘Disallowed Comment Keys’ section of the Discussion Settings (not forgetting to hit ‘Save Changes’). This will automatically route further messages from that address to ‘Trash’.
5. Once done, I hit the ‘Empty Spam’ button.
6. Finally, I check the ‘Trash’ section. A cursory glance here is all that’s needed to verify that there’s nothing there that shouldn’t be there.
7. Then I hit the ‘Empty Trash’ button to clear the lot in one swell foop. Job done.

#2: mystery links (they only show on mouseover)

#3: location of the IP address

About the ‘Wibble spammers’ spreadsheet

I use the free open source LibreOffice (but I’m sure MS-Excel would work just as well).

This spreadsheet is really simple. It’s just two columns:

• Column A: IP addresses, copy-pasted from the ‘Spam’ section (#3 above).
• Column B: cell B2 contains a formula:

=IF(COUNTIF(A:A, A2)>1,"Spammer","")

That formula is copied down so there’s an entry in each ‘B’ cell adjacent to every non-blank ‘A’ cell. It compares the value in the adjacent cell in column A with all the other column A entries, and if it finds an identical value elsewhere it puts ‘Spammer’ in column B against the matching entries (otherwise, it leaves it blank).

The ‘Wibble spammers’ spreadsheet

Once a ‘Spammer’ is identified, the IP address can be added to the ‘Disallowed Comment Keys’ section of ‘Discussion Settings’ (I enter them in numerical order, but that’s because I have CDO‡; you don’t have to do that), and then simply deleted from the spreadsheet.

A couple of extra wrinkles makes life a little easier:

• Setting an AutoFilter on column A allows the values to be sorted, which brings ‘Spammer’ values together.
• Adding highlight to the cells in column B that contain the formula makes it easy to see them… so when (as inevitably happens) the list in column A outruns that, it’s a simple matter to grab that little square widget at the lower right corner of any one of those cells (you should be able to see that on cell B2 in the above image) and drag it down to copy the formula down as far as needed (the highlight is automagically copied too).

Benefits of this technique

• It promotes early identification of genuine comments that have been incorrectly categorised as ‘Spam’. Hmm… that’s actually an important point. Maybe I should make that bold – there we go.
• It doesn’t clog the ‘Disallowed Comment Keys’ list with:
  • duplicate entries (which is what would happen if I were to simply copy-paste IP addresses from the ‘Spam’ section, as there are often several spam comments from the same IP address in there)
  • unnecessary entries – there’s no point blocking ‘lightwieght’ spammers who only ever send me a single spam comment (there are currently 195 entries in my ‘Wibble spammers’ spreadsheet)

Well, there you have it; I hope it’s clear – if not, please do let me know, so that I can fix it. And I’d love to hear from you whether you think it’s useful.

Monty Python – Spam Song (Official Lyric Video)

‡ I have CDO†, which is like OCD but the letters are in alphabetical order, as they should be.
† Although looking around now at the untidiness of my room… perhaps I don’t.