Início / dba / Perguntas / 215477
Accepted
A_V
Asked: 2018-08-22 04:57:28 +0800 CST

Função de manipulação Sqlserver IPV6 ou conversão numérica

  • 772

Estou procurando maneiras de lidar com intervalos de endereços CIDR IPv6 no SqlServer.

Os dados que obtive estão formatados conforme abaixo e possuem estas colunas:

Int_IP_Start, Int_IP_End, CIDR_Range, ASN, Name

para 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"

E isso é o mesmo, mas para 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"

A representação numérica de um único IPv6 é bastante grande, pois o espaço de endereço é equivalente a um inteiro de 128 bits.

O objetivo aqui é ter uma maneira de uma consulta de banco de dados retornar se um IP faz ou não parte de um dos intervalos armazenados que vieram do CSV.

Para o IPv4 é fácil, você pega o IP e converte para seu equivalente INT32.

Como não há tipo de dados INT128 no SqlServer, tenho as seguintes perguntas:

-Existe uma maneira de criar um tipo personalizado que armazenaria um bigint de 128 bits? (Só precisa executar operações maiores ou menores que)

-Existe uma maneira de lidar adequadamente com intervalos de endereços IP no SqlServer?

EDITAR:

Aqui está o maior número que um endereço IPv6 pode representar:

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

Este código c# gera 340282366920938463463374607431768211455

Tentando inserir isso

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 O número '340282366920938463463374607431768211455' está fora do intervalo para representação numérica (precisão máxima 38).

sql-server-2016 ipv6

1 respostas

  1. Best Answer

    Existe uma maneira de criar um tipo personalizado que armazenaria um bigint de 128 bits? (Só precisa executar operações maiores ou menores que)

    Você pode não precisar de um tipo personalizado - numeric(38,0)pode ser adequado aqui (embora eu não conheça o intervalo completo para inteiros válidos de 128 bits ou se o IPv6 pode ser restrito ao intervalo de valores que se encaixam):

    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;

    Se isso não funcionar para os endereços aos quais você precisa oferecer suporte, talvez seja necessário separar o prefixo de rede (48 bits), o ID da sub-rede (16 bits) e o ID da interface (64 bits) em três números separados antes de passá-los. ao SQL Server. Suas cláusulas where se tornariam mais complexas, mas essa pode ser uma abordagem mais simples do que tentar representar um IPv6 como um único número de qualquer maneira.

    Existe uma maneira de lidar adequadamente com intervalos de endereços IP no SqlServer?

    O SQL Server não tem capacidade interna para entender o que é um endereço IP.

    • 2

relate perguntas