Top Hosting Forum

WebHosting Reviews Forum


Budget DirectAdmin hosting and domain names

How to clean overspammed PHPBB forum

PHPBB forum script Discussion. PHPBB Questions, Troubleshooting, Errors, Mods and so on..
Hosting Forum Description
PHPBB forum script Discussion. PHPBB Questions, Troubleshooting, Errors, Mods and so on..

How to clean overspammed PHPBB forum

Postby hostingforum » Sat Jun 01, 2013 1:06 pm

Hi, you had an webmaster holiday and your PHPBB forum turned into SPAM forum?

Mine too. What to do? Follow this whole guide on your own risk.

1. create custom antispam question (prevent new spam bots registering)
PHPBB Administration: Spambot countermeasures (left menu item)

Ensure that: Enable spambot countermeasures for registrations is Enabled. My values: Enabled, 5, 5, Enabled, Enabled, Q&A

Q&A is configurable antispam question and answer.

My Q&A are like this similar:
- Please answer if this forum is about cars, bikes or movies (if i have forum about cars, then correct answer is cars.
- Please write down the numbers from this phrasse: a2s6e9f3g1xd4po6
- Please how many characters are in domain name mydomain.com including dot (.)
.... create always your own human questions, bots can count math, write text out of images and recognise thousands of casual antispam questions.

2. Examine forum SPAM posts and users
Learn common words and signs of spam posts/accounts. Browse your board and you may note words that none of non-spam posts use, like viagr* and similar. Accrording to this you may filter out these accounts in PHPBB Administration (step 3)

3. Filtering and deleting spam users using PHPBB
Would be good to have PHPBB Mod for this, but i did not found one advanced posts pruning. If you also did not find, you will need to do it harder and less smooth way.
Go to PHPBB Administration, USERS AND GROUPS (tab), [ Find a member ] (link). Use asterisk (*) as a word replacement, for example: viagr*
For example you may find all users who entered icq, signature and website url (just add * in the fields)and additionally sort it according to post count. Most of such accounts can be spammers (always click on its usernames and look on last posts where you are unsure) Then click Administrate User link in their profile. Then Last option on the page, you select delete user and also all his posts.. (if you want to).
Better way: example i found 500+ users with .pl email on my english forum or 500+ users with email @163.com and 1000+ users with email @sina.com (all most probably some chinesse spammer). They can be deleted in bulk from Admin / USERS AND GROUPS (tab) / Prune Users

4. Filtering and deleting spam users using PHPMyAdmin
If your forum has thousands of spam user for long period of time and you dont have a mysql backup, you would need to do some bulk deleting. Best bet would be special PHPBB Mod (downloaded from PHPBB forum) but i did not found any good for bulk pruning (according to post views and post date for example or according to contained word phrasses). What you can do is execute so called MySQL queries (commands) which will bulk delete matching entries from your forum mysql database.

a) To enter mysql database, you need to go to your hosting control panel and then find out some MySQL manager icon there. Usually its PHPMyAdmin. Login, select mysql database name there.

b) In PHPMyAdmin from step a, click "Export" tab. Select all tables and export, preferably sql.gz to save disk space. You can also do backup from your hosting panel to make more certainty.

c) In PHPMyAdmin click "SQL" tab. There is window where you can add commands to your mysql, delete, insert, replace (update). All these commands can badly modiffy your mysql which can than turn to be useless and not usable, that is why you created backup (export) before. In step d. If you are not 100% sure you made backup of your phpbb forum mysql correctly, do not continue with next steps, especially with mysql queries. Backup should be full complete backup (on shared hosting, backup can be interupted in the middle and you dont notice untill you open the backup file and examine end of the file). There are example mysql queries you can use to clean forum:

d) These queries: http://pastebin.com/pM6Vb89Y in them use words you researched in step 2)
The queries above may delete user accounts, posts and topics. In some cases topic titles saved in phpbb_topics mysql table dont contain phrasses from your mysql queries above and only these topics content (topic text itself, is saved in phpbb_posts table) contain your phrasses. The result is that after you run queries, it will delete only topics text and leave topic titles. In forum front-end when you click some spam topic name, you can see "The requested topic does not exist." message. What you need to do is to delete remaining entries from phpbb_topics table. But how to do it? You may use this command:
Code: Select all
DELETE FROM phpbb_topics WHERE NOT EXISTS (SELECT NULL FROM phpbb_posts p WHERE p.post_id = topic_first_post_id)

