What am I doing wrong in calculating cidr IP range?

Question

I am trying to write a function that will output some address information on a CIDR formatted IP (output underneath code):

create function dbo.ConvertIpToInt (@Ip as varchar(15))
    returns bigint
    as
    begin
        return (convert(bigint, parsename(@Ip, 1)) +
                convert(bigint, parsename(@Ip, 2)) * 256 +
                convert(bigint, parsename(@Ip, 3)) * 256 * 256 +
                convert(bigint, parsename(@Ip, 4)) * 256 * 256 * 256)
    end
go


create function dbo.ConvertIntToIp (@Int bigint) 
    returns varchar(15)
    as 
    begin
        declare
             @IpHex     varchar(8)
            ,@IpDotted  varchar(15)

        select
             @IpHex = substring(convert(varchar(30), master.dbo.fn_varbintohexstr(@Int)), 11, 8)

        select
            @IpDotted = convert(varchar(3), convert(int, (convert(varbinary, substring(@IpHex, 1, 2), 2)))) + '.' +
                        convert(varchar(3), convert(int, (convert(varbinary, substring(@IpHex, 3, 2), 2)))) + '.' +
                        convert(varchar(3), convert(int, (convert(varbinary, substring(@IpHex, 5, 2), 2)))) + '.' +
                        convert(varchar(3), convert(int, (convert(varbinary, substring(@IpHex, 7, 2), 2))))
        return @IpDotted
    end
go


create function dbo.GetCidrIpRange (@CidrIp varchar(15))
    returns @result table
    (
        CidrIp      varchar(15) not null,
        Mask        int not null,
        LowRange    varchar(15) not null,
        LowIp       varchar(15) not null,
        HighRange   varchar(15) not null,
        HighIp      varchar(15) not null,
        AddressQty  bigint not null
    )
    as
    begin
        declare @Base       bigint  = cast(4294967295 as bigint)
        declare @Mask       int     = cast(substring(@CidrIp, patindex('%/%' , @CidrIP) + 1, 2) as int)
        declare @Power      bigint  = Power(2.0, 32.0 - @Mask) - 1
        declare @LowRange   bigint  = dbo.ConvertIpToInt(left(@CidrIp, patindex('%/%' , @CidrIp) - 1)) & (@Base ^ @Power)
        declare @HighRange  bigint  = @LowRange + @Power

        insert @result
        select
              CidrIp     = @CidrIp
            , Mask       = @Mask
            , LowRange   = @LowRange
            , LowIp      = dbo.ConvertIntToIp(@LowRange)
            , HighRange  = @HighRange
            , HighIp      = dbo.ConvertIntToIp(@HighRange)
            , AddressQty = convert(bigint, power(2.0, (32.0 - @Mask)))
        return
    end
go

select * from dbo.GetCidrIpRange('195.65.254.11/2');

This outputs the following:

CidrIp           Mask   LowRange    LowIp      HighRange   HighIp           AddressQty
--------------------------------------------------------------------------------------
195.65.254.11/2  2      3221225472  192.0.0.0  4294967295  255.255.255.255  1073741824

I have been browsing SO and Google for some hours now, and I am quite convinced that ConvertIpToInt and ConvertIntToIp are correct.

However, I was expecting the following output:

CidrIp           Mask   LowRange    LowIp         HighRange   HighIp          AddressQty
--------------------------------------------------------------------------------------
195.65.254.11/2  2      3275881985  195.65.254.1  3275882238  195.65.254.254  254

Can someone please point me out where the mistake in my code is? I've been staring myself blind and I don't see it (or I am misunderstanding how to do this).

Answer 1

According to both http://www.ipaddressguide.com/cidr and http://jodies.de/ipcalc?host=195.65.254.11&mask1=2&mask2=, your calculations are correct. The only disagreement between those two sites is that the jodies.de/ipcalc page removes the lowest and highest (broadcast) IP addresses from the range.

I tested with both 195.65.254.11/2 and 195.65.254.11/24. In order to get your code working, I needed to change the input parameter specification on dbo.GetCidrIpRang to be VARCHAR(20) (as mentioned by @Damien_The_Unbeliever in a comment on the question).

Two notes regarding performance:

1. For the ConvertIpToInt and ConvertIntToIp Scalar UDFs you might be better off using the INET_AddressToNumber and INET_NumberToAddress functions, respectively, that are included in the Free version of the SQL# SQLCLR library (which I wrote, but hey, Free :). The reason for this recommendation is that unlike T-SQL UDFs, deterministic SQLCLR UDFs (and these two are) do not prevent parallel plans.

2. If you don't want to go the SQLCLR route, then you should, at the very least, keep the ConvertIntToIp function as purely mathematical. There is no reason to do all of those conversions and substrings.

   CREATE FUNCTION dbo.IPNumberToAddress(@IPNumber BIGINT)
   RETURNS VARCHAR(15)
   WITH SCHEMABINDING
   AS
   BEGIN
       DECLARE @Oct1 BIGINT,
              @Oct2 INT,
              @Oct3 INT;
   
       SET @Oct1 = @IPNumber / (256 * 256 * 256);
       SET @IPNumber -= (@Oct1 * (256 * 256 * 256));
   
       SET @Oct2 = @IPNumber / (256 * 256);
       SET @IPNumber -= (@Oct2 * (256 * 256));
   
       SET @Oct3 = @IPNumber / 256;
       SET @IPNumber -= (@Oct3 * 256);
   
       RETURN CONCAT(@Oct1, '.', @Oct2, '.', @Oct3, '.', @IPNumber);
   END;
   GO
   

   And then:

   SELECT dbo.IPNumberToAddress(3275881995);
   -- 195.65.254.11
   

3. For the GetCidrIpRange TVF, you would be better off converting that to be an Inline TVF. You can accomplish the multi-step calculations via CTEs in the following manner (you will just need to clean it up a little / finish it):

   WITH cte1 AS
   (
       SELECT 2 AS [Mask] -- replace with real formula
   ), cte2 AS
   (
       SELECT 999 AS [Base], -- replace with real formula
              POWER(2.0, 32.0 - cte1.[Mask]) - 1 AS [Power],
           cte1.[Mask]
       FROM   cte1
   ), cte3 AS
   (
       SELECT SQL#.INET_AddressToNumber(left(@CidrIp, PATINDEX('%/%' , @CidrIp) - 1))
           & (cte2.[Base] ^ cte2.[Power]) AS [LowRange],
           cte2.[Power],
           cte2.[Mask]
       FROM   cte2
   )
   SELECT @CidrIp AS [CidrIp],
           cte3.[Mask],
           cte3.[LowRange],
           SQL#.INET_NumberToAddress(cte3.[LowRange]) AS [LowIp],
           (cte3.[LowRange] + cte3.[Power]) AS [HighRange],
           SQL#.INET_NumberToAddress(cte3.[LowRange] + cte3.[Power]) AS [HighIp],
           CONVERT(BIGINT, POWER(2.0, (32.0 - cte3.[Mask]))) AS [AddressQty]
   FROM   cte3 c;