Blocked deletion of users

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

It is a pity that flowBB does not allow modulating access to information about users in accordance with their experience .

The first response to spam is very simple: Spam already becomes useless if the robots.txt file prohibits search engines from accessing profiles:

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

Then from time to time you will need to clear the database of unnecessary registrations, all spammers whose messages were erased or did not post messages.

But this is not so simple: there are multiple relationships between the database tables, and if you delete a record in one of them, then you need to delete all references to this record.

FlowBB tables and registered participants

So, we will see all the tables and columns related to users.

users

id: id stored in other tables.
username: name in other tables.
num_posts: number of messages.

exiles

username: user can be denied, the entry in bans must also be deleted.

transferrings

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

subsignatures

user_id: (id) if the spammer would agree with this thread of discussion.

Request for Inquiry

From the PHPMyAdmin interface or any other interface in SQL, you can already see how many inactive users:

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

Replace users with xxxusers if the prefix xxxx is declared in the .php configuration.
Excluded is Guest, which is the common name for guests in the English version. This may be a different name in your version, check in the user list for id 1.

In fact, you need to save the user with ID 1.

View users without messages:

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

Another wording suitable for exclusion:

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

Delete will be used instead of SELECT *.

Delete Request

Users without messages are deleted using this SQL command:

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

Important: In the French-language version, replace the guest with the guest. He has the No 1.

But first we have to eliminate the 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')

You can just check the links first and make a simple deletion if they are not there.

Create script

The second part presents a PHP script for posting on the site and performing the operation automatically.

A spammer is defined as a user registering to add their profile, with a link to a possibly questionable site that has never added a ticket or whose tickets are spam and have been deleted.

The script is more complex than a simple SQL command, as it will test the message table to make sure that the user did not post them, and not just view the number of messages in the user table.
The latter is not really updated by the BB stream after the ticket is deleted. It's a one-way meter!

More flowBB tables

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

Message table:

	poster	poster_id
	user nickname	its identification number

You just need to check if the user has tickets.

Table by topic:

	poster
	user nickname

Usually the topic is deleted when the first post is deleted, and we will not use this table.

Inquiry

We repeat the requests given in the first part and add processing for the message table.

Check the number of messages for one user (we will actually test for all users):

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

If the number of messages is zero, you can update the number of tickets in the users table:

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

The delete requests visible in the first part, which are based on the users table and related tables, are then added to the script:

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'

We keep a record of 1 guest "Guest" in English, which should always be present.

Deletion scenario

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)
  {
       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 you need to add the database connection code, which is included in the download script...

Download and use

To use the script, place it in the flowBB root directory and immediately call the file from the browser .
Exempli gratia:

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

You can see what the script will do without changing anything by temporarily enabling the DEBUG flag in the source code:

$DEBUG = true;   

Bulk Delete flowBB Users Scenario

The file is called killbill.php. You can change that name and give it an arbitrary name .

Before you can use it for the first time, you must back up the database using the PHPMyAdmin export command or the Dashboard feature.