sql-server - imagen - tipo de dato para guardar un archivo en sql server

Tipo de datos para almacenar direcciones IP en SQL Server

Question

¿Qué tipo de datos debo elegir para almacenar una dirección IP en un servidor SQL?

Al seleccionar el tipo de datos correcto, ¿sería bastante fácil filtrar por dirección IP?

Answer 1

Aquí hay un código para convertir IPV4 o IPv6 en formato varchar a binario (16) y viceversa. Esta es la forma más pequeña que se me ocurre. Debe indexar bien y proporcionar una forma relativamente fácil de filtrar subredes. Requiere SQL Server 2005 o posterior. No estoy seguro de que sea totalmente a prueba de balas. Espero que esto ayude.

-- SELECT dbo.fn_ConvertIpAddressToBinary(''2002:1ff:6c2::1ff:6c2'')
-- SELECT dbo.fn_ConvertIpAddressToBinary(''10.4.46.2'')
-- SELECT dbo.fn_ConvertIpAddressToBinary(''bogus'')

ALTER FUNCTION dbo.fn_ConvertIpAddressToBinary
(
     @ipAddress VARCHAR(39)
)
RETURNS BINARY(16) AS
BEGIN
DECLARE
     @bytes BINARY(16), @vbytes VARBINARY(16), @vbzone VARBINARY(2)
     , @colIndex TINYINT, @prevColIndex TINYINT, @parts TINYINT, @limit TINYINT
     , @delim CHAR(1), @token VARCHAR(4), @zone VARCHAR(4)

SELECT
     @delim = ''.''
     , @prevColIndex = 0
     , @limit = 4
     , @vbytes = 0x
     , @parts = 0
     , @colIndex = CHARINDEX(@delim, @ipAddress)

IF @colIndex = 0
     BEGIN
           SELECT
                @delim = '':''
                , @limit = 8
                , @colIndex = CHARINDEX(@delim, @ipAddress)
           WHILE @colIndex > 0
                SELECT
                      @parts = @parts + 1
                      , @colIndex = CHARINDEX(@delim, @ipAddress, @colIndex + 1)
           SET @colIndex = CHARINDEX(@delim, @ipAddress)

           IF @colIndex = 0
                RETURN NULL     
     END

SET @ipAddress = @ipAddress + @delim

