IP database from ip2location and convert IPV4 to IPV6 in Perl

Question

I am trying to use IP to country database from ip2location.

My question is, does the IPV6 database files already contains the IPV4 address embedded or do I have to use both IPV4 and IPV6 databases to cover all the IP Ranges for the internet for all versions.

What I mean if I want to support both IPV4 and IPV6 I should load both database files into the same mysql table or should I just use IPV6.

I am talking about the file IP2LOCATION-LITE-DB11.CSV.ZIP and IP2LOCATION-LITE-DB11.IPV6.CSV.ZIP

Answer 1

I am answering my own question after two days of search and testing. The IPV4 can be embedded in IPV6 and this file IP2LOCATION-LITE-DB11.IPV6.CSV.ZIP contains the IPV4 embedded as IPV6 and all IPs are stored as decimal(39, 0).

The trick I used here to use the IPV6 database file for both versions of IP's IPV4 and IPV6 is to convert the IPV4 address when searching the database to IPV6 then to Integer and do the search normal. This way your application supports both IPV4 and IPV6.

To convert IPV4 to IPV6, this wiki article is the answer:

http://en.wikipedia.org/wiki/IPv6#IPv4-mapped_IPv6_addresses

IPv4-mapped IPv6 addresses

Hybrid dual-stack IPv6/IPv4 implementations recognize a special class of addresses,
the IPv4-mapped IPv6 addresses. In these addresses, the first 80 bits are zero, the 
next 16 bits are one, and the remaining 32 bits are the IPv4 address. One may see these
addresses with the first 96 bits written in the standard IPv6 format, and the 
remaining 32 bits written in the customary dot-decimal notation of IPv4. For example,
::ffff:192.0.2.128 represents the IPv4 address 192.0.2.128. A deprecated format for
IPv4-compatible IPv6 addresses was ::192.0.2.128

and this article also good on this issue:

IPv6/IPv4 Address Embedding

All you need to do is prepend the IP with ::ffff: so the IP address 192.0.2.128 will be ::ffff:192.0.2.128 as valid IPV6.

The next step is to convert your IP either IPV4 or IPV6 to Decimail (39,0) and you can now search the database normal.

Since I use Perl, here are some helping code for testing and clearing.

use Net::IP ':PROC';

# IPV4 address
my $ipaddress = '197.36.107.146';

my $ip = new Net::IP ($ipaddress) or die (Net::IP::Error());
print ("IPV4  : ".$ip->ip()."\n");
print ("IPV4 Integer : ".$ip->intip()."\n");
print ("Version  : ".$ip->version()."\n");
print ("Size: ".$ip->size()."\n");
print ("Len : ".$ip->prefixlen()."\n");
print ("Type: ".$ip->iptype()."\n");
print "\n";

# Convert IPV4 to IPV6. Just prepend ipv4 with '::ffff:'
my $ip = new Net::IP ("::ffff:".$ipaddress) or die (Net::IP::Error());
print ("IPV6  : ".$ip->ip()."\n");
print ("IPV6 Integer : ".$ip->intip()."\n");
print ("Version  : ".$ip->version()."\n");
print ("Size: ".$ip->size()."\n");
print ("Len : ".$ip->prefixlen()."\n");
print ("Type: ".$ip->iptype()."\n");
print "\n";

# detect the user ip address and convert it

my $user_ip = get_user_ip();
$user_ip ||= $ipaddress; # just for testing on command line
print "Detected User IP address: $user_ip\n";

# if user remote address is IPV4 convert it to IPV6
if ($user_ip =~ /\./) {
    # Convert IPV4 to IPV6
    $user_ip = Net::IP->new("::ffff:$user_ip");
    # Now convert it to Integer
    $user_ip = $user_ip->intip();
}
else {
    # Already IPV6, just convert to Integer
    $user_ip = Net::IP->new($user_ip);
    $user_ip = $user_ip->intip();
}

print "User IP address in IPV6 format: $user_ip\n";
#----------------------------------
# Now you can search the geo database with IPV4 and IPV6 stored as decimails
# select * from ip_country where $ipaddress<=ip_to limit 1
#----------------------------------
sub get_user_ip {
    foreach (qw(REMOTE_ADDR HTTP_CLIENT_IP HTTP_X_FORWARDED_FOR HTTP_X_FORWARDED HTTP_X_CLUSTER_CLIENT_IP
               HTTP_FORWARDED_FOR HTTP_FORWARDED)) {
        if ($ENV{$_}) {
            return $ENV{$_};
        }
    }
}

and this is the output of this testing code:

IPV4  : 197.36.107.146
IPV4 Integer : 3307498386
Version  : 4
Size: 1
Len : 32
Type: PUBLIC

IPV6  : 0000:0000:0000:0000:0000:ffff:c524:6b92
IPV6 Integer : 281473989241746
Version  : 6
Size: 1
Len : 128
Type: IPV4MAP

Detected User IP address: 197.36.107.146
User IP address in IPV6 format: 281473989241746

Searching the database until now shows everything working normal.