Updated 12/30/14: Added support for extended format.
A Regional Internet registry (RIR) is responsible for managing allocation and registration of IP addresses. Additionally, each registry publishes a list of allocations with an assigned country code. I have created a database context using Entity Framework 5 which can interpret and store this data as well as look up the country code associated with an IP address.
How it Works
There are currently five RIRs across the world and to successfully look up the country code for any IP address, data from all sources must be utilized. Each RIR provides a line delimited and pipe separated list of IP allocations. Here is an example IPv4 and IPv6 allocation for LACNIC, the Latin America and Caribbean Network Information Centre for Brazil:
lacnic|BR|ipv4|177.0.0.0|262144|20101104|allocated lacnic|BR|ipv6|2001:1280::|32|20071219|allocated
Each entry provides crucial data that define a range of IP addresses assigned to a country. This data is interpreted to populate an SQL database. The above IPv4 entry defines a start IP address of 177.0.0.0 with a count of 262144 addresses. I decided to store IPv4 start and end values as integer data types, since both IPv4 and integers are 32-bit, making the conversion quick and simple.
Handling IPv6 data is slightly more complex. The IPv6 entry above defines a start IP address of 2001:1280:: with a value of 32 indicating the minimum bit to set to find the end address. These addresses require a separate table to contain 128-bit values and unfortunately, SQL only supports up to 64-bit bigint data types. To store the addresses, I chop the values in half to create two start and two end bigint data type columns for each entry.
Each line is read from the registry file, split and formatted in the LoadRegionalInternetRegistryFile method:
/// <summary>
/// Parses and loads a regional internet registry file into the database.
/// </summary>
/// <param name="registry">The regional internet registry to update in the database.</param>
/// <param name="path">The path to a regional internet registry file.</param>
private static void LoadRegionalInternetRegistryFile(RegionalInternetRegistry registry, string path)
{
using (DataTable ipv6Table = new DataTable("IPv4RIRData"))
using (DataColumn ipv6CountryParam = new DataColumn("CountryCode", typeof(string)))
using (DataTable ipv4Table = new DataTable("IPv4RIRData"))
using (DataColumn ipv4CountryParam = new DataColumn("CountryCode", typeof(string)))
{
//add ipv6 table columns
ipv6Table.Columns.Add("StartIP1", typeof(long));
ipv6Table.Columns.Add("StartIP2", typeof(long));
ipv6Table.Columns.Add("EndIP1", typeof(long));
ipv6Table.Columns.Add("EndIP2", typeof(long));
ipv6CountryParam.MaxLength = 2;
ipv6Table.Columns.Add(ipv6CountryParam);
//add ipv4 table columns
ipv4Table.Columns.Add("StartIP", typeof(int));
ipv4Table.Columns.Add("EndIP", typeof(int));
ipv4CountryParam.MaxLength = 2;
ipv4Table.Columns.Add(ipv4CountryParam);
//open the RIR file
using (StreamReader reader = new StreamReader(path))
{
for (; ; ) //continuous
{
//read each line
string line = reader.ReadLine();
if (line == null) //EOF
break;
//registry|cc|type|start|value|date|status
string[] split = line.Split('|');
int l = split.Length;
if (l != 7 && l != 8) //allow extended format
continue;
switch (split[6]) //status
{
case "allocated": //only take allocated data, the assigned data may contain duplicates
//number of ipv4 addresses or number of ipv6 bits
int num_end;
if (!int.TryParse(split[4], out num_end)) //value
continue;
switch (split[2].ToLower()) //type
{
case "ipv4": //accept IPv4 and IPv6 entries
case "ipv6":
IPAddress address;
//attempt to parse the ip address:
if (!IPAddress.TryParse(split[3], out address)) //start
continue;
//get ip as binary:
byte[] start = address.GetAddressBytes();
switch (address.AddressFamily)
{
case AddressFamily.InterNetwork:
num_end--;
if (BitConverter.IsLittleEndian)
Array.Reverse(start);
//convert into a signed value:
int startip = (int)(BitConverter.ToUInt32(start, 0) - int.MaxValue);
//add entry to table
ipv4Table.Rows.Add(startip, startip + num_end, split[1]);
break;
case AddressFamily.InterNetworkV6:
byte[] end = new byte[16];
Array.Copy(start, end, 16);
byte sub;
int index;
for (int i = 127; i >= num_end; i--) //set each bit
{
sub = (byte)(i % 8); //get which octet
index = (i - sub) / 8; //get which byte
end[index] |= (byte)(Math.Pow(2, 7 - sub)); //set flag
}
if (BitConverter.IsLittleEndian)
{
Array.Reverse(start, 0, 8);
Array.Reverse(start, 8, 8);
Array.Reverse(end, 0, 8);
Array.Reverse(end, 8, 8);
}
//add entry to table
ipv6Table.Rows.Add((long)(BitConverter.ToUInt64(start, 0) - long.MaxValue), (long)(BitConverter.ToUInt64(start, 8) - long.MaxValue), (long)(BitConverter.ToUInt64(end, 0) - long.MaxValue), (long)(BitConverter.ToUInt64(end, 8) - long.MaxValue), split[1]);
break;
}
break;
}
break;
}
}
}
InsertIPv4RIRData(registry.RegionalInternetRegistryID, ipv4Table);
InsertIPv6RIRData(registry.RegionalInternetRegistryID, ipv6Table);
}
}
This loads IPv4 and IPv6 data into separate DataTable instances.
Data tables are inserted into the database using SQL stored procedures that handle replacing IP ranges and country codes for an RIR:
CREATE PROCEDURE dbo.InsertIPv4RIRData @RIRID int, @RIRTable IPv4RIRTable readonly AS SET XACT_ABORT ON; BEGIN BEGIN TRANSACTION; DELETE FROM IPv4 WHERE RegionalInternetRegistry_RegionalInternetRegistryID=@RIRID; INSERT INTO IPv4 (RegionalInternetRegistry_RegionalInternetRegistryID, StartIP, EndIP, CountryCode) SELECT @RIRID, StartIP, EndIP, CountryCode FROM @RIRTable; COMMIT TRANSACTION; END
A specialized database initializer is responsible for seeding the database with the user-defined table types and stored procedures to quickly add a large number of records as well as preloading all Regional Internet Registry repository download locations.
To locate the country code for an IP address, LINQ statements are created that look for an entry with a valid range:
/// <summary>
/// Gets the country code for the specified address from the database.
/// </summary>
/// <param name="ipString">The address to look up.</param>
/// <returns>A two character country code or null.</returns>
private static string LookupCountryCodeFromDatabase(string ipString)
{
IPAddress address;
if (!IPAddress.TryParse(ipString, out address))
return null;
byte[] ipAddressBytes = address.GetAddressBytes();
switch (address.AddressFamily)
{
case AddressFamily.InterNetwork: //IPv4
{
if (BitConverter.IsLittleEndian)
Array.Reverse(ipAddressBytes);
//convert into a signed value:
int ip = (int)(BitConverter.ToUInt32(ipAddressBytes, 0) - int.MaxValue);
using (IPCountryCodeContext context = new IPCountryCodeContext())
//find the country code for an entry within the IP range
return (from a in context.IPv4
where a.StartIP <= ip && a.EndIP >= ip
select a.CountryCode).FirstOrDefault();
}
case AddressFamily.InterNetworkV6: //IPv6
{
if (BitConverter.IsLittleEndian)
{
Array.Reverse(ipAddressBytes, 0, 8);
Array.Reverse(ipAddressBytes, 8, 8);
}
//split the 128-bit value into two 64-bit signed values:
long ip1 = (long)(BitConverter.ToUInt64(ipAddressBytes, 0) - long.MaxValue);
long ip2 = (long)(BitConverter.ToUInt64(ipAddressBytes, 8) - long.MaxValue);
using (IPCountryCodeContext context = new IPCountryCodeContext())
//find the country code for an entry within the IP range
return (from a in context.IPv6
where (ip1 > a.StartIP1 || (ip1 == a.StartIP1 && ip2 >= a.StartIP2)) && (ip1 < a.EndIP1 || (ip1 == a.EndIP1 && ip2 <= a.EndIP2))
select a.CountryCode).FirstOrDefault();
}
}
return null;
}
To minimize trips to the database, I employ IP country code caching using a sliding expiration:
/// <summary>
/// Gets the country code for the specified address.
/// </summary>
/// <param name="ipString">The address to look up.</param>
/// <returns>A two character country code or null.</returns>
public static string LookupCountryCode(string ipString)
{
if (ipString == null)
return null;
//get cache time in milliseconds
string cacheTimeoutValue = ConfigurationManager.AppSettings["IPCountryCodeContext_IPAddressCacheTimeout"];
if (cacheTimeoutValue != null)
{
int cacheTimeout;
if (int.TryParse(cacheTimeoutValue, out cacheTimeout) && cacheTimeout > 0) //if caching is available
{
HttpContext context = HttpContext.Current; //get http context
if (context != null) //only cache with context
{
string cacheKey = NetCacheKey.CreateCacheKey("RegionalInternetRegistry", ipString); //create a cache key for the ip address
string countryCode = context.Cache[cacheKey] as string; //get any cached country code
if (countryCode == null)
{
countryCode = LookupCountryCodeFromDatabase(ipString); //lookup ip address
if (countryCode == null) //store empty string if no country code found
countryCode = string.Empty;
//add ip address to cache:
context.Cache.Add(cacheKey, countryCode, null, Cache.NoAbsoluteExpiration, TimeSpan.FromMilliseconds(cacheTimeout), CacheItemPriority.Low, null);
}
if (countryCode.Length == 0) //empty string from cache indicates no country code was found in the database
return null;
return countryCode;
}
}
}
return LookupCountryCodeFromDatabase(ipString); //lookup and return ip address
}
This allows for high performance IP to country code look ups that can be used for a variety of purposes.
Using
Using the IPCountryCodeContext requires amending the main web.Config with the connection string, application setting and Entity Framework database initializer shown below:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<connectionStrings>
<!-- defines the connection string for the IPCountryCodeContext -->
<add name="IPCountryCodeConnection" providerName="System.Data.SqlClient" connectionString="[your connection string]" />
</connectionStrings>
<appSettings>
<!-- defines the duration in milliseconds to cache an ip address's country code -->
<add key="IPCountryCodeContext_IPAddressCacheTimeout" value="300000"/>
</appSettings>
<entityFramework>
<contexts>
<context type="System.Data.Entity.IPCountryCodeContext, [your assembly]">
<!-- defines the database initializer for the IPCountryCodeContext -->
<databaseInitializer type="System.Web.Mvc.IPCountryCodeInitializer, [your assembly]"/>
</context>
</contexts>
</entityFramework>
</configuration>
To look up the country code for an IP address, use the static IPCountryCodeContext.LookupCountryCode method with an IPv4 or IPv6 value.
To download new RIR data and update the database, call the static IPCountryCodeContext.UpdateRegionalInternetRegistryData method. This call may take some time to complete, you may want to use a scheduled process to keep RIR data up to date.
