INFORMATICA - INFORMÁTICA - INFORMATION TECHNOLOGY

That strange sensation of something going wrong. The untouchable feel of bug shimmering in the air around you...
And the confirm! PhpBB failed! I have just seen it!
Version: 3.0.7-PL1-5 (Debian)
When you administer a forum, like in PhpBB, you often ban an IP address. Yes, you must first consider that many people around the world have dynamic addresses, but when the same kind of spam comes from the same IP you just ban that IP. I use to keep a text file where I write down the IP ascending. I separate the networks with a horizontal line of dashes, and I specify the IP range taken from the whois, well provided near the IP by the PhpBB user administration in ACP. I take note of the IPs, and then I delete the user. The second time an IP spams, I ban the IP.
In order to punish the spammer, I go through the MySQL database searching for other users from the same IP, subscribed before I did the IP ban, to delete them.
When you search for users in ACP, a pop-up windows appears, with a form on top to fill in, and one of the fields is the IP. This method works with asterisks in place of numbers when you are specifying a subnet.
For example the subnet 46.118.158.0, written in technical correct form, in the whois is referred to as "46.118.158.0 - 46.118.158.255", not in the classic 46.118.158.0/24 way. This is for newbies, maybe, but is a very simple and immediate representation.
In the pop-up form, instead, you have to write it as 46.118.158.*
I do not suggest to ban subnets, because it is not justice for the other users of that net. Sometimes it seems a pirate bay, where all are spammers, but you are cutting out potential clients that nothing of it deserve.
Specified this, you can just try out to enter a single IP and see which users are registered from it... And strange results you will obtain!
Doing this work everyday, happens to remember at-the-fly some IPs, and I realized that something was going wrong. It is very disappointing, when some of the result users have just a different IP!
So I connected to MySQL database from command line, I choosed the DB "phpbb" and then queried to the table "phpbb_users". I immediately realized that all my checks done until that moment were nothing useful. My DB was FULL of users subscribed from IP I then banned! Horrible.
So I wrote a script. Below you can read the SQL statement inside a shell-script if you want to estract it.
In the same days, I was trying to unban some subnets, for the reason explained above. And again the ACP fails to unban anything! I am pretty sure the first times I did it, it worked. But since a day and on, I noticed that almost every IP, with asterisks or not, appears doubled in the list. Lately I was not able to unban anyone from the ban list. Damn.
I wrote a second statement to do it from mysql command line interface (CLI). My lazyness told me to implement it in the same shell-script, and I create a little menu. Again you can extract the statement or copy the entire script if you like it, but I signal the must to be SQL skilled to do what I did, a skill you should not be requested to, to administer PhpBB.
The script askes you for the MySQL root password (it is not suggested to store it on the server, anywhere). But in this was you must enter it ONCE, while the script access the DB every time.

############################ MAIN #########################################
echo "Insert password for root:"
read ROOTPWD

while true ; do
        echo "1 - Select an IP range from users"
        echo "2 - Unban an IP from banlist"
        read CHOICE

        case ${CHOICE} in

        1)
        echo "Enter an IP range to select:"
        read IPRANGE

        #mysql -u root -p phpbb3 <<EOS
        mysql -u root --password=${ROOTPWD} phpbb3 <<EOS

        select username, user_ip
        from phpbb_users
        where user_ip like '${IPRANGE}%'
        order by user_ip
        ;
        select count(*)
        from phpbb_users
        where user_ip like '${IPRANGE}%'
        ;
EOS
        ;;

        2)
        echo "Enter an IP to UNban:"
        read IPRANGE

        #mysql -u root -p phpbb3 <<EOS
        mysql -u root --password=${ROOTPWD} phpbb3 <<EOS


        delete from phpbb_banlist where ban_ip='${IPRANGE}';
EOS
        ;;

        *)
        echo "------------------------------------>Enter 1 or 2"
        ;;
        esac

        echo "Ctrl-C to exit"

done

Forum topic

IT INDEX
SITE INDEX