I absolutely hate advertisement bots, and i wish that their inventors would be blessed with a flesh eating virus. I have been fighting a losing battle over the last two years to regain control over my forums, claiming it back from the shitty bots, and i think i have finally figured things out.
Below is the beginnings of a script that you can run on a semi regular basis and keep your PHPBB3 Databases cleaned of ridiculous bullshit landing on your forum. Just remember to log in from time to time to resynchronize your statistics.
CREATE TEMPORARY TABLE ShittyUsers( user_id int(11) NOT NULL ); INSERT INTO ShittyUsers (user_id) SELECT users.user_id FROM `bbwd23_users` as users LEFT JOIN `bbwd23_posts` as p ON poster_id = user_id WHERE user_type != 2 GROUP BY user_id HAVING COUNT(p.post_id) = 0; DELETE FROM `bbwd23_posts` WHERE poster_id IN (SELECT user_id FROM ShittyUsers) AND post_approved = 0; DELETE FROM `bbwd23_posts` WHERE poster_id IN (SELECT user_id FROM `bbwd23_users` WHERE user_posts = 0 AND user_type != 2) AND post_approved = 0; DELETE FROM `bbwd23_users` WHERE user_id IN (SELECT user_id FROM ShittyUsers) AND user_type != 2; DROP TABLE ShittyUsers; DELETE FROM `bbwd23_topics` WHERE topic_approved = 0;