So you deleted posts, topics and user accounts which match your words, words you had in your mysql queries mentioned on pastebin.com/pM6Vb89Y

5. Check forum and do post-delete tasks

a) See your PHPBB forum whether everything looks correct and working on frontend, you may try also deleting cache from Administration homepage. If everything looks ok, In PHPMyAdmin click again your mysql database name and see the list of database tables. Now scroll down and click Optimise all tables requiring optimisation, so you reclaim and save the mysql space. Then create new mysql backup (dont replace old one, keep it). Backup is done again from PHPMyAdmin click "Export" tab. Select all tables and export, preferably sql.gz to save disk space. You may do also backup from your hosting control panel to make more certainty.

b) you may also delete huge Search Index. It contains most of the forum words, so its also probably full of spam crap. So after delete from PHPBB Administration / Maintenance / Search Index, you may then Create index again without all the spam. Mysql table "phpbb_wordmatch" will tell you how big is your index. You may disable search function on your forum too if you badly need to save the space and dont create search idnex at all.

6. Dont let spammers rule your forum again
Make sure to check your site from time to time. When you see spam, its time to make better antispam questions, to apply additional rules, to search Google for How to prevent phpbb spam. and get the ideas.

----------
Hope this guide helped you in your SPAM deleting journey on PHPBB, if you have any questions, ideas, fixes, please just post reply to help any future readers of this page in their deleting job. If you liked this posts, please use Facebook Like button and Google + to recommend to others. Thx



Ad: There You can have your forum site-wide banner/text ad for $99 yearly. Contact admin


User avatar
hostingforum
Site Admin
 
Posts: 656
Joined: Fri May 20, 2011 7:58 pm
Location: Czech Republic

Re: How to clean overspammed PHPBB forum

Postby hostingforum » Thu Jun 06, 2013 5:20 pm

Above manual in short

Browse your forum and learn common words for spam posts which canot appear in other non-spam posts. You can write these phrasses down for future use.

Login to your mysql manager like Phpmyadmin. It can be usually accessed from your hosting control panel. Then select your database name there and click "SQL" tab.

Use this command to delete all (1) posts containing phrasse viagr and also all (2) topics names containing this phrasse. Then also all (3) usernames whose signature contains this phrasse. Remember that when you do %viagr% also posts, topics, usernames which contain via_ra or any other phrasse containing "viagr". Such command is irreversible, so FIRST YOU NEED TO BACKUP YOUR MYSQL DATABASE. To do so, go to your hosting control panel and create mysql backup from there. Also in PHPMyAdmin click Tab named "Export" and select all tables + export it into sql.gz file. If you are not sure your backup file was created successfully, dont continue with following queries.

DELETE FROM `phpbb_posts` WHERE (`post_text` LIKE '%viagr%') LIMIT 20000;
DELETE FROM `phpbb_topics` WHERE (`topic_title` LIKE '%viagr%') LIMIT 20000;
DELETE FROM `phpbb_users` WHERE (`user_sig` LIKE '%viagr%') LIMIT 20000;
DELETE FROM phpbb_topics WHERE NOT EXISTS (SELECT NULL FROM phpbb_posts p WHERE p.post_id = topic_first_post_id);

TIP: before runing above "DELETE" command in "SQL" tab from PHPMyAdmin, you may want to first try "SELECT" command which will only list the entries.:

SELECT * FROM `phpbb_posts` WHERE (`post_text` LIKE '%viagr%') LIMIT 100;

After cleanup, you may want to Optimise all mysql tables from PHPMyAdmin (option at the bottom of the tables list).

----
Another TIP:
Should you need to move all users posts to another section here is example command:
UPDATE phpbb_topics SET forum_id="5" WHERE topic_first_poster_name LIKE "%ozerway%" AND forum_id="4" ;
this will move user *ozerway* postcd from section id 4 to section id 5


User avatar
hostingforum
Site Admin
 
Posts: 656
Joined: Fri May 20, 2011 7:58 pm
Location: Czech Republic


Return to PHPBB

Who is online

Users browsing this forum: No registered users and 0 guests

.