WHILE @colIndex > 0
     BEGIN
           SET @token = SUBSTRING(@ipAddress, @prevColIndex + 1, @Colindex - @prevColIndex - 1)

           IF @delim = '':''
                BEGIN
                      SET  @zone = RIGHT(''0000'' + @token, 4)

                      SELECT
                           @vbzone = CAST('''' AS XML).value(''xs:hexBinary(sql:variable("@zone"))'', ''varbinary(2)'')
                           , @vbytes = @vbytes + @vbzone

                      IF @token = ''''
                           WHILE @parts + 1 < @limit
                                 SELECT
                                      @vbytes = @vbytes + @vbzone
                                      , @parts = @parts + 1
                END
           ELSE
                BEGIN
                      SET @zone = SUBSTRING('''' + master.sys.fn_varbintohexstr(CAST(@token AS TINYINT)), 3, 2)

                      SELECT
                           @vbzone = CAST('''' AS XML).value(''xs:hexBinary(sql:variable("@zone"))'', ''varbinary(1)'')
                           , @vbytes = @vbytes + @vbzone
                END

           SELECT
                @prevColIndex = @colIndex
                , @colIndex = CHARINDEX(@delim, @ipAddress, @colIndex + 1) 
     END            

SET @bytes =
     CASE @delim
           WHEN '':'' THEN @vbytes
           ELSE 0x000000000000000000000000 + @vbytes
     END 

RETURN @bytes

END

-- SELECT dbo.fn_ConvertBinaryToIpAddress(0x200201FF06C200000000000001FF06C2)
-- SELECT dbo.fn_ConvertBinaryToIpAddress(0x0000000000000000000000000A0118FF)

ALTER FUNCTION [dbo].[fn_ConvertBinaryToIpAddress]
(
     @bytes BINARY(16)
)
RETURNS VARCHAR(39) AS
BEGIN
DECLARE
     @part VARBINARY(2)
     , @colIndex TINYINT
     , @ipAddress VARCHAR(39)

SET @ipAddress = ''''

IF SUBSTRING(@bytes, 1, 12) = 0x000000000000000000000000
     BEGIN
           SET @colIndex = 13
           WHILE @colIndex <= 16
                SELECT
                      @part = SUBSTRING(@bytes, @colIndex, 1)
                      , @ipAddress = @ipAddress
                           + CAST(CAST(@part AS TINYINT) AS VARCHAR(3))
                           + CASE @colIndex WHEN 16 THEN '''' ELSE ''.'' END
                      , @colIndex = @colIndex + 1

           IF @ipAddress = ''0.0.0.1''
                SET @ipAddress = ''::1''
     END
ELSE
     BEGIN
           SET @colIndex = 1
           WHILE @colIndex <= 16
                BEGIN
                      SET @part = SUBSTRING(@bytes, @colIndex, 2)
                      SELECT
                           @ipAddress = @ipAddress
                                 + CAST('''' as xml).value(''xs:hexBinary(sql:variable("@part") )'', ''varchar(4)'')
                                 + CASE @colIndex WHEN 15 THEN '''' ELSE '':'' END
                           , @colIndex = @colIndex + 2
                END
     END

RETURN @ipAddress   

END 

Answer 2

Como quiero manejar tanto IPv4 como IPv6 , estoy usando VARBINARY(16) y las siguientes funciones SQL CLR para convertir la presentación de la dirección IP de text a bytes y viceversa:

[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlBytes GetIPAddressBytesFromString (SqlString value)
{
    IPAddress IP;

    if (IPAddress.TryParse(value.Value, out IP))
    {
        return new SqlBytes(IP.GetAddressBytes());
    }
    else
    {
        return new SqlBytes();
    }
}


[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlString GetIPAddressStringFromBytes(SqlBytes value)
{
    string output;

    if (value.IsNull)
    {
        output = "";
    }
    else
    {
        IPAddress IP = new IPAddress(value.Value);
        output = IP.ToString();
    }

    return new SqlString(output);
}

Answer 3

Estoy usando Varchar (15) hasta ahora todo está funcionando para mí. Insertar, actualizar, seleccionar. Acabo de iniciar una aplicación que tiene direcciones IP, aunque todavía no he hecho demasiados trabajos de desarrollo.

Aquí está la declaración selecta:

Seleccione * De dbo.Servidor donde [IP] = (''132.46.151.181'') Vaya

Answer 4

Gracias RBarry. Estoy armando un sistema de asignación de bloques IP y el almacenamiento como binario es el único camino a seguir.

Estoy almacenando la representación CIDR (por ejemplo, 192.168.1.0/24) del bloque IP en un campo varchar, y usando 2 campos calculados para mantener la forma binaria del inicio y el final del bloque. A partir de ahí, puedo ejecutar consultas rápidas para ver si un bloque determinado ya se ha asignado o es libre de asignar.

Modifiqué tu función para calcular la dirección IP final así:

CREATE FUNCTION dbo.fnDisplayIPv4End(@block AS VARCHAR(18)) RETURNS BINARY(4)
AS
BEGIN
    DECLARE @bin AS BINARY(4)
    DECLARE @ip AS VARCHAR(15)
    DECLARE @size AS INT

    SELECT @ip = Left(@block, Len(@block)-3)
    SELECT @size = Right(@block, 2)

    SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))

    SELECT @bin = CAST(@bin + POWER(2, 32-@size) AS BINARY(4))
    RETURN @bin
END;
go

Answer 5

La forma técnicamente correcta de almacenar IPv4 es Binario (4), ya que eso es lo que realmente es (no, ni siquiera un INT32 / INT (4)), la forma textual numérica que todos conocemos y amamos (255.255.255.255) siendo solo la conversión de visualización de su contenido binario.

Si lo hace de esta manera, querrá que las funciones se conviertan hacia y desde el formato de visualización de texto:

A continuación, se explica cómo convertir el formulario de visualización de texto a binario:

CREATE FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4)
AS
BEGIN
    DECLARE @bin AS BINARY(4)

    SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))

    RETURN @bin
END
go

Y aquí está cómo convertir el binario a la forma de visualización de texto:

CREATE FUNCTION dbo.fnDisplayIPv4(@ip AS BINARY(4)) RETURNS VARCHAR(15)
AS
BEGIN
    DECLARE @str AS VARCHAR(15) 

    SELECT @str = CAST( CAST( SUBSTRING( @ip, 1, 1) AS INTEGER) AS VARCHAR(3) ) + ''.''
                + CAST( CAST( SUBSTRING( @ip, 2, 1) AS INTEGER) AS VARCHAR(3) ) + ''.''
                + CAST( CAST( SUBSTRING( @ip, 3, 1) AS INTEGER) AS VARCHAR(3) ) + ''.''
                + CAST( CAST( SUBSTRING( @ip, 4, 1) AS INTEGER) AS VARCHAR(3) );

    RETURN @str
END;
go

Aquí hay una demostración de cómo usarlos:

SELECT dbo.fnBinaryIPv4(''192.65.68.201'')
--should return 0xC04144C9
go

