[Plugin: User Spam Remover] Feature request

Thanks a lot for the idea.

To be clear, the plugin deletes user accounts when there are zero comments, posts or links in the database made by that user, and the user account’s creation date is older than the age threshold.

And, to make sure I’m understanding you correctly: On your site specifically, you’re saying you require users to register to get special content, and that you’ve got a large number of these users who never post, comment or add links — just log in and out. It sounds like you’re using WordPress in a nonstandard way, but interesting.

Your idea sounds intriguing, and I’ve put it in the maybe section of my “todo” list for the plugin.

Off the bat, two things occur to me:

• WordPress doesn’t actually track users’ last login times like you suggest. So, to do that, you’re talking about adding a custom wp_usermeta field to the database and modifying the WP login code to write to the database on every login, which kind of goes against the KISS and “first, do not evil” intentions of this plugin (by adding INSERT/UPDATES many times per day, and when your site may be busy, potentially hanging the login process). It definitely can be done, it’s just a bit different in scope than reading from the stock database once a day.
• The plugin design is about the idea that users may register, but if they’re spammers or evil humans they won’t be allowed to post or add links, and their comments (if they make any) will be modded into oblivion. By failing to achieve anything positive, they essentially mark themselves for future deletion. However, your idea allows another out — spammers can just fuzz the login process.

Anyway, that’s just thinking out loud. Because of #1 (which means your feature request is nontrivial — I can’t just add 'and user_logindate > NOW()-30 days' somewhere) it isn’t just an instant win, so I’ve put it on the list and look at it again when I prioritize major features.

Keep in mind, cleaning out spam user registrations is just a housekeeping issue. In your case, because you’ve adapted the WordPress user system for another purpose, you may be better served by another, site-specific approach, like throwing a SQL query into cron that uses a regex, or using the user_status field for something on your blog.

Hi Joel,

thanks for your reply and consideration.

To elucidate:
The site in question is the web front-end for a radio station. Logging in gives access to…
1. commenting
2. direct contact with station presenters
3. competition entry
4. polls
5. Other rich content.
6. And soon, inter-user social interactivity

The station and site survive from an advertising model, and the stats this setup gives us are useful. It’s a system that works well. I don’t think WP has a standard intended usage, but then I’m one of those people who has, from the beginning, regarded WP as a CMS with a very good blog engine, not just as a blog, as some might have. But that’s by the by. Some users will comment, some won’t, so tying user activity to blog commenting doesn’t work for me for this site. As it’s a family site, all comments are moderated, and I use tight anti-bot methods with htaccess, hidden field traps etc., so have no spam worries. It’s really the housekeeping that I’m after.

What I really like about your work, is the backup log and restoration capability. That’s awesome.

There’s a plugin called Login Locker, which presents a viewable log of each subscriber’s last login time. From my very mediocre SQL (non)skillz, I see it’s creating it’s own table, and storing the last login timestamp, then processing that against the unix time stamp to get a value, which it then displays as a user list, ordered by last login. So, I can go through this, compare dates manually, write down a list of users, and manually delete (obviously, so cumbersome…)

But perhaps, if that methodology could be added to your plugin, that would present a great system for managing not just spam accounts, but inactive accounts (in every sense) too. Like a holy grail of user account monitoring, with restore. Cha-ching!

Cool, thanks for all the extra explanation. I’m a developer/consultant by day, and it always helps a lot when people explain what it is exactly they’re doing (trying to keep it too technical never works). ??

Actually, I built a site that was a web-based radio prep service (using a more general-purpose CMS but that’s beside the point), and of course everything past the homepage was login-only, even though it was a totally free service. So, I’m a little familiar with the desire for “real” metrics in the radio world. (We also had a studio and hosted some shows, good times.)

It’s not at all difficult to save the login time … if I were doing it I’d definitely use wp_usermeta for this, rather than creating a new table (this sort of thing really is user metadata after all).

Anyway … what you’re after is a way to delete users who haven’t logged in for a certain interval. But, in your case it doesn’t have much to do with spam user registrations? (Or when you say “have no spam worries” are you talking about comment spam, and you do have some user spam?)

I haven’t really looked at that space, but I’d guess there are a bunch of “enhanced user management” plugins out there … at some point I could get into that (hey, maybe I will!) but I don’t think that’s what I want this plugin to be.

