Intro
The aim of this guide is to demonstrate how to import IP2Proxy data (PX11 IPv6) in CSV form into MongoDB and then query the data in a PHP web page.
First of all, you will need to download the IP2Proxy PX11 IPv6 CSV file.
Download commercial version at https://ip2location.com/download?code=PX11IPV6
Extract out the IP2PROXY-IPV6-PROXYTYPE-COUNTRY-REGION-CITY-ISP-DOMAIN-USAGETYPE-ASN-LASTSEEN-THREAT-RESIDENTIAL-PROVIDER.CSV file from the downloaded zipped file.
Important Note
We will not cover installation of either MongoDB or PHP in this guide. We will assume you have already set up MongoDB and are using PHP via Apache on a Linux or Windows platform. For our example, our MongoDB has a user called mongoAdmin with changeMe as the password. This user has been granted the privilege to drop databases. Please note that this is a requirement since we will drop the existing database and collection during import.
To grant the necessary permission, run the following in the mongo client.
use admin;
db.grantRolesToUser("mongoAdmin", ["root"]);
Pre-requisites
To install the MongoDB PHP driver, Linux users can run the following commands in Bash.
pecl install mongodb echo "extension=mongodb.so" >> `php --ini | grep "Loaded Configuration" | sed -e "s|.*:\s*||"`
Windows users can get the driver from https://windows.php.net/downloads/pecl/releases/mongodb/ and then edit the php.ini to load the dll.
To install the MongoDB PHP Library, you will need to have the PHP composer for either Linux or Windows then run the following command.
composer require mongodb/mongodb
NOTE: Make sure you run the above command in the folder where your PHP page is going to be created.
Preparing the CSV data for import
We will be using a Perl script to create an index field in the CSV data to speed up queries later.
Create a new Perl file called createindex.pl and paste the following code into it:
use strict;
my $dir = "./";
my $filename = $dir . "IP2PROXY-IPV6-PROXYTYPE-COUNTRY-REGION-CITY-ISP-DOMAIN-USAGETYPE-ASN-LASTSEEN-THREAT-RESIDENTIAL-PROVIDER.CSV";
my $filename2 = $dir . "INDEXED.CSV";
my $padchar = "0";
my $padlen = 40;
open IN, "<$filename" or die "Cannot open input file";
open OUT, ">$filename2" or die "Cannot open output file";
while (<IN>)
{
my $line = $_;
if ($line =~ /^"([^"]+)","([^"]+)",/)
{
my $ipnumfrom = $1;
my $ipnumto = $2;
my $resultfrom = $padchar x ($padlen - length($ipnumfrom));
$resultfrom .= $ipnumfrom;
my $resultto = $padchar x ($padlen - length($ipnumto));
$resultto .= $ipnumto;
print OUT '"A' . $resultfrom . '","A' . $resultto . '",' . $line;
}
}
close OUT;
close IN;
Run the Perl script by calling the below command in command prompt:
perl createindex.pl
Importing the CSV data into MongoDB
For Linux
At the command prompt, run the following command:
mongoimport -u mongoAdmin -p changeMe --authenticationDatabase admin --drop --db ip2proxy --collection px11 --type csv --file "/var/www/TestMongoPHP/INDEXED.CSV" --fields ip_from_index,ip_to_index,ip_from,ip_to,proxy_type,country_code,country_name,region_name,city_name,isp,domain,usage_type,asn,as,last_seen,threat,provider
My CSV file folder is /var/www/TestMongoPHP while your folder may be different so remember to change to the folder where you have extracted the CSV file earlier.
For Windows
At the command prompt, run the following command:
C:\mongodb\bin\mongoimport -u mongoAdmin -p changeMe --authenticationDatabase admin --drop --db ip2proxy --collection px11 --type csv --file "C:\inetpub\wwwroot\TestMongoPHP\INDEXED.CSV" --fields ip_from_index,ip_to_index,ip_from,ip_to,proxy_type,country_code,country_name,region_name,city_name,isp,domain,usage_type,asn,as,last_seen,threat,provider
My MongoDB is installed in C:\mongodb but if your installation folder is different, remember to change the example above. Same with the CSV file folder; mine is C:\inetpub\wwwroot\TestMongoPHP\ so remember to change to the folder where you have extracted the CSV file earlier.
Create index to speed up queries
For Linux
Next, create a text file called buildindexipv6 and paste the following code into it:
use ip2proxy
db.px11.createIndex({ip_from_index: 1, ip_to_index: 1})
exit
Save the buildindexipv6 and then run the following in command prompt:
mongo -u mongoAdmin -p changeMe --authenticationDatabase admin < buildindexipv6
For Windows
Next, create a text file called buildindexipv6.txt and paste the following code into it:
use ip2proxy
db.px11.createIndex({ip_from_index: 1, ip_to_index: 1})
exit
Save the buildindexipv6.txt and then run the following in command prompt:
C:\mongodb\bin\mongo.exe -u mongoAdmin -p changeMe --authenticationDatabase admin < buildindexipv6.txt
Again, my MongoDB is installed in C:\mongodb but if your installation folder is different, remember to change the example above.
Querying the IP2Proxy data from a PHP web page
Now, create a PHP file called testipv6.php in your website.
Paste the following PHP code into it and then run it in the browser:
<?php
require 'vendor/autoload.php';
// ip address to test
$ip = "2600:1F18:45B0:5B00::";
$user = "mongoAdmin";
$pwd = "changeMe";
$m = new MongoDB\Client("mongodb://${user}:${pwd}@localhost:27017");
// select a database
$db = $m->ip2proxy;
// select a collection (analogous to a relational database's table)
$collection = $db->px11;
function ip62long($ipv6) {
return (string) gmp_import(inet_pton($ipv6));
}
function queryIP2Proxy($myip) {
global $collection;
// convert IP address to IP number
if (filter_var($myip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4)) {
$myip = '::FFFF:' . $myip;
}
if (filter_var($myip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV6)) {
$ipnum = ip62long($myip);
}
// pad ipnum to 40 digits with zeroes in front so we can do string comparison
$ipnum = 'A' . str_pad($ipnum, 40, '0', STR_PAD_LEFT);
// filter those ip_from and ip_to which cover our IP number
$query = array('ip_from_index' => array('$lte' => $ipnum), 'ip_to_index' => array('$gte' => $ipnum));
// perform query and return a single result
$retarr = $collection->findOne($query);
return ($retarr);
}
// call the function to get IP2Proxy information on the ip address
$myresult = queryIP2Proxy($ip);
// output the result
echo 'proxy_type: ' . $myresult["proxy_type"] . "<br>\n";
echo 'country_code: ' . $myresult["country_code"] . "<br>\n";
echo 'country_name: ' . $myresult["country_name"] . "<br>\n";
echo 'region_name: ' . $myresult["region_name"] . "<br>\n";
echo 'city_name: ' . $myresult["city_name"] . "<br>\n";
echo 'isp: ' . $myresult["isp"] . "<br>\n";
echo 'domain: ' . $myresult["domain"] . "<br>\n";
echo 'usage_type: ' . $myresult["usage_type"] . "<br>\n";
echo 'asn: ' . $myresult["asn"] . "<br>\n";
echo 'as: ' . $myresult["as"] . "<br>\n";
echo 'last_seen: ' . $myresult["last_seen"] . "<br>\n";
echo 'threat: ' . $myresult["threat"] . "<br>\n";
echo 'provider: ' . $myresult["provider"] . "<br>\n";
?>