Script: Delete all spammers at once 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 become useless for the spammer 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 have not posted any message.

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

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.

Turning it into a script

This second part introduces a PHP script to be placed on the site that does all that automatically.

We define a spammer as registering as a user to add his profile, with a link to a site that could be questionable, and who has not ever added a message or whose messages were spams and were removed.

The script is more elaborate than the simple previous SQL command, because it tests the post table to verify that the user has no message and do not just look at the number of messages in the user table.
The latter in effect is not updated by FluxBB after deleting a ticket. It is a one-way counter!

More fluxBB tables

In addition to the tables described in the first part, we will use this time the table posts.

Table posts:

  poster poster_id      
  user name identification number      

We just want to verify that a user has no post.

Table topics:

  poster      
  user name      

Usually the topic is deleted when the first ticket is deleted so we no not use it.

Query

We use the query described in the first part and add a processing for the posts table.

Checking the number of messages for a user (actually the test will be made for all users):

$user = "xxxx"; 
$query = SELECT * WHERE  poster='$user'
$hnd = mysqli_query($db_handler, $query);
if(mysqli_num_rows($hnd) == 0)
{
  echo "$user has no message.<br>";
}

If the number of messages is null, we can update the number of posts in the table users:

UPDATE users SET num_posts='0' WHERE poster='$user'

Then we add to the script queries of suppressions seen in part one, which is based on users and related tables:

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 user_id FROM users WHERE num_posts='0')
DELETE FROM users WHERE num_posts='0' AND id != '1'

An entry is always retained, which is the Guest, whose id is 1 which must always remain present.

The removal script

include("config.php");

$hnd = mysqli_query($db_handler, "SELECT username FROM users");
while($userlist = mysqli_fetch_assoc($hnd))
{
  $user = $userlist['username'];
  $hndposts = mysqli_query($db_handler, "SELECT * FROM $posts WHERE poster='$user'");
  if(mysqli_num_rows($hndposts) == 0)
  {
       mysqli_query($db_handler, "UPDATE users SET num_posts='0' WHERE username='$user'");
  }
}
mysqli_query($db_handler, "DELETE FROM bans WHERE username IN (SELECT username FROM users WHERE num_posts='0')");
mysqli_query($db_handler, "DELETE FROM reports WHERE reported_by IN (SELECT id FROM users WHERE num_posts='0')");
mysqli_query($db_handler, "DELETE FROM subscriptions WHERE user_id IN (SELECT id FROM users WHERE num_posts='0')");
mysqli_query($db_handler, "DELETE FROM users WHERE num_posts='0' AND id != '1'");

echo "Done!";

To this we must also add the code to connect to the database, which is included in the script to download ...

Download and use

To use the script, upload it in the fluxBB root directory and call it directly from a browser.
For example:

http://www.mywebsite.com/forum/killbill.php

You can see what the script will do without to change anything by activating provisionally the DEBUG flag in the source code:

$DEBUG = true;

Script to mass delete users on fluxBB

The file is called killbill.php. You can change this name and give it a custom name.

Make carefully a backup of the database with PHPMyAdmin export command or a command of your admin panel, before first use.