The aim of this guide is to demonstrate how to import IP2Proxy data (PX11 IPv6) in CSV form into Oracle Database and then query the data in a PHP web page.

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 Oracle or PHP in this guide. We will assume you have already setup Oracle 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 Oracle

Run the following SQL in Oracle to create the table and import the data from the CSV file:

CREATE TABLE ip2proxy_px11_ipv6(
  ip_from CHAR(39),
  ip_to CHAR(39),
  proxy_type VARCHAR2(3),
  country_code CHAR(2),
  country_name VARCHAR2(64),
  region_name VARCHAR2(128),
  city_name VARCHAR2(128),
  isp VARCHAR2(256),
  domain VARCHAR2(128),
  usage_type VARCHAR2(11),
  asn VARCHAR2(6),
  "as" VARCHAR2(256),
  last_seen NUMBER(10),
  threat VARCHAR2(128),
  provider VARCHAR2(256),
  CONSTRAINT pk_ip_from_ip_to_ipv6 PRIMARY KEY (ip_from, ip_to)
);

CREATE OR REPLACE DIRECTORY ip_tmp AS 'C:\your_CSV_file_folder';
GRANT READ, WRITE ON DIRECTORY ip_tmp TO sys;

INSERT INTO ip2proxy_px11_ipv6(ip_from, ip_to, proxy_type, country_code, country_name, region_name, city_name, isp, domain, usage_type, asn, "as", last_seen, threat, provider)
SELECT LPAD(ip_from2, 39, '0'), LPAD(ip_to2, 39, '0'), proxy_type2, country_code2, country_name2, region_name2, city_name2, isp2, domain2, usage_type2, asn2, as2, last_seen2, threat2, provider2
FROM EXTERNAL (
  (ip_from2 VARCHAR2(39),
  ip_to2 VARCHAR2(39),
  proxy_type2 VARCHAR2(3),
  country_code2 CHAR(2),
  country_name2 VARCHAR2(64),
  region_name2 VARCHAR2(128),
  city_name2 VARCHAR2(128),
  isp2 VARCHAR2(256),
  domain2 VARCHAR2(128),
  usage_type2 VARCHAR2(11),
  asn2 VARCHAR2(6),
  as2 VARCHAR2(256),
  last_seen2 NUMBER(10),
  threat2 VARCHAR2(128),
  provider2 VARCHAR2(256))
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ip_tmp
ACCESS PARAMETERS (
  RECORDS DELIMITED BY 0x'0A'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
)
LOCATION ('IP2PROXY-IPV6-PROXYTYPE-COUNTRY-REGION-CITY-ISP-DOMAIN-USAGETYPE-ASN-LASTSEEN-THREAT-RESIDENTIAL-PROVIDER.CSV') REJECT LIMIT 0) px11_ipv6_external;

EXEC DBMS_STATS.GATHER_TABLE_STATS('sys','ip2proxy_px11_ipv6');

 

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 = 'sys';
  $pass = '12345';
  $host = 'localhost';
  $port = 1521;
  $service = 'XE';
  $tns = '
  (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ' . $host . ')(PORT = ' . $port . '))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = ' . $service . ')
    )
  )';
  
  if ($conn = oci_connect($user, $pass, $tns, 'AL32UTF8', OCI_SYSDBA)) {
    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);
    
    $stid = oci_parse($conn, 'SELECT * FROM ip2proxy_px11_ipv6 WHERE :ip_num >= ip_from AND :ip_num <= ip_to ORDER BY ip_from, ip_to FETCH FIRST 1 ROWS ONLY');
    oci_bind_by_name($stid, ':ip_num', $ipnum);
    
    oci_execute($stid);
    $num = oci_fetch_all($stid, $res, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
    
    oci_close($conn);
    
    if ($num > 0) {
      return $res[0];
    }
    else {
      die('No record found.' . "<br>\n");
    }
  }
  else {
    $err = oci_error();
    die('Error: ' . $err['message'] . "<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";
?>