Spring Sale! Save 30% on all books w/ code: PLANET24
Web Dev + WordPress + Security

WordPress Tip: Remove Spam from the Comment Subscription Manager

[ Image: Jonny Quest (Inverted) ] After investigating some unusual 404 errors the other day, I found myself digging through the WordPress Admin Area trying to locate the “Subscribe to Comments” options panel. As it turns out, administrative options for the Subscribe to Comments plugin are split into two different areas. First, the S2C plugin provides configuration options under the WordPress General Settings > “Subscribe to Comments”, which enables users to tweak everything from subscription messages to custom CSS styles. New to me was the other half of the S2C administration area: the Comment Subscription Manager!

Comment Subscription Manager

Carefully hidden under the WordPress Comments menu as “Subscriptions”, the Comment Subscription Manager provides several useful ways to filter your email subscribers:

  • via “Top Subscriber List”
  • via “Top Subscribed Posts”
  • via searching entries by email address
  • via listing everyone who has subscribed

Fascinated by my discovery, I began playing around with the various filtering options and quickly noticed a rather large number of email subscriptions for “jonny123” and his many different @gmail.com accounts:

[ Screenshot: List of Jonny Subscriptions ]

As much I would like to think that these were all unique, authentic subscribers, this was obviously not the case. Sadly, the President of the Perishable Press fan club is an army of jonny clones. In fact, I quickly learned that roughly half of all comment subscriptions were for the incredibly prolific jonny family.

Apparently, in their relentless, mindless efforts to make everyone’s online lives utterly miserable, comment spammers employ automated scripts that populate comment forms by filling out or responding to every available input field1. Thus, when such spam scripts hit your comment form, many will automatically select the checkbox to “subscribe to follow-up comments via email.” In the process of doing this, they are populating your Subscribe to Comments email list with their randomly generated email information, effectively spamming your S2C Subscription Manager without even trying to do so.

Dealing with Jonny

With hundreds of these “jonny” entries to deal with, I could have really used a built-in “mass editing” feature, but alas, none is provided. In fact, to remove “jonny” spam from the comment subscription list, you have to manually select each subscriber, wait for the page to load, tick the checkbox next to any/all posts to which they have subscribed, and click “Remove Selected Subscription(s)”. Then return to the list and wash, rinse, repeat.

This method works great for removing a few subscribers every now and then, but to eliminate vast cesspools of jonny waste, we need must invoke the magical powers of SQL! Using phpMyAdmin or some other database tool, execute the following customized query:

SELECT `comment_post_ID` , `comment_ID` , `comment_author_email` , `comment_subscribe`
FROM `wp_comments` WHERE `comment_subscribe` = 'Y' 
LIMIT 0 , 100

This will return a nice table listing the comment ID, post ID, and email address for every comment subscriber. As written, the query shows 100 entries per page, but this is easily adjusted in the third line (e.g., edit 100 to desired value). This is the exact same list of subscribers as displayed in the plugin’s Comment Subscription Manager.

The benefit of using a direct database query for removing subscriber spam is the ease with which unwanted entries may be identified and removed. Simply scan the list for spam entries, tick the associated checkbox, and click the “Delete” button. And don’t forget to always make a backup before making changes to your database.

Want more awesome SQL recipes? Check out Wizard’s SQL Recipes for WordPress 🪄 Features over 300 recipes and an entire chapter on optimizing the WP database! Check out the PDF Demo and get the book »

Take Home..

My advice? If you are using the Subscribe to Comments plugin (or something similar), login and examine your subscriber list. You may be surprised to find a “jonny-clone fan-club” of your very own! ;)

Footnotes

  • 1 This fact has led to the implementation of several effective honey pot methods of fighting automated comment spam.

About the Author
Jeff Starr = Web Developer. Book Author. Secretly Important.
Blackhole Pro: Trap bad bots in a virtual black hole.

7 responses to “WordPress Tip: Remove Spam from the Comment Subscription Manager”

  1. The “Dealing with Jonny” headline is so great :’D

    It’s interesting to point out that the mailing plugin is vulnerable to spam. Isn’t it protected by the askimet (and other antispams) engine ?

    I mean, if the comment is detected as spam, does it still write the mail adress in the mailing plugin database ?

    Note: your “References” footnotes need anchors. From word to explanation, and vice versa.

  2. Perishable 2008/03/10 2:41 pm

    Thanks Louis, I really had a blast writing this article — probably more than I should have..

    You raise a good question, one to which I do not know the answer. I do know that both Akismet and Bad Behavior were active at the time of investigation (and possibly Spam Karma, but I honestly don’t remember). I suppose it would’t be too difficult to run a few tests, but I just don’t have the extra time!

    Also, thanks for the heads up on the References link.. should work fine now.

  3. I would’ve tested it if I had had comments on my blog :p

    Concerning the footnotes, your anchors are in place, but I would recommend you the Gruber’s way of doing back anchors, i.e :

    XHTML[1]

    1. The new black [↵]

    Because the [↵] sign speaks for itself.

    But I guess that’s not quite important.

  4. Perishable 2008/03/11 7:31 am

    Hmmm.. I like it! “↵” does have a certain “ring” to it!

    Thanks :)

  5. Perishable 2008/03/11 7:33 am

    Wait a minute — what is the actual code used to create the shorter version? It looks different than mine..

  6. Footnotes” at daringfireball:

    Unicode LEFTWARDS ARROW WITH HOOK, decimal-encoded HTML
    entity

  7. Perishable 2008/03/11 9:59 am

    Nice. Thanks ;)

Comments are closed for this post. Something to add? Let me know.
Welcome
Perishable Press is operated by Jeff Starr, a professional web developer and book author with two decades of experience. Here you will find posts about web development, WordPress, security, and more »
Digging Into WordPress: Take your WordPress skills to the next level.
Thoughts
I live right next door to the absolute loudest car in town. And the owner loves to drive it.
8G Firewall now out of beta testing, ready for use on production sites.
It's all about that ad revenue baby.
Note to self: encrypting 500 GB of data on my iMac takes around 8 hours.
Getting back into things after a bit of a break. Currently 7° F outside. Chillz.
2024 is going to make 2020 look like a vacation. Prepare accordingly.
First snow of the year :)
Newsletter
Get news, updates, deals & tips via email.
Email kept private. Easy unsubscribe anytime.