1
votes

At the moment I have a serious problem, over a couple of days forum-spammers have breached my phpbb3 forum and posted so many topics and posts that it is just too much for the ACP mass-prune feature to handle (it always gets out of memory and I can't give it more).

So, I was wondering, is their a way to delete these users and posts from the database itself so I don't need any php interaction? Or a phpbb user prune function written in Perl/Python that can run over time?

I've searched the web and every website just says "use the ACP" but at the moment that is not possible for me.

1
a hardcore solution would be to edit the code and add lets say LIMIT 150 on the mysql query - Nikola
You can just write a query to delete all posts from the last few days... - HTDutchy
But that doesn't delete the users nor is it a clean solutions. - Tom
well @Tom what I said would be a working solution as it won't time out anymore - Nikola

1 Answers

3
votes

A semi-manual way to do this is to go to each spam user's profile, select Administer User and then at the bottom of the Overview (the default) page, select Delete Posts. This will delete that specific user and all of their associated topics. This is the recommended way.

Another option is to reduce the criteria on your prune user page. Set smaller date windows, set more specific post criteria, etc.

If the above don't work, this topic also points out a couple very dangerous queries that may be helpful, but it only removes topics that don't have associated users. This is likely the case if you ran your query and it timed out. A portion of the query ran.

Remember, PHPBB does NOT recommend running queries directly. The first two options I provided are their recommended method of removing mass users and posts. These are dangerous to run against your database (translation: make a backup first)

Query 1 - Removes search results:

delete from phpbb_search_wordmatch where post_id in
(SELECT post_id
    FROM `phpbb_posts`
    WHERE topic_id
    IN (
       SELECT topic_id
       FROM phpbb_topics
       WHERE `topic_poster` NOT
       IN (
           SELECT user_id
           FROM phpbb_users
       )
    )
)

Query 2 - Removes Topics:

DELETE
FROM `phpbb_topics`
WHERE `topic_poster` NOT
IN (
    SELECT user_id
    FROM phpbb_users
)

Query 3 - Removes Posts:

DELETE
FROM `phpbb_posts`
WHERE topic_id
IN (
    SELECT topic_id
    FROM phpbb_topics
    WHERE `topic_poster` NOT
    IN (
        SELECT user_id
        FROM phpbb_users
    )
)