Create Digital Media

phpBB to bbPress Migration Process - Step 1, Remove Spam Users

By jaymis

We’ve been planning to move to bbPress for quite a while. phpBB is plagued with spam, both through spam user signups and to a lesser extent spam postings. Last time I checked there was no serious anti-spam plugins or modifications available, and the couple of hacky attempts to fix the problem were painful to implement and not particularly effecive.

So we’re now seriously on the path to a bbPress migration, there are some scripts around which will convert a phpBB database to bbPress, but our phpBB currently has 7486 registered users, and I’d say about 300 of those are real people. So before the migration, it’s time to remove the spam profiles.

Here’s a great start, it deletes all “members” who signed up over 48 hours ago and are inactive (haven’t clicked the confirmation link in their signup email).

The SQL looks like this:

[code lang="sql"]DELETE FROM phpbb_users WHERE user_active=0 AND user_id>0 AND FROM_UNIXTIME(user_regdate)… and running it on our database removed 3299 rows, which I would equate to 3299 spam users. That’s a great start, but I’m sure there are plenty more who have actually clicked that confirmation link, so my next move is to remove any users who have 0 posts. This could remove some lurkers who are actually interested in the site. Apologies to those people, but this is a war, and I’m a bastard.
I’m confident enough in my SQL that I don’t need to search around for something to do this, just modify that last SQL command. “user_posts” is where the post count is contained, so here’s what should remove users which are activated but haven’t made any posts: [code lang="sql"]DELETE FROM phpbb_users WHERE user_posts=0 AND user_id>0 AND FROM_UNIXTIME(user_regdate)And lo, it worked. 3316 rows removed. That’s 6615 spam users removed, and our phpBB installation is now showing 828 registered users, which feels about right. I’m sure there’s still some cruft in there, but this is definitely a manageable level, and some manual pruning of 820 users could be feasible, whereas manually going through 7000+ “users” would be utterly painful.Next step: Try the migration from phpBB to bbPress, and research whether we can redirect old orphaned phpBB URLs to the new bbPress hotness.

An aside: The lovely code highlighting in this post was provided with the Code Snippet Wordpress plugin, which I installed because WP was misinterpreting my html <code> blocks.

Related posts

• Testing Asides (2)
• bbPress Installation Step 2: Plugin Browser (2)
• Marketing Gone Mad: Pandora and Energizer Spam our Forum (0)
• Hello internets. (0)
• For the Masses: WYSIWYG Comments for Wordpress (4)