CODEWITHSUNDEEP
sqlsql-server
Magpie
Magpie

Reputation: 7173

Perfecting SQL binary to IP Address string conversion

We use a binary(16) field to store IP Addresses. We do this as it can hold both IPv4 and IPv6 addresses and is easily used with the .Net IPAddress class.

However I have created the following SQL function to convert the binary address to the IP Address string for reporting purposes.

CREATE FUNCTION fn_ConvertBinaryIPAddressToString
(
    @binaryIP binary(16)
)
RETURNS nvarchar(39)
AS
BEGIN
    DECLARE @ipAsString nvarchar(39)

    -- Is IPv4
    IF (substring(@binaryIP, 5, 1) = 0x00)  <-- Is there a better way?
    BEGIN

        SELECT @ipAsString = CAST(CAST(substring(@binaryIP, 1, 1) AS int) AS nvarchar(3)) + '.' +
        CAST(CAST(substring(@binaryIP, 2, 1) AS int) AS nvarchar(3)) + '.' +
        CAST(CAST(substring(@binaryIP, 3, 1) AS int) AS nvarchar(3)) + '.' +
        CAST(CAST(substring(@binaryIP, 4, 1) AS int) AS nvarchar(3))

    END
    ELSE
    BEGIN
    -- Is IPv6
        -- taken and modified from http://support.microsoft.com/kb/104829
        DECLARE @i int
        DECLARE @length int
        DECLARE @hexstring char(16)

        SELECT @ipAsString = ''
        SELECT @i = 1
        SELECT @length = 16
        SELECT @hexstring = '0123456789ABCDEF'

        WHILE (@i <= @length)
        BEGIN
            DECLARE @tempint int
            DECLARE @firstint int
            DECLARE @secondint int

            SELECT @tempint = convert(int, substring(@binaryIP,@i,1))
            SELECT @firstint = floor(@tempint/16)
            SELECT @secondint = @tempint - (@firstint*16)

            SELECT @ipAsString = @ipAsString + substring(@hexstring, @firstint+1, 1) + substring(@hexstring, @secondint+1, 1)

            IF (@i % 2 = 0)
                SELECT @ipAsString = @ipAsString + ':'

            SELECT @i = @i + 1
       END
    END

    -- Return the result of the function
    RETURN @ipAsString

END
GO

At the moment if the 5 byte is 0 I assume it is an IPv4 address. Is this a safe assumption? Is it better to check all the remaining bytes for zeros or is there a better way?

EDIT removed unnecessary cast

Upvotes: 11

Views: 4265

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

I'd say you ought to be storing the address family also, either encoded in your byte array or as a separate column. If you pass 16 bytes to the IPAddress constructor, it will construct as IPv6 address, so you'd need checks in your code also to determine the address family. It would seem a lot easier (and not rely on assumptions about the bytes of an IPv6 address) to keep this information around directly.

Or use a varbinary(16), which you can then check the length of (length=4 => IPv4 address)

Upvotes: 5

Related Questions