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

The aim of this guide is to demonstrate how to import IP2Proxy data (PX11 IPv6) in CSV form into DynamoDB 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.

Also remember to download the AWS PHP SDK into the same folder as your web page.

Important Note

We will not cover installation of PHP in this guide. We will assume you have already setup PHP on the localhost and are using PHP via Apache (also on the localhost). For this example, we are using an Amazon EC2 instance running Debian Linux.

More info can be found at the following URLs if you need assistance with installations:
PHP: http://php.net/manual/en/install.unix.debian.php

You can get the AWS PHP SDK and setup your AWS credentials.

We will be using the AWS PHP SDK to do mass import of CSV data into DynamoDB and then querying the data via PHP using the same SDK.

Importing the CSV data into DynamoDB

Create a new PHP file called import.php and paste the following code into it:

<?php
require('./aws.phar');

$db = 'PX11_IPV6';
$filename = 'IP2PROXY-IPV6-PROXYTYPE-COUNTRY-REGION-CITY-ISP-DOMAIN-USAGETYPE-ASN-LASTSEEN-THREAT-RESIDENTIAL-PROVIDER.CSV';

$itemsperbatch = 25; // limit of bulk import API
$padzero = 40; // length to pad with zeroes

date_default_timezone_set('UTC');

use Aws\DynamoDb\Exception\DynamoDbException;

$sdk = new Aws\Sdk([
	'region'   => 'YOUR_AWS_REGION',
	'version'  => 'latest'
]);

$client = $sdk->createDynamoDb();

do {
	$params['Limit'] = 2;
	if (isset($response)) {
		$params['ExclusiveStartTableName'] = $response['LastEvaluatedTableName'];
	}
	$response = $client->listTables($params); 
	
	foreach ($response['TableNames'] as $key => $value) {
		if ($value == $db) { // table already exists so must drop first
			echo "Deleting existing table\n";
			$result = $client->deleteTable(['TableName' => $db]);
		}
	}
}
while ($response['LastEvaluatedTableName']);

try {
	// waiting for existing table to be deleted
	do {
		$result = $client->describeTable(['TableName' => $db]);
		sleep(2);
	} while (true);
}
catch (DynamoDbException $e) {
	if (preg_match('/ResourceNotFoundException/', $e->getMessage()) !== 1) {
		die($e->getMessage() . "\n");
	}
}

$attributes = [];
$keys = [];

$attributes[] = ['AttributeName' => 'MY_ID', 'AttributeType' => 'S'];
$attributes[] = ['AttributeName' => 'IP_TO', 'AttributeType' => 'S'];
$keys[] = ['AttributeName' => 'MY_ID', 'KeyType' => 'HASH'];
$keys[] = ['AttributeName' => 'IP_TO', 'KeyType' => 'RANGE'];

$tablearr = ['TableName' => $db, 'AttributeDefinitions' => $attributes, 'KeySchema' => $keys, 'ProvisionedThroughput' => ['ReadCapacityUnits' => 5, 'WriteCapacityUnits' => 1000]];

echo "Creating table\n";
$result = $client->createTable($tablearr);

redocheck:
try {
	do {
		// wait for table to become active
		$result = $client->describeTable(['TableName' => $db]);
		sleep(2);
	} while ($result['Table']['TableStatus'] != 'ACTIVE');
	echo "Table created\n";
}
catch (DynamoDbException $e) {
	if (preg_match('/ResourceNotFoundException/', $e->getMessage()) === 1) {
		// table not ready yet
		sleep(2);
		goto redocheck;
	}
	else {
		die($e->getMessage() . "\n");
	}
}

$handle = fopen($filename, 'r');
$contents = '';
$dataarr = [];
$counter = 0;
while (!feof($handle)) {
	$line = fgets($handle, 8192);
	$dataarr[] = $line;
	
	if (count($dataarr) == $itemsperbatch) {
		if ($counter % 5000 == 0) {
			echo 'Importing row ' . $counter . "\n";
		}
		doImport($dataarr);
		$dataarr = []; //reset
	}
}
fclose($handle);
if (count($dataarr) > 0) {
	doImport($dataarr);
	$dataarr = []; //reset
}

