Script to mass delete spammers on fluxBB

In the first part, we saw how to remove spammers who register on fluxBB forums (version 1.4).

This second part introduces a PHP script to be placed on the site that does this 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!

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 = mysql_query($query, $db_handler);
if(mysql_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 = mysql_query("SELECT username FROM users", $db_handler);
while($userlist = mysql_fetch_assoc($hnd))
{
  $user = $userlist['username'];
  $hndposts = mysql_query("SELECT * FROM $posts WHERE poster='$user'", $db_handler);
  if(mysql_num_rows($hndposts) == 0)
  {
       mysql_query("UPDATE users SET num_posts='0' WHERE username='$user'", $db_handler);
  }
}
mysql_query("DELETE FROM bans WHERE username IN (SELECT username FROM users WHERE num_posts='0')",  $db_handler);
mysql_query("DELETE FROM reports WHERE reported_by IN (SELECT id FROM users WHERE num_posts='0')",  $db_handler);
mysql_query("DELETE FROM subscriptions WHERE user_id IN (SELECT id FROM users WHERE num_posts='0')",  $db_handler);
mysql_query("DELETE FROM users WHERE num_posts='0' AND id != '1'",  $db_handler);

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.