3

I'm looking for ways to handle IPv6 CIDR address ranges in SqlServer.

The data I obtained is formatted as bellow and has these columns :

Int_IP_Start, Int_IP_End, CIDR_Range, ASN, Name

for IPv4

"2868826112","2868826623","170.254.208.0/23","265116","Wave Net"
"2868827136","2868828159","170.254.212.0/22","265381","Furtado & Furtado Provedores LTDA"
"2868828928","2868829183","170.254.219.0/24","264770","Leonir Remussi (KDMNET)"
"2868829184","2868829439","170.254.220.0/24","265373","NET TELECOMUNICACOES LTDA - ME"
"2868829440","2868829695","170.254.221.0/24","265373","NET TELECOMUNICACOES LTDA - ME"
"2868830208","2868831231","170.254.224.0/22","265382","TELECOM LTDA ME"
"2868831232","2868831487","170.254.228.0/24","27951","Media Commerce Partners S.A"

And this is the same but for IPv6

"58568835385568506466387976054061924352","58568835464796668980652313647605874687","2c0f:f288::/32","328039","JSDAAV-ZA-Telecoms-AS"
"58568842991472107835764385034281156608","58568842991473316761583999663455862783","2c0f:f2e8::/48","37182","TERNET"
"58568844892948008178108487279335964672","58568844892949217103928101908510670847","2c0f:f300::/48","37153","Hetzner"
"58568847428249208634567290272742375424","58568847507477371148831627866286325759","2c0f:f320::/32","37126","BELL-TZ"
"58568849329725108976911392517797183488","58568849408953271491175730111341133823","2c0f:f338::/32","327983","Interworks-Wireless-Solutions"

The numerical reprensentation of a single IPv6 is quite large as the address space is the equivalent of a 128bit integer.

The goal here is to have a way for a database query to return wether or not an IP is part of one of the stored ranges that came from the CSV.

For IPv4 that's easy, you take the IP and convert it to it's INT32 equivalent.

Since there is no INT128 datatype in SqlServer I've got the following questions :

-Is there a way to create a custom type that would store a bigint of 128bits ? (Only need to perform Greater than or lesser than operations)

-Is there a way to properly handle IP address ranges in SqlServer ?

EDIT:

Here is the largest number an IPv6 address can represent :

    string LongestIp = "ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff";
    var SerializedIp = IPNetwork.ToBigInteger(IPAddress.Parse(LongestIp));
    Console.WriteLine(SerializedIp.ToString());

This c# code outputs 340282366920938463463374607431768211455

Trying to insert this

declare @ipv6Decimals table (Ip decimal(38,0));

insert into @ipv6Decimals (Ip) values (58568844892949217103928101908510670847); --this is okay,
--When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1
insert into @ipv6Decimals (Ip) values (99999999999999999999999999999999999999); --This is the largest numeric(38,0) will fit
insert into @ipv6Decimals (Ip) values (340282366920938463463374607431768211455);-- This is too large

Msg 1007, Level 15, State 1, Line 3 The number '340282366920938463463374607431768211455' is out of the range for numeric representation (maximum precision 38).

2

Is there a way to create a custom type that would store a bigint of 128bits ? (Only need to perform Greater than or lesser than operations)

You might not need a custom type - numeric(38,0) might be suitable here (though I don't know the full range for valid 128-bit integers or whether IPv6 might be constrained to the range of values that would fit):

CREATE TABLE #ips
(
  iplow numeric(38,0),
  iphigh numeric(38,0),
  c varchar(64),
  d int,
  e nvarchar(128)
);

INSERT #ips VALUES
(58568835385568506466387976054061924352,58568835464796668980652313647605874687,
    '2c0f:f288::/32',328039,N'JSDAAV-ZA-Telecoms-AS'),
(58568842991472107835764385034281156608,58568842991473316761583999663455862783,
    '2c0f:f2e8::/48',37182 ,N'TERNET'),
(58568844892948008178108487279335964672,58568844892949217103928101908510670847,
    '2c0f:f300::/48',37153 ,N'Hetzner'),
(58568847428249208634567290272742375424,58568847507477371148831627866286325759,
    '2c0f:f320::/32',37126 ,N'BELL-TZ'),
(58568849329725108976911392517797183488,58568849408953271491175730111341133823,
    '2c0f:f338::/32',327983,N'Interworks-Wireless-Solutions');

DECLARE @ip numeric(38,0) = 58568842991472107835764385034281156617;

SELECT iplow,iphigh,c,d,e
  FROM #ips 
  WHERE @ip BETWEEN iplow AND iphigh;

DROP TABLE #ips;

If this doesn't work for the addresses you need to support then you may need to separate out the network prefix (48 bits), subnet ID (16 bits), and interface ID (64 bits) into three separate numbers before passing them off to SQL Server. Your where clauses would become more complex but this might be a simpler approach than trying to represent an IPv6 as a single number anyway.

Is there a way to properly handle IP address ranges in SqlServer ?

SQL Server has no built-in capability to understand what an IP address is.

  • 2
    Unsigned 128bit integers have max value of 2^128-1 ~ 3.4 x 10^38 , so numeric(38,0) won't fit all the addresses, unless there's some reason the high-order bit is always 0. A normalized string representation or binary(16) should work, though. – David Browne - Microsoft Aug 21 '18 at 13:48
  • 1
    @DavidBrowne-Microsoft I knew there would be an issue. :-( How would you reliably get the value into a binary representation? As a string first it wouldn't work because of ranges like 99999 to 100001 for example. I suppose you'd have to get it into that format before ever handing it off to SQL Server. – Aaron Bertrand Aug 21 '18 at 13:51
  • 1
    Well, numeric(38,0) does work for the entire list of IP address ranges I've got for today. In it's current state IPv6 upper address ranges remain mainly unused. It'll be years before you start NEEDING FFFF:FFFF addresses. I'll live with numeric(38,0) for now until numeric(39,0) or some other solution for IPv6 handling comes around in a future version. – A_V Aug 21 '18 at 13:56
  • @a_v expanding an existing column where additional bytes need to be allocated I believe is a write intensive process and if you are doing that across the potential number of rows that accommodate 128bit, the query might complete when your grand children's great great great grand child retires. Whereas intra-byte allocations are extremely fast. It would not surprise me if Microsoft's Azure products in this space have a 128 bit integer data type that is not available to the public. – J Weezy Feb 25 at 21:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.