Intro

The guide will demonstrate how to import IP2Proxy Proxy Detection data (PX11 IPv6) in CSV form into IBM Db2 and then query the data in a PHP 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. We’ll store ours in C:\myfolder but you can choose any folder you wish.

Important Note

We will not cover installation of Db2 or PHP in this guide. We will assume you have already set up Db2 and PHP on the localhost and are using PHP via Apache (also on the localhost). For this example, we are using a Windows 10 machine.

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 = 39;

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 $others = $3;
		my $resultfrom = $padchar x ($padlen - length($ipnumfrom));
		$resultfrom .= $ipnumfrom;
		my $resultto = $padchar x ($padlen - length($ipnumto));
		$resultto .= $ipnumto;
		print OUT '"' . $resultfrom . '","' . $resultto . '",' . $others . "\n";
	}
}
close OUT;
close IN;

Run the Perl script by calling the below command in command prompt:

perl createindex.pl

Importing the CSV data into Db2

In the DB2 Command Line Processor, run the following command to create the database.

CREATE DATABASE ip2prox

After creating the database, you need to connect to it by running the below command. Our username is “db2admin” and password is “12345”. Change it to your own credentials.

CONNECT TO ip2prox USER db2admin USING "12345"

Next, run the following command to create the table.

CREATE TABLE px11ipv6(ip_from CHAR(39) NOT NULL, ip_to CHAR(39) NOT NULL, proxy_type VARCHAR(3), country_code CHAR(2), country_name VARCHAR(64), region_name VARCHAR(128), city_name VARCHAR(128), isp VARCHAR(256), domain VARCHAR(128), usage_type VARCHAR(11), asn VARCHAR(6), "as" VARCHAR(256), last_seen INT, threat VARCHAR(128), provider VARCHAR(256), PRIMARY KEY (ip_from, ip_to))

Now that we have a table, we will commence the import of data from our CSV file into the table.

LOAD FROM "C:\myfolder\INDEXED.CSV" OF DEL INSERT INTO px11ipv6

After the import is done, one last command is needed to make the table ready for queries.

SET INTEGRITY FOR px11ipv6 IMMEDIATE CHECKED

Querying the data in PHP

Now, create a PHP file called test.php in your website.

Paste the following PHP code into it and then run it in the browser:

<?php
$ip = '2600:1F18:45B0:5B00::';

function ip62long($ipv6) {
  return (string) gmp_import(inet_pton($ipv6));
}

function queryIP2Proxy($myip) {
  $database = 'ip2prox';
  $user = 'db2admin';
  $password = '12345';
  
  $conn = db2_connect($database, $user, $password);
  
  if ($conn) {
    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 39 digits with zeroes in front so we can do string comparison
    $ipnum = str_pad($ipnum, 39, '0', STR_PAD_LEFT);
    
    $stmt = db2_prepare($conn, 'SELECT * FROM px11ipv6 WHERE ? >= ip_from AND ? <= ip_to ORDER BY ip_from, ip_to LIMIT 1');
    if ($stmt) {
      if ($rs = db2_execute($stmt, [$ipnum, $ipnum])) {
        if ($result = db2_fetch_assoc($stmt)) {
          return $result;
        }
        else {
          die("No record found.<br>\n");
        }
      }
      else {
        die("Error: " . db2_stmt_errormsg() . "<br>\n");
      }
    }
    else {
      die("Error: " . db2_stmt_errormsg() . "<br>\n");
    }
    db2_close($conn);
  }
  else {
    die("Error: Could not connect.<br>\n");
  }
}

$myresult = queryIP2Proxy($ip);

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"; // this is lowercase due to the name being a reserved keyword
echo 'LAST_SEEN: ' . $myresult["LAST_SEEN"] . "<br>\n";
echo 'THREAT: ' . $myresult["THREAT"] . "<br>\n";
echo 'PROVIDER: ' . $myresult["PROVIDER"] . "<br>\n";
?>