Cleaning up and upgrading from phpBB Version: 2.0.22 to 3.08

Buliding digital bulletin boards and forums
KBleivik
Site Admin
Posts: 178
Joined: Tue Sep 29, 2009 6:25 pm
Location: Moss Norway
Contact:

Cleaning up and upgrading from phpBB Version: 2.0.22 to 3.08

Postby KBleivik » Wed Jun 01, 2011 9:42 pm

We have an old forum http://www.forumnorway.com that has not been upgraded in some years. The forum has been closed for posting, but during the last months there has been a remarkable increases in the number of members. Taking a fast look at the members, we noted that most of them were spam bots, many from the same Ip and email adresses. We could of course delete them by some simple phpMyAdmin SQL statements or we could restore the forum to an older date. Then we would loose serious members that have signed up during the last months. But because of cross relations in the tables it is not recommended to issue SQL statements in the phpMyAdmin panel to delete members. We have a php tool http://starfoxtj.no-ip.com/ developed for php 2.*

Development for the Admin and Upload toolkits has discontinued due to the release of phpbb v3 which includes many of the toolkit features. I appreciate everyone\'s support and feedback and am glad my script(s) provided assistance to those in the past.


that we have modified so it can delete x members from the database and fix the cross relations in one operation. For our job it is a perfect tool.

We always start with those that have only signed up but not visitied the forum, a clear indication of a spam bot. But since our forum is on a shared host, we have problems with deleting more than 5000 with a given status at a time (in this case Visit -). In phpBB 3.08 it is easy to bulk delete members with a given characteristic in the ACP. Among other things, our php script performes this operation.

Code: Select all

<option value="5000"<?php if( $_SESSION['limit_num'] == 5000 ) { echo ' selected'; } ?>>5000 Users</option>
<option value="10000"<?php if( $_SESSION['limit_num'] == 10000 ) { echo ' selected'; } ?>>10000 Users</option>


The first line is OK, even if it takes a few minutes for every operation on our shared host. The second is too big with the memory given to us. In the cPanel under PHP configuration, I see

Resource Limits memory_limit Maximum amount of memory a script may consume (8MB).


The value is 32 MB, so we could of course try to "steal" more memory from our shared host, e.g. doubling that value by the following code:

Code: Select all

php_value memory_limit 64M

in our .htaccess file. But then other members have to suffer. So we decided to stay with the default memory distributed to us.

The second option above deleting 10 000 members at a time seem to be too heavy for our php / memory configuration.

So with 220 000 registered, we have to use the first operation above until all members that have not been active are deleted. While the above is written two operations, that is 10 000 inactive members are deleted.

It is also possible to set up a cron job

Code: Select all

#!/usr/bin/php -q
<?php
  // cron job to delete inactive users older than 48 hours
  $db=mysql_connect('server','user','password');
  mysql_select_db('your_phpbb_database_here',$db);
  $strSQL="DELETE phpbb_users u, phpbb_user_group ug, " .
    "phpbb_groups AS g FROM phpbb_users u, " .
    "phpbb_user_group ug, phpbb_groups g WHERE " .
    "u.user_active=0 AND u.user_id>0 AND " .
    "u.user_id=ug.user_id AND ug.group_id=g.group_id " .
    "AND g.group_single_user=1 AND " .
    "FROM_UNIXTIME(u.user_regdate)<" .
    "DATE_SUB(NOW(),INTERVAL 2 DAY);";
  mysql_query($strSQL,$db) or die(mysql_error());
  mysql_close($db);
?>


as explained in the above code: http://boonedocks.net/mike/archives/134 ... n-Job.html

We are now going on a holiday and http://www.forumnorway.com will be closed for visitors. Every visitor to the forum is now rejected at the door. When it returns, it will be upgraded to phpBB version 3.08.

With version 3.08 that is used on this forum, we have much stronger control over spam bots, e.g. in the ACP panel under Spambot countermeasures. The default settings is often good enough, but they can be made stricter.

I have not used many resources on this, since I have let the bots register without worrying about them. But it is interesting to see how active spammers are even when the forum / bulletin or message board is closed for posting.

So while the rest of this post is written 25 000 members have been deleted and the next 5000 is being deleted from the database tomorrow.

It is late night, so it is time to go to bed. The rest of the members with certain characteristics will be deleted tomorrow when I take a breakfeast and prepare for the holiday.

Now it is moring and while I had my breakfast another 20 000 members with no activity are deleted from the database. All in all 100 000 inactive members are now deleted without great effort. That is about 50 % of the members that have signed up. There are 125 000 members left that have confirmed their registration. A fast look at their email adresses confirm that there are still a large number of spam bots in the database. Two typical examples are users, most probably the same spambot with the emai adresses like:
  1. @ultradrugbuy.com
  2. @myownemail.com
Aonther look at the usernames reveals typical spambots. Usernames that start with the # are typical. I search by that character and a lot more members can be automatically deleted. The same with user names that start with 004, 005, 006, 007, 008, 009. User names that end with Lien and have the same registration date as last visit can also be bulk deleted.

Another mouse click option in the good old Starfoxtj toolkit is Active / Inactive. That shows an interesting thing. Even today with a mild blocking, some have registered or most probably they were logged in when I started the cleanup. They show no activity and are bulk deleted. Sorry if I delete a person, but I doubt it.

Now I have a manageable database. In the phpMyAdmin panel, some new mouse clicks and the database is exported both to my laptop and my computer as a flat SQL file. It is time to delete the whole old database, so there is nothing to corrupt and start upgrading step by step via older version to the last version of phpBB. That will not be finished before I come back from holiday. Until then http://www.forumnorway.com is closed for visitors.

When phpBB 3.08 is installed, we will have more control over the database than in the old ACP + Starfoxtj toolkit. It is much easier to finetune the database in the 3.08 phpBB ACP.

A final note.

Please forgive me if I deleted a person from the database. We have to automate some tasks to be able to handle the board. As stated in the board's rules, we can not use too much time on moderating this board, so you have to sign up again.

Return to “Bulletin board and forum software”

Who is online

Users browsing this forum: No registered users and 1 guest