PHP, MySQL and spam

Yes, those three are my favourites, especially when running a phpBB forum. Leave a forum alone for a week and the spambots spring into action. It is like they can smell it. 10k posts with the usual content. Links to the sites that you just have see, pr0n, long texts, funny jokes in languages you do not understand and of course a picture of Putin as Neo kicking ass together with his team. The server felt very dirty because of all this, so the poor thing decided to restart and forget it ever had a network card.

So, what to do with all the spam? The first thing that comes into mind is to just round it all up and delete all the accounts together with their posts. But I did not want to accidentally delete a real user, so I decided to take it a little slower.

“The query is too big, database corrupted!” Eeeh, what? O_o

Restore the database from backup, increase the query size of the database and the allowed runtime of php-scrips, and try again. Now the scripts just crash and give me a blank page. When I check the forum the user and all its topics are still there. When I click on a topic I get the messages that say it does not exist. When I try to delete the topic I am told that the topic does not exits. Interesting. The user does still exist so I delete the user and all its posts. Now the user behaves the same as its topics. Ghosts.

This is a known problem and the solution is to remove all the posts a few at a time. Manually. You serious? Apparently they are. And you can not do it directly from MySQL because that is “dangerous”, so I have to dig through the code to get the query needed for that. No! Lets restore the database and do as the developers want.

I delete the users with 0 or very few posts in big chunks and dump the database after every delete. As soon as I do not get the big green OK screen, I restore the database. When the only spammers left have more than 700 posts, as they corrupt the database if deleted, I start the manual cleaning. I take it forum by forum and it seems doable. then I found them. The ghosts. Even though I got a big green “Everything is OK” screen after every deletion, the database still got corrupted. I love this feeling :D

Time to restore the database and start all over again. Then i get an idea. What if I create a new forum, spam for example, move all the spamposts to it, then delete the forum and all the posts in it and then delete all the users who now will have 0 posts? Said and done. I start moving all the posts, user by user. Every query is like lightning, even with 2k posts. Deleting the spam forum with 7k posts in it is fast. Then it is no problem deleting all the users with 0 posts.

If you ever have to clean out spam from a phpBB forum, this is how you do it:

  1. Take a backup of the database.
  2. Create a new forum.
  3. Move all the spammers’ posts to the new forum, user by user.
  4. Delete the new forum and all its posts.
  5. Delete all the spammers’ accounts.

Comments in the forum.

Recognition!

This is a day for celebration!
It have just come to our knowledge that we appear to be blocked by Trend Micro’s web filter. To be more precise, in the category called games.
Trend Micro have over 5000 employees, so very cool :D

Comments in the forum.