How to Remove and Moderate Spam Entries and Users from Flarum Database

AlfMueller

How to Remove and Moderate Spam Entries and Users from Flarum Database
Managing a Flarum forum often involves handling spam posts and users. These unwanted entries can crowd your database and potentially impact performance. This guide provides steps to remove spam entries and their corresponding users, while also setting up a manual approval process for new posts.

⚠️ Warning: Before you proceed, make sure to take a full backup of your database. The SQL commands will permanently delete data.

Required Tables:
users
posts
discussions
SQL Script for Removal:

`-- Create a temporary table to store affected User-IDs
CREATE TEMPORARY TABLE temp_spam_users (id INT(10) UNSIGNED);

-- Insert User-IDs of spam accounts into the temporary table
INSERT INTO temp_spam_users(id)
SELECT DISTINCT user_id FROM posts WHERE is_private = 1;

-- Delete discussions created by spam users
DELETE FROM discussions WHERE user_id IN (SELECT id FROM temp_spam_users);

-- Delete posts from spam users
DELETE FROM posts WHERE user_id IN (SELECT id FROM temp_spam_users);

-- Delete the spam users themselves
DELETE FROM users WHERE id IN (SELECT id FROM temp_spam_users);

-- Drop the temporary table
DROP TEMPORARY TABLE temp_spam_users;
`

Steps to Execute:

manual approval
Backup your database.
Run the SQL script for removal.

Verify if the spam entries and users have been removed and new posts require manual approval.
By following these steps, you'll not only delete existing spam but also require new posts to be manually approved, giving you more control over your forum content.

Again, ensure you backup your database before running any of these commands.

Good luck in keeping your forum spam-free and well-moderated! 🍀

luceos

AlfMueller SELECT DISTINCT user_id FROM posts WHERE is_private = 1;

This is very risky. There's no guarantee that posts set as private are all spam. As such this guide possibly deletes non spam accounts too.

clarkwinkelmann

If you use my Shadow ban extension, I believe these commands will cause all shadow banned users who posted any message to be deleted, as well as any user who has had one of their message manually shadow hidden.

As luceos says, the Flarum private API is not a reliable solution.

If you are looking for all messages under approval, at least query for the is_approved column, this way private/hidden-by-default messages from other extensions will not be counted.

Even if all selected posts are indeed spam, I would recommend having a special case/warning to review discussions that contain messages from other users, to avoid deleting an entire discussion if somehow the spam account started a legitimate discussion only to post spam in it later.

Additional note, just keep in mind users and discussions have meta-data that Flarum updates after some events. When performing SQL commands those events are not triggered. Therefore the number of discussion per tag or discussion/post per user will be incorrect until a new post is made for that tag or user. Of course it doesn't matter for the spam user if they are deleted but for tags and other users that participated in those deleted discussions it will cause an incorrect value until another event fixes it.

And just to close my comment, I believe the commands are just a few characters away from deleting all posts and discussions by deleted users. The first statement could return NULL as user_id if any post by a deleted user is still awaiting approval, then it would select all discussions and posts for user NULL and delete them. Luckily it seems like the temporary table doesn't accept null values so this would either error or cause the value to be cast to zero instead, which should be safe-ish.