Reclaiming the forums from bots

It’s been a long time since the last time I had done any cleaning in the G-Loaded Forums. I use the forums for further discussion about the published content, since the comments are disabled after a period of time. During the last months the place had been left at the hands of bots. But this is no more. Below you will find information about all the actions I took in order to cleanup the user accounts created by bots and prevent further automatic user registrations.

The software I use is bbPress. In order to protect the forums from automatic user registrations, I installed the recaptcha plugin. This requires registration at and the creation of a private/puplic key pair, but the procedure is straightforward, so I won’t go into the details.

Second, but equally important, is the deletion of user accounts created automatically by bots. The characteristic of those accounts is that they have never posted any posts. All the advertising information existed in the user profile fields. After inspecting the database structure for a while, I deleted all the users with zero number of posts from the bb_users table using the following MySQL query:

DELETE FROM bb_users WHERE id IN (
    FROM bb_users AS u LEFT JOIN bb_posts AS p ON LEFT JOIN bb_usermeta AS m ON
    WHERE m.meta_value LIKE "%\"member\"%" AND p.post_time IS NULL
  ) AS bb_non_posters

Then I deleted all the user metadata for non-existent users from the bb_usermeta table using the following query:

DELETE FROM bb_usermeta WHERE umeta_id IN (
  SELECT umeta_id FROM (
    SELECT m.umeta_id
    FROM bb_usermeta AS m LEFT JOIN bb_users AS u ON
  ) AS bb_unlinked_meta

The above will eventually delete all user accounts and their meta data when no posts have been published by those users. That means that even legitimate users with no posts will be deleted, but such users are very rare. If your account has been deleted, please make a new one.

Normally, further clean up of the tags tables should be performed, but since those users hadn’t posted anything, it is very unlikely that they had created any tags, so I think the above are just enough.

Using the above queries I got rid of thousands of user accounts created by bots. Make sure you have backed up your database before attempting any manipulation of the data.

Reclaiming the forums from bots by George Notaras is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.
Copyright © 2009 - Some Rights Reserved

George Notaras avatar

About George Notaras

George Notaras is the editor of the G-Loaded Journal, a technical blog about Free and Open-Source Software. George, among other things, is an enthusiast self-taught GNU/Linux system administrator. He has created this web site to share the IT knowledge and experience he has gained over the years with other people. George primarily uses CentOS and Fedora. He has also developed some open-source software projects in his spare time.

2 responses on “Reclaiming the forums from bots

  1. Rhys Permalink →


    Thanks for the link back to recaptcha bbpress, it is most appreciated, and I’m so happy that it’s working for you!

    Mind I ask what version of bbPress are you using (if you don’t want to broadcast this in the forums, then drop me an email)? Reason I ask is that I’m collecting version numbers my software is compatible, incompatible with. I suspect, more than likely, is that my software works with certain templates, rather than certain version numbers, as I’ve managed to get it working with some versions of 1.0.2 branch of bbPress, but other people are reporting problems.

    If you could let me know this, that would be great, thanks again!

  2. George Notaras Post authorPermalink →

    Hi Rhys. Thanks for the great plugin. Those automatic user registrations were a real issue here.

    I use the latest stable version of bbPress, 1.0.2 at the time of writing, and had no problems in getting your plugin working with the registration form.

    One thing I’d like to point out is that bbPress now does not allow registrations using an email address that has already been used by another registered user. But, if such a duplicate email address is used at the registration form, bbPress displays no error message at all (!!!), so it seems as if the input in the recaptcha form was incorrect. After realizing that, I had no further problems. I can confirm that it works fine here so far.

    Thanks for stopping by.