Anti-spam: Mass delete of users on fluxBB

How to remove massively spammers who register on forums just for links on their site on search engines indexes?

It is unfortunate that fluxBB does not allow to modulate access to information about users according to their seniority.

An initial response to spam is very simple: Spam is useless if the robots.txt file prohibits search engines to access to the profiles:

disallow:/forum/profile.php
disallow:/forum/userlist.php

Then you would take time to clean the database of useless entries, all spammers whose messages have been deleted or who not posted any message.

But this is not so simple: there are multiple relationships between database tables and if we remove an entry from one of them, we must also delete all references to this entry.

Bulk deletion of users in phpBB

The other forum allows to mass-delete users in two stages. From the management panel, we choose the command of deactivation or "prune". We gives the value 0 for the number of posts and we select all.

Then in the list of inactive users, we choose to display the maximum number of users per page. We can so check all with the option at the bottom of the page.
We choose the delete option and send the command.

On fluxBB, on the current version at least, we must create our own SQL query and have a knowledge of related tables.

FluxBB tables and registered members

We will see all the tables and columns that relate to users.

users

id: identifier included in other tables.
username: name listed in other tables.
num_posts: number of messages.

bans

username: the user can be banned, we must remove the entry from bans as well.

reports

reported_by: (id) for the unlikely event that a spammer would have made a report.

subscriptions

user_id: (id) for the case where the spammer would have subscribed to a thread.

Query request

From the interface of PHPMyAdmin or any other interface to SQL, we can already see how many inactive users:

SELECT username FROM users WHERE num_posts='0' AND username != 'Guest'

Replace users by xxxxusers if prefix in config.php is xxxx.
Guest is excluded as is the generic name for guests. Guest has id 1. The name could be different in your version if the CMS is translated.

It is absolutely required to keep an user with id value 1. You can create one if necessary.

See users banned without messages:

SELECT * FROM bans, users WHERE bans.username=users.username AND users.num_posts='0'

Another formulation suitable for deletion:

SELECT * FROM bans WHERE username IN (SELECT username FROM users WHERE num_posts='0')

We will replace SELECT * by DELETE, to perform a deletion.

The removal request

Users who have no message are deleted with this SQL command:

DELETE FROM $usertable WHERE num_posts='0' AND username != 'Guest'

Replace Guest by the name with id 1 in your version of the CMS.

But we must first eliminate all references:

DELETE FROM bans WHERE username IN (SELECT username FROM users WHERE num_posts='0')
DELETE FROM reports WHERE reported_by IN (SELECT id FROM users WHERE num_posts='0')
DELETE FROM subscriptions WHERE user_id IN (SELECT id FROM users WHERE num_posts='0')

It is enough to first check references and make a simple delete the if there is none.

Second part