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 recaptcha.net 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 (
  SELECT id FROM (
    SELECT DISTINCT(u.id)
    FROM bb_users AS u LEFT JOIN bb_posts AS p ON p.poster_id=u.id LEFT JOIN bb_usermeta AS m ON m.user_id=u.id
    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 u.id=m.user_id
    WHERE u.id IS NULL
  ) 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