SELECT dbo.fnDisplayIPv4( 0xC04144C9 )
-- should return ''192.65.68.201''
go

Finalmente, al hacer búsquedas y comparar, siempre use la forma binaria si desea poder aprovechar sus índices.

ACTUALIZAR:

Quería agregar que una forma de abordar los problemas de rendimiento inherentes de las UDF escalares en SQL Server, pero aún conservar la reutilización de código de una función es usar una función de valor de tabla en línea (iTVF). Así es como la primera función anterior (de cadena a binaria) se puede volver a escribir como un iTVF:

CREATE FUNCTION dbo.itvfBinaryIPv4(@ip AS VARCHAR(15)) RETURNS TABLE
AS RETURN (
    SELECT CAST(
               CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
            +  CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
            +  CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
            +  CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))
                AS BINARY(4)) As bin
        )
go

Aquí está en el ejemplo:

SELECT bin FROM dbo.fnBinaryIPv4(''192.65.68.201'')
--should return 0xC04144C9
go

Y así es como lo usarías en un INSERT

INSERT INTo myIpTable
SELECT {other_column_values,...},
       (SELECT bin FROM dbo.itvfBinaryIPv4(''192.65.68.201''))

Answer 6

Me gustan las funciones de SandRock. Pero encontré un error en el código de dbo.fn_ConvertIpAddressToBinary . El parámetro entrante de @ipAddress VARCHAR (39) es demasiado pequeño cuando concat la @delim a él.

SET @ipAddress = @ipAddress + @delim

Puede aumentarlo a 40. O mejor aún, usar una nueva variable que sea más grande y usarla internamente. De esta forma no perderás el último par en números grandes.

SELECT dbo.fn_ConvertIpAddressToBinary(''ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff'')

Answer 7

Para las personas que usan .NET pueden usar la clase IPAddress para analizar la cadena IPv4 / IPv6 y almacenarla como un VARBINARY(16) . Puede usar la misma clase para convertir byte[] en cadena. Si quieres convertir el VARBINARY en SQL:

--SELECT 
--  dbo.varbinaryToIpString(CAST(0x7F000001 AS VARBINARY(4))) IPv4,
--  dbo.varbinaryToIpString(CAST(0x20010DB885A3000000008A2E03707334 AS VARBINARY(16))) IPv6

--ALTER 
CREATE
FUNCTION dbo.varbinaryToIpString
(
    @varbinaryValue VARBINARY(16)
)
RETURNS VARCHAR(39)
AS
BEGIN
    IF @varbinaryValue IS NULL
        RETURN NULL
    IF DATALENGTH(@varbinaryValue) = 4
    BEGIN
        RETURN 
            CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 1, 1))) + ''.'' +
            CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 2, 1))) + ''.'' +
            CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 3, 1))) + ''.'' +
            CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 4, 1)))
    END
    IF DATALENGTH(@varbinaryValue) = 16
    BEGIN
        RETURN 
            sys.fn_varbintohexsubstring(0, @varbinaryValue,  1, 2) + '':'' +
            sys.fn_varbintohexsubstring(0, @varbinaryValue,  3, 2) + '':'' +
            sys.fn_varbintohexsubstring(0, @varbinaryValue,  5, 2) + '':'' +
            sys.fn_varbintohexsubstring(0, @varbinaryValue,  7, 2) + '':'' +
            sys.fn_varbintohexsubstring(0, @varbinaryValue,  9, 2) + '':'' +
            sys.fn_varbintohexsubstring(0, @varbinaryValue, 11, 2) + '':'' +
            sys.fn_varbintohexsubstring(0, @varbinaryValue, 13, 2) + '':'' +
            sys.fn_varbintohexsubstring(0, @varbinaryValue, 15, 2)
    END

    RETURN ''Invalid''
END

Answer 8

Puedes usar varchar. La duración de IPv4 es estática, pero la de IPv6 puede ser muy variable.

A menos que tenga una buena razón para almacenarlo como binario, quédese con un tipo de cadena (textual).

Answer 9

Suelo usar un viejo filtrado VARCHAR simple para una IPAddress que funciona bien.

Si quieres filtrar los rangos de la dirección IP, la dividiría en cuatro enteros.

Answer 10

sys.dm_exec_connections utiliza varchar (48) después de SQL Server 2005 SP1. Suena lo suficientemente bueno para mí, especialmente si quieres usarlo en comparación con tu valor.

De manera realista, no verá IPv6 como la corriente principal por un tiempo, por lo que preferiría la ruta 4 tinyint. Dicho esto, estoy usando varchar (48) porque tengo que usar sys.dm_exec_connections ...

De otra manera. La respuesta de Mark Redman menciona una pregunta anterior de debate SO.