WordPress Tip: Remove Spam from the Comment Subscription Manager
Published Monday, March 10, 2008 @ 11:34 am • 7 Responses
![[ Image: Jonny Quest (Inverted) ]](http://perishablepress.com/press/wp-content/images/2008/subscriptions/subscriptions-quest.gif "jonny123@gmail.com?")
After investigating some unusual 404 errors the other day, I found myself digging through the WordPress Admin 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 “Options > 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 Subscription Manager! Carefully hidden under “Manage > Subscriptions”, the 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 ]](http://perishablepress.com/press/wp-content/images/2008/subscriptions/subscriptions-jonny.gif "jonny loves me!")
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 field 1. 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 equivalent, 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 , 100This 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 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.
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! ;)
References
- 1 This fact has led to the implementation of several effective “honey pot” methods of fighting automated comment spam.
About this article
Related articles
- Backwards-Compatible Spam and Delete Buttons for WordPress
- WordPress Spam Battle: 3 Seconds that will Save You Hours of Time
- WordPress Tip: Quick Hack to Block Spam for the Wordspew Shoutbox Chat Plugin
- WordPress Tip: Update Email Address in the WordPress Database
- Contact Perishable via Comment
- Block Spam by Denying Access to No-Referrer Requests
- Provide a Link for Visitors to Verify Your Feedburner Subscriber Count
Dialogue
7 Responses Jump to comment form
March 11, 2008 at 3:35 am
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.
March 11, 2008 at 9:33 am
“Footnotes” at daringfireball:
Unicode LEFTWARDS ARROW WITH HOOK, decimal-encoded HTML
entity↩
[ Comments are closed for this post. ]
If you have additional information, contact me.
← Previous post • Next post →
« What is My WordPress Feed URL? • An Easy Introduction to Web Feeds »
1 • Louis
March 10, 2008 at 12:15 pm
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.