I mean, I can see how just having a last login time would be a totally useful feature to add to WordPress all on its own — one of the things I hate about WordPress is that it does zero logging so (for somebody with both sysadmin and analytics backgrounds) you never have much of an idea what’s going on with your site, unless you spend hours with grep and your www access logs. It would be a really easy standalone no-config WP plugin just to add that bit by itself.

But again, I’m not a fan at all of adding database inserts/updates to this plugin (one thing I like about it is that it runs just 1 SQL transaction a day … there are so many bad WP plugins with so much code bloat and db overhead that they create way more problems than they solve).

(With this plugin, there’s also an implementation/user support problem it would raise … since User Spam Remover is adding the timestamp in the first place, initially none of the users have one, so when do they become OK to prune? and presumably you’d want a separate age threshold … anyway I’m seriously rambling now.)

Anyway, since you presumably already have this Login Locker thing installed and running, I can do a quick mod to User Spam Remover for you, if you like, that joins to LL’s db table and uses its last login timestamp the way you want. I mean, that’s easy, probably just changing a line or two.

I’ll take a look and get back to you here. In the meantime, let me know if you’d like me to hardcode a different age threshold (tell me how many days … 30? 6 months?) for last login time or just leave it the same as the regular one.

Hi, that would be awesome! That’s a really good, simple idea (just grabbing the info from the other plugin’s table).

Btw, the other plugin is Login LOGGER (not “Locker” – my bad).

“Or when you say “have no spam worries” are you talking about comment spam”

Yes.

“let me know if you’d like me to hardcode a different age threshold (tell me how many days … 30? 6 months?) for last login time or just leave it the same as the regular one. “

The same as the regular one, I’d just like to use it as an additional test (ie. have they posted, commented, or even logged in) for the selected timespan.

OK, here you go … I tested this out with the most recent version 1.2.1 of Login Logger. I ran it against some test data and checked to make sure that it leaves “active” users older than the age threshold alone, but still deletes those who are not active.

(For anybody coming across this thread, this is a patch to user-spam-remover.php that mods it so that, if you have the plugin Login Logger installed, it doesn’t delete users who have been logged in [or tried to log in] during the “age threshold” setting of User Spam Remover. Should work fine with all released versions of User Spam Remover including the current version 0.3.)

I can’t attach a file to this forum, so I’ll just paste the patch here. You can apply it by copy/pasting it into a text file (i.e. login-logger.diff) and using patch like this:

patch user-spam-remover.php < login-logger.diff

If you’re on Windows or just have no idea about patch then you can just manually edit user-spam-remover.php. It’s just adding 2 lines in different places to the SQL query (add the lines with the + next to them where it shows, but not the + itself).

=== modified file 'user-spam-remover.php'
--- user-spam-remover.php       2010-09-04 18:32:50 +0000
+++ user-spam-remover.php       2010-09-08 20:37:02 +0000
@@ -412,8 +412,10 @@
            "LEFT OUTER JOIN ${pre}comments AS c ON u.ID = c.user_id ".
            "LEFT OUTER JOIN ${pre}posts AS p ON u.ID = p.post_author ".
            "LEFT OUTER JOIN ${pre}links AS l ON u.ID = l.link_owner ".
+"LEFT OUTER JOIN ${pre}loginlog AS ll ON u.ID = ll.id ".
            "WHERE (c.comment_approved = 'spam' OR c.user_id IS NULL) ".
            "AND p.post_author IS NULL AND l.link_owner IS NULL $wlSQL ".
+"AND (ll.active IS NULL OR ll.active < DATE_ADD(NOW(), INTERVAL -$days DAY)) ".
            "AND u.user_registered < DATE_ADD(NOW(), INTERVAL -$days DAY) ".
            "GROUP BY u.ID;";
     mysql_query("BEGIN", $db);

If this works for ya, please give my plugin some stars and a compatibility vote! ??

This plugin, and mod, is exactly what I was looking for. On my site I give downloads only to registered users, and they may never post a comment, etc, but still log in to get new content. Now, will this method delete accounts if they DON’T log in but HAVE posted a comment past the time threshold?

No, if the user has posted a comment they will never be deleted (which is the normal way User Spam Remover works, and this mod doesn’t change that).

This mod only adds an additional reason for users to *not* be deleted, if they are already permanently safe from deletion (by adding a post, comment or link) then they still will never be deleted by the plugin.

For updates to this patch for User Spam Remover 0.9.1 and later, please see this thread.

Please consider this thread closed and direct any questions or comments there.

^ for some reason the link redirects to the main support page

Yeah, there was an issue with the forum. This link to the thread should work.