Removing SPAM entries from phpbb

[^rooker]

[PROBLEM]
Just recently we had to forbid guest posting due to heavy spamming ... *sigh*

A major problem with those entries (>600 !!!) were that it would take AGES to delete them.



[SOLUTION]
Use some program to execute SQL queries (e.g. phpMyAdmin) and look for Anonymous entries, containing http:// links

Code: Select all

select p.post_time, u.username, pt.post_subject, pt.post_text
from phpbb_posts p, 
phpbb_posts_text pt, 
phpbb_users u

where p.post_id=pt.post_id
and p.poster_id=u.user_id
and pt.post_text like '%http://%'
and u.user_id=-1

order by p.post_time; 

You could add this line to limit the listing to start at a certain point in time:

Code: Select all

and p.post_time > 1140575047

(the number here is just some date I used)



Now you could use a subselect to remove all entries from "phpbb_posts" and "phpbb_posts_text" that contains a "post_id" showing up in this listing.
(Unfortunately subselects are only possible from MySQL >v4.1, so I've just created heavy inconistency in our board which I will fix as soon as I have time to)

[^rooker]

Here's a query I'm using to check foreign-key inconsistencies between "posts" and "posts_text":

Code: Select all

SELECT * FROM phpbb_posts_text pt
LEFT JOIN phpbb_posts p
ON pt.post_id=p.post_id
WHERE p.post_id IS NULL

the output of this should be empty - if it's NOT, that's bad.