As a database guy, I never like to delete anything.
Unless, of course, I want to delete it.
But even then, I don’t really delete it.
A while back I started writing some code that would allow me to clean up my user table. Sites with phpBB boards have become quite the target for spammers, and the most typical scenarios that I have seen are:
- Register but never activate
- Activate but never post
And then there are, of course, those that register, activate, and post some spam. But those we already have some tools to deal with as you can ban their email address, or even ban their IP address if you are willing to take the risk that you also ban some innocent users at the same time.
But those that register and never post (active or not) are tougher to deal with. They show up on your memberlist, and for a while they may show up on the “newest registered user” link on the index. But that’s about it.
A while back my biggest board got hit by a bot of some kind that registered over 200 users within a span of minutes. Removing them was a real chore. Fortunately I understand the database side of things (it’s my job, afterall) so I did it by editing the database directly. But that’s not for the faint of heart, and most phpBB board owners would probably mess things up worse if they tried that way. Nothing personal to anyone reading this, to be sure, as I don’t mean to be insulting. It’s just the way I see it.
So anyway, back to the point. I started writing a MOD that would allow me (or anyone) to quickly and easily remove users. There are a number of them out there already, and some of them probably work, and a smaller percentage of them probably work correctly. What do I mean by this?
The way phpBB deletes a user leaves their posts and topics behind. So you haven’t really cleaned everything up. But deleting posts runs the risk of having topics that are messed up, where a following post quotes something that no longer exists. Since there is no “threadiness” to phpBB, there’s no way to tell if that has happened. Or worse, deleting a spammer post if it’s the first post in a topic could really mess things up.
And what happens when a legitimate user comes back to your board and can’t log in? And in doing some research you discover that they don’t exist? This is where some sort of logging process becomes more important.
All of these things went through my mind as I was designing my Prune Users MOD some months ago. Then, as is often the case, I got a bunch of code written and reached a stopping point that, well, just stopped. This is one of the reasons why I prefer to release only simple MODs at phpbb.com as I am fairly confident that they’ll get finished and ultimately released.
But I have recently revived this development effort, as I find that I really need it myself. There are only so many steps that I am willing to take to keep people out of my board, as I want folks to register. My EZ Registration MOD makes it take longer for a spammer to register, and the Memberlist Controls makes it easier for me to protect my board members from “Unsavory” links. But I still want to be able to clean up the user table on a regular basis. Thus, Pruning Users is back under development.
Here are the features in a nutshell:
- Write custom queries on a variety of user fields to identify potential users to prune
- Review the results as a list of potential users to remove
- The ability to mark users to “never prune” for individual users
- Options to remove users and leave posts, remove posts except for topic starters, or remove all posts by the user
- The ability to ban the user by email address while removing them
- A log of all users deleted by the process for later review
That last step is really what this blog post is about, and – I think – one of the important attributes that sets this MOD apart from others of the same kind. By keeping a log of user accounts that have been removed (and why) when someone legitimate comes back and asks where their user account went I actually have a record of it.
If the user was simply deleted, then there’s no audit trail, no way to track back and figure out why they were removed. I feel like this is an important difference in how I’m approaching this MOD, as compared to some of the others that I have seen.
I hope to have a beta release out shortly.