IP queries allows you to discover the geographical location of a specific IP address. Businesses use IP queries to enhance cybersecurity measures by monitoring and analyzing network traffic. As users of IP2Location CSV data files know, we offer both IPv4 CSV files and also IPv6 CSV files for geolocation data. The IPv4 CSV files contain only the IPv4 address ranges while the IPv6 files actually contains both IPv4 and IPv6 ranges. However, users need to be aware that the IPv4 ranges inside the IPv6 CSV files are in IPv4-mapped IPv6 format.
So, in this article, we’ll demonstrate how to convert the IPv4 address to the IPv4-mapped IPv6 form. We’ll also show how to create the IPv6 table in MySQL, import the CSV data then query using PHP.
NOTE: Included among the IPv6 ranges are special IPv6 ranges used by 6to4 and Teredo tunnels. These tunnels serve to allow IPv4 networks to communicate with IPv6 networks. Our PHP example will show the conversion for these IPv6 addresses to their equivalent IPv4 addresses. By getting the actual IPv4 client addresses, you’ll be able to query the geolocation for the clients vs. that of the tunnels.
Before we proceed with our coding example, please familiarize yourselves with the below:
Download the DB11 LITE IPv6 CSV file
Our example is using the DB11 LITE IPv6 CSV file, but feel free to modify the steps to whichever IPv6 CSV file you may be using.
After you’ve downloaded the IP2LOCATION-LITE-DB11.IPV6.CSV.zip file, extract the IP2LOCATION-LITE-DB11.IPV6.CSV file.
Let’s create our DB11 IPv6 table in MySQL
Run the below SQL to create the table.
CREATE DATABASE ip2location; USE ip2location; CREATE TABLE `ip2location_db11_ipv6`( `ip_from` DECIMAL(39,0), `ip_to` DECIMAL(39,0), `country_code` CHAR(2), `country_name` VARCHAR(64), `region_name` VARCHAR(128), `city_name` VARCHAR(128), `latitude` DOUBLE, `longitude` DOUBLE, `zip_code` VARCHAR(30), `time_zone` VARCHAR(8), PRIMARY KEY (`ip_to`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Now, let’s import that CSV data into the table
Run the SQL below to perform the import.
LOAD DATA LOCAL INFILE '/myfolder/IP2LOCATION-LITE-DB11.IPV6.CSV' INTO TABLE `ip2location_db11_ipv6` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
Converting IPv4 address to IP number in PHP
It is relatively straightforward to convert an IPv4 address into its IP number. Just take the sample code below and save it as testipv4.php.
<?php
$ip = '8.8.8.8';
if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4)) {
$ipNumber = sprintf('%u', ip2long($ip));
echo $ip . ' is ' . $ipNumber . "\n";
}
?>
Run the code and you’ll see the IP number being displayed.
php testipv4.php
You’ll see the result as shown below.
8.8.8.8 is 134744072
How to calculate the IP number for IPv6 cases?
For Teredo and 6to4, we need to extract out the IPv4 address that’s embedded inside the Teredo or 6to4 IPv6 address. Then, we append “::ffff:” to the IPv4 to convert it into IPv4-mapped IPv6 before we calculate the IP number. The below code will handle IPv4-mapped IPv6, Teredo, 6to4 and normal IPv6 calculations into the IP number. Save the code as testipv6.php.
<?php
// IPv4-mapped IPv6
$ip = '::ffff:8.8.8.8';
echo $ip . ' is ' . getipnum($ip) . "\n";
// Teredo
$ip = '2001:0000:4136:e378:8000:63bf:3fff:fdd2';
echo $ip . ' is ' . getipnum($ip) . "\n";
// 6to4
$ip = '2002:0C9B:A665:0001:0000:0000:0C9B:A665';
echo $ip . ' is ' . getipnum($ip) . "\n";
// Normal IPv6
$ip = '2600:1f18:45b0:5b00:f5d8:4183:7710:ceec';
echo $ip . ' is ' . getipnum($ip) . "\n";
function getipnum($ip) {
if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV6)) {
// IPv4-mapped IPv6
if (preg_match('/^\:\:ffff\:\d+\.\d+\.\d+\.\d+$/i', $ip)) {
return ip62long($ip);
}
else {
// Expand IPv6
$ip = implode(':', str_split(unpack('H*0', inet_pton($ip))[0], 4));
// 6to4 Address - 2002::/16
if (substr($ip, 0, 4) == '2002') {
return ip62long('::ffff:' . substr($ip, 5, 9));
}
// Teredo Address - 2001:0::/32
elseif (substr($ip, 0, 9) == '2001:0000') {
return ip62long('::ffff:' . long2ip(~hexdec(str_replace(':', '', substr($ip, -9)))));
}
// IPv4 in IPv6 format
elseif (substr($ip, 0, 30) == '0000:0000:0000:0000:0000:ffff:') {
return ip62long('::ffff:' . long2ip(hexdec(str_replace(':', '', substr($ip, 30)))));
}
// Normal IPv6 Address
else {
return ip62long($ip);
}
}
}
else {
return 0;
}
}
function ip62long($ipv6) {
return (string) gmp_import(inet_pton($ipv6));
}
?>
Run the code.
php testipv6.php
See the results below.
::ffff:8.8.8.8 is 281470816487432 2001:0000:4136:e378:8000:63bf:3fff:fdd2 is 281473902969389 2002:0C9B:A665:0001:0000:0000:0C9B:A665 is 281470893270629 2600:1f18:45b0:5b00:f5d8:4183:7710:ceec is 50511294517568083089461819682126352108
Try to query geolocation for a simple IPv4 address 8.8.8.8
Convert the IPv4 address 8.8.8.8 into its IP number which is 134744072. Then, we try to query the IP geolocation from the table above using the SELECT statement below.
select * from `ip2location_db11_ipv6` where 134744072 <= `ip_to` limit 1;
You’ll notice that there is no geolocation data found. This is because the IPv4 address should be in the IPv4-mapped IPv6 form before conversion into IP number.
So 8.8.8.8 should be ::ffff:8.8.8.8 instead which gives an IP number of 281470816487432. The modified SELECT statement will now look like the below.
select * from `ip2location_db11_ipv6` where 281470816487432 <= `ip_to` limit 1;
Now, you can see the data.
NOTE: If you don’t know how to convert IP address to IP number, please read our FAQ page. If you want to verify if your IP address to IP number conversion is correct, you use the conversion page at https://www.ipaddressguide.com/ipv6-to-decimal and see if your results tally.
How do you query an IPv6 address?
For any IPv6 address, you can query in a similar fashion to the IPv4-mapped IPv6 query above. If you use the PHP calculation code in testipv6.php file, it will give you the IP number necessary for the queries.
E.g., for 2600:1f18:45b0:5b00:f5d8:4183:7710:ceec, we will get 50511294517568083089461819682126352108 as its IP number. We just modify the above query and plug in this new IP number and we get the SELECT statement below.
select * from `ip2location_db11_ipv6` where 50511294517568083089461819682126352108 <= `ip_to` limit 1;
Conclusion
As long as an IPv4 address is in IPv4-mapped IPv6 form before being converted into IP number, it should be able to return a result in the IPv6 data SELECT statement. With regards to Teredo and 6to4, it is also good to know how to get the IP number for their embedded IPv4 addresses. Using the PHP code above, it is that easy to convert an IP address to IP number to query the DB11 IPv6 table.