The spam accounts have now been cleared up with the help of some clever SQL.
Ban all users that made a post today
INSERT INTO `phpbb_banlist` (`ban_userid`, `ban_start`, `ban_reason`, `ban_give_reason`)
SELECT `user_id`, UNIX_TIMESTAMP(), 'Spambot', 'Spambot' FROM `phpbb_users` WHERE `user_id` IN (SELECT DISTINCT `poster_id` FROM `phpbb_posts` WHERE `post_time` >= 1422576000);
Ban all users that made a topic with less that 20 views and with a user ID above 3000
INSERT INTO `phpbb_banlist` (`ban_userid`, `ban_start`, `ban_reason`, `ban_give_reason`)
SELECT `topic_poster`, UNIX_TIMESTAMP(), 'Spambot', 'Spambot' FROM `phpbb_topics` WHERE `topic_views` < 20 AND `topic_poster` > 3000 GROUP BY `topic_poster`
Ban all users that have made a topic and added more than one post to it with an ID over 3000
INSERT INTO `phpbb_banlist` (`ban_userid`, `ban_start`, `ban_reason`, `ban_give_reason`)
SELECT `topic_poster`, UNIX_TIMESTAMP(), 'Spambot', 'Spambot' FROM `phpbb_topics` WHERE `topic_replies` > 0 AND `topic_poster` = `topic_last_poster_id` AND `topic_poster` > 3000 GROUP BY `topic_poster`
Add a manual list of users to the ban list
INSERT INTO `phpbb_banlist` (`ban_userid`, `ban_start`, `ban_reason`, `ban_give_reason`)
SELECT `user_id`, UNIX_TIMESTAMP(), 'Spambot', 'Spambot' FROM `phpbb_users` WHERE `username` IN ('onthwguhv', /* snip */, 'eorhonkas');
Delete all posts made by banned users
DELETE FROM `phpbb_posts` WHERE `poster_id` IN (SELECT `ban_userid` FROM `phpbb_banlist` WHERE `ban_userid` != 0);
Delete all topics with no posts
DELETE FROM `phpbb_topics` WHERE `topic_id` NOT IN (SELECT DISTINCT `topic_id` FROM `phpbb_posts`);
Reset all post counts of banned users to 0
UPDATE `phpbb_users` SET `user_posts` = 0 WHERE `user_id` IN (SELECT `ban_userid` FROM `phpbb_banlist` WHERE `ban_userid` != 0);
Finally pruned all users with 0 posts via the admin control panel.
Recaptcha has been enabled for registration now which should help with the bot registrations, I'll keep an eye on it and switch to admin approval if we have to.
I'm hoping to get back into being a regular forum user here again and will be sending out an email to those who may be interested in returning in the next few days.
Finally I would like to say an massive thank you to Temor who battled the bots for a long time keeping this place tidy. I'm also very sorry for neglecting the place for so long. I have some new ideas and a nice new mic though so perhaps some new content will make up for it
