As an aside, I found it interesting that often these spammy posts would happen late in the evening, or on a Friday night, like they were trying to get at least a weekend out of it, before an administrator could delete them Monday morning.
I have never published an official MOD before, so I'm just going to describe how to implement these changes manually. This is not push button like other mods, but with some basic PHP, Perl, and mysql knowledge you should hopefully be able to follow these steps. There are 2 parts to implementing this mod:
1) Set up a database that allows a lookup from an IP address to a country
2) Modify ucp_register.php to perform this lookup, and country check
1) Set up a database that allows a lookup from an IP address to a country:
Create a new mysql database and then save the following to a file named createtable.sql:DROP TABLE IF EXISTS `bycountry`;
CREATE TABLE `bycountry` (
`ID` mediumint(8) unsigned NOT NULL auto_increment,
`min` int(10) unsigned NOT NULL,
`max` int(11) unsigned NOT NULL,
`ctry` varchar(2) NOT NULL,
`country` varchar(255) NOT NULL,
PRIMARY KEY (`ID`),
KEY `minval` (`min`),
KEY `maxval` (`max`)
) ENGINE=MyISAM AUTO_INCREMENT=1209189 DEFAULT CHARSET=latin1;
You need to know the database username/password that you created in the last step, and have command line access to mysql. You could also use something like phpMyAdmin. Somehow you need to create this database and a table with the above format. For the mysql approach, run this from a command line:
mysql -u username -ppassword dbasename < createtable.sql
Now that the database and table format are created, populate the table with data. You can download this data in a CSV file (IpToCountry.csv) from: http://software77.net/geo-ip/
We will use a Perl script to transfer the data from this downloaded CSV file into our database. Save the following to filltable.pl:
#!C:/Perl/bin/perl
use strict;
use DBI;
my $database_host = "myhost.com";
my $database_port = 3306;
my $database_username = "username";
my $database_password = "password";
my $db_name = "dbasename";
my $ip_file = 'IpToCountry.csv';
# connect to the database
my $dbh = DBI->connect("dbi:mysql:$db_name:$database_host:$database_port", $database_username, $database_password);
unless ($dbh) {
print "-E- Could not connect to mySQL database\n";
exit(1);
}
# remove all the current rows, leaving table structure/definition in tact
my $add_sth = $dbh->prepare("DELETE FROM bycountry");
$add_sth->execute();
# re-add new ones
$add_sth = $dbh->prepare("INSERT IGNORE INTO bycountry (min, max, ctry, country) VALUES(?, ?, ?, ?)");
open(FILE, "< $ip_file") || die "Could not open $ip_file";
while (<FILE>) {
# File format is:
# IP FROM IP TO REGISTRY ASSIGNED CTRY CNTRY COUNTRY
# "1346797568","1346801663","ripencc","20010601","IL","ISR","ISRAEL"
next if (/^#/);
chomp;
my ($startip, $endip, $registry, $assigned, $ctry, $cntry, $country) = split('","');
next if (! defined($country));
$startip =~ s/^\"//;
$country =~ s/\"$//;
$add_sth->execute($startip, $endip, $ctry, $country);
}
close(FILE);
exit(0);
You will need to adjust the relevant items at the top of the script, like the path to your perl, your database info, etc. Once you have this done, run this filltable.pl script from the command line to fill in your table. If completed properly, your table should have over 140,000 rows as of the time of this writing. You can also use this script again to periodically refresh the table with updated data. Just re-download the IPToCountry.csv file, and then run this script again.
2) Modify ucp_register.php to perform this lookup, and country check
Open up the existing file: <phpbb3_path>\includes\ucp\ucp_register.php in an editor. Below the section that does the optional DNSBL check we will add our modification. Search for "DNSBL" and below that "if" block, add this:
// My MOD for banning registrations by country
$mabmod_dbhost = 'myhost.com';
$mabmod_dbusername = 'username'; // read only user
$mabmod_dbuserpassword = 'password';
$mabmod_default_dbname = 'dbasename';
$mabmod_link_id = mysqli_connect($mabmod_dbhost, $mabmod_dbusername, $mabmod_dbuserpassword, $mabmod_default_dbname, 3306);
if(!$mabmod_link_id)
{
$error[] = "Connection failed to the host $mabmod_dbhost.";
}
else
{
$mabmod_ips = explode('.', $user->ip);
$mabmod_ipnumber = ($mabmod_ips[3] + $mabmod_ips[2] * 256 + $mabmod_ips[1] * 256 * 256 + $mabmod_ips[0] * 256 * 256 * 256);
$mabmod_query = "SELECT country FROM bycountry WHERE '$mabmod_ipnumber' >= min AND '$mabmod_ipnumber' <= max LIMIT 1";
$mabmod_result = mysqli_query($mabmod_link_id, $mabmod_query);
if(!$mabmod_result) $error[] = mysqli_error($mabmod_link_id);
else
{
if(mysqli_num_rows($mabmod_result) > 0)
{
if(!($mabmod_db_data = mysqli_fetch_array($mabmod_result))) $error[] = "Error fetching data.";
else
{
if (preg_match('/Pakistan/i', $mabmod_db_data['country'])) $error[] = "Registrations banned from this country";
}
}
}
mysqli_close($mabmod_link_id);
}
// End my MOD
Again you will need to modify the database info at the top, and optionally change the country check to whatever you want to block. You can add additional lines to block multiple countries if necessary.
To test this mod out, you might add another line for checking your own country, something like:
if (preg_match('/United States/i', $mabmod_db_data['country'])) $error[] = "Testing - Registrations temporarily disabled...";
I hope this proves helpful to others in preventing spammers on your board.
