Importing IP2Proxy data into PostgreSQL and querying with PHP (IPv6)

Intro

The guide will demonstrate how to import IP2Proxy data (PX10 IPv6) in CSV form into PostgreSQL and then query the data in a PHP page.

First of all, you will need to download the IP2Proxy PX10 IPv6 CSV file.

Download commercial version at https://ip2location.com/download?code=PX10IPV6

Extract out the IP2PROXY-IPV6-PROXYTYPE-COUNTRY-REGION-CITY-ISP-DOMAIN-USAGETYPE-ASN-LASTSEEN-THREAT-RESIDENTIAL.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 PostgreSQL or PHP in this guide. We will assume you have already setup PostgreSQL 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.

 

Importing the CSV data into PostgreSQL

In the psql shell, run the following command to create the database.

CREATE DATABASE ip2proxy WITH ENCODING 'UTF8';

 

After creating the database, you need to select it by running the below command.

\c ip2proxy

 

Next, run the following command to create the table.

CREATE TABLE ip2proxy_px10_ipv6( ip_from decimal(39, 0) NOT NULL, ip_to decimal(39, 0) NOT NULL, proxy_type character varying(3) NOT NULL, country_code character(2) NOT NULL, country_name character varying(64) NOT NULL, region_name character varying(128) NOT NULL, city_name character varying(128) NOT NULL, isp character varying(256) NOT NULL, domain character varying(128) NOT NULL, usage_type character varying(11) NOT NULL, asn character varying(6) NOT NULL, "as" character varying(256) NOT NULL, last_seen int NOT NULL, threat character varying(128) NOT NULL, CONSTRAINT ip2proxy_px10_ipv6_pkey 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.

COPY ip2proxy_px10_ipv6 FROM 'C:\myfolder\IP2PROXY-IPV6-PROXYTYPE-COUNTRY-REGION-CITY-ISP-DOMAIN-USAGETYPE-ASN-LASTSEEN-THREAT-RESIDENTIAL.CSV' WITH CSV QUOTE AS '"';

WARNING: If you encountered a “could not stat file” error, try the below import command instead.

COPY ip2proxy_px10_ipv6 FROM PROGRAM 'cmd /c "type C:\myfolder\IP2PROXY-IPV6-PROXYTYPE-COUNTRY-REGION-CITY-ISP-DOMAIN-USAGETYPE-ASN-LASTSEEN-THREAT-RESIDENTIAL.CSV"' WITH CSV QUOTE AS '"';

 

 

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) {
  $user = 'postgres';
  $pass = '12345';
  $host = 'localhost';
  $port = 5432;
  $dbname = 'ip2proxy';
  $conn_string = "host=$host port=5432 dbname=$dbname user=$user password=$pass";
  
  if ($conn = pg_connect($conn_string)) {
    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);
    }
    
    if ($rs = pg_query_params($conn, 'SELECT * FROM ip2proxy_px10_ipv6 WHERE $1 >= ip_from AND $1 <= ip_to ORDER BY ip_from, ip_to LIMIT 1', [$ipnum])) {
      if ($result = pg_fetch_all($rs)) {
        return $result[0];
      }
      else {
        die('No record found.' . "<br>\n");
      }
    }
    else {
      die('Error: ' . pg_last_error() . "<br>\n");
    }
  }
  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";
echo 'last_seen: ' . $myresult["last_seen"] . "<br>\n";
echo 'threat: ' . $myresult["threat"] . "<br>\n";
?>

 

Was this article helpful?

Related Articles