function doImport($dataarr) {
	global $client;
	global $db;
	global $counter;
	global $padzero;
	
	$mainarr = [];
	foreach ($dataarr as $data) {
		$data = rtrim($data); // clear EOL
		
		if (preg_match('/^"[^"]+","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)","([^"]+)"$/', $data, $matches) == 1) {
			$itemarr = [];
			$attributearr = [];
			
			$counter++;
			$ipto = $matches[1];
			$attributearr['MY_ID'] = ['S' => $db]; // DynamoDB needs 2 different field names for hash key and range key
			$attributearr['IP_TO'] = ['S' => str_pad($ipto, $padzero, '0', STR_PAD_LEFT)];

			$proxytype = $matches[2];
			$attributearr['PROXY_TYPE'] = ['S' => $proxytype];
			$countrycode = $matches[3];
			$attributearr['COUNTRY_CODE'] = ['S' => $countrycode];
			$countryname = $matches[4];
			$attributearr['COUNTRY_NAME'] = ['S' => $countryname];
			$regionname = $matches[5];
			$attributearr['REGION_NAME'] = ['S' => $regionname];
			$cityname = $matches[6];
			$attributearr['CITY_NAME'] = ['S' => $cityname];
			$isp = $matches[7];
			$attributearr['ISP'] = ['S' => $isp];
			$domain = $matches[8];
			$attributearr['DOMAIN'] = ['S' => $domain];
			$usagetype = $matches[9];
			$attributearr['USAGE_TYPE'] = ['S' => $usagetype];
			$asn = $matches[10];
			$attributearr['ASN'] = ['S' => $asn];
			$as = $matches[11];
			$attributearr['AS'] = ['S' => $as];
			$lastseen = $matches[12];
			$attributearr['LAST_SEEN'] = ['S' => $lastseen];
			$threat = $matches[13];
			$attributearr['THREAT'] = ['S' => $threat];
			$provider = $matches[14];
			$attributearr['PROVIDER'] = ['S' => $provider];
			
			$itemarr = ['PutRequest' => ['Item' => $attributearr]];
			$mainarr[] = $itemarr;
		}
	}
	
	$finalarr = ['RequestItems' => [$db => $mainarr]];
	$retries = 1;
	dowrite:
	try {
		$result = $client->batchWriteItem($finalarr);
		
		// see if there are any items that couldn't be imported then we resubmit for import
		if (!empty($result['UnprocessedItems'])) {
			$finalarr = ['RequestItems' => $result['UnprocessedItems']];
			goto dowrite;
		}
	}
	catch (DynamoDbException $e) {
		if (preg_match('/(ProvisionedThroughputExceededException|Handshake timed out)/', $e->getMessage()) === 1) {
			sleep(2**$retries); // exponential retries
			echo "Retrying\n";
			$retries++;
			goto dowrite;
		}
		else {
			die($e->getMessage() . "\n");
		}
	}
}
?>

Run the PHP script by calling the below command in command prompt:
php import.php

Querying the IP2Proxy data from a PHP web page

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
require('./aws.phar');

$db = 'PX11_IPV6';

date_default_timezone_set('UTC');

use Aws\DynamoDb\Exception\DynamoDbException;

$sdk = new Aws\Sdk([
	'region'   => 'YOUR_AWS_REGION',
	'version'  => 'latest'
]);

$client = $sdk->createDynamoDb();

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

function queryIP2Proxy($myip) {
	global $db;
	global $client;
	
	$padzero = 40; // need to pad the ip numbers because DynamoDB is comparing as strings, not numbers
	
	// 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 = str_pad($ipnum, $padzero, '0', STR_PAD_LEFT);
	
	$request = [
		'TableName' => $db,
		'KeyConditions' => [
			'MY_ID' => [
				'ComparisonOperator' => 'EQ',
				'AttributeValueList' => [
					['S' => $db]
				]
			],
			'IP_TO' => [
				'ComparisonOperator' => 'GT',
				'AttributeValueList' => [
					['S' => $ipnum]
				]
			]
		],
		'AttributesToGet' => ['PROXY_TYPE', 'COUNTRY_CODE', 'COUNTRY_NAME', 'REGION_NAME', 'CITY_NAME', 'ISP', 'DOMAIN', 'USAGE_TYPE', 'ASN', 'AS', 'LAST_SEEN', 'THREAT', 'PROVIDER'],
		'ConsistentRead' => true,
		'Limit' => 1
	];
	
	$response = $client->query($request);
	
	$result = [];
	
	foreach ($response['Items'] as $key => $value) {
		$result['PROXY_TYPE'] = $value['PROXY_TYPE']['S'];
		$result['COUNTRY_CODE'] = $value['COUNTRY_CODE']['S'];
		$result['COUNTRY_NAME'] = $value['COUNTRY_NAME']['S'];
		$result['REGION_NAME'] = $value['REGION_NAME']['S'];
		$result['CITY_NAME'] = $value['CITY_NAME']['S'];
		$result['ISP'] = $value['ISP']['S'];
		$result['DOMAIN'] = $value['DOMAIN']['S'];
		$result['USAGE_TYPE'] = $value['USAGE_TYPE']['S'];
		$result['ASN'] = $value['ASN']['S'];
		$result['AS'] = $value['AS']['S'];
		$result['LAST_SEEN'] = $value['LAST_SEEN']['S'];
		$result['THREAT'] = $value['THREAT']['S'];
		$result['PROVIDER'] = $value['PROVIDER']['S'];
	}
	
	return $result;
}

//test IP
$ip = '8.8.8.8';

$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";
echo 'PROVIDER: ' . $myresult['PROVIDER'] . "<br>\n";
?>

Articles & Tutorials

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

Important Note

Importing the CSV data into DynamoDB

Querying the IP2Proxy data from a PHP web page

Was this article helpful?

Articles & Tutorials

Important Note

Importing the CSV data into DynamoDB

Querying the IP2Proxy data from a PHP web page

Was this article helpful?

Related Articles