IT

SQL Server에 IP 주소를 저장하기위한 데이터 유형

lottoking 2020. 8. 10. 07:30
반응형

SQL Server에 IP 주소를 저장하기위한 데이터 유형

SQL Server에 IP 주소를 저장하려는 데이터 유형을 선택해야합니까?

올바른 데이터 유형을 IP 주소로 쉽게 필터링 할 수 있습니까?

IPv4를 저장하는 기술적으로 올바른 방법은 바이너리 (4)입니다. 왜냐하면 그것이 실제로있는 것이 때문입니다. 바이너리 콘텐츠의 표시 변환).

이렇게하면 함수가 텍스트 표시 형식으로 또는 텍스트 표시 형식으로 변환되기를 원할 것입니다.

텍스트 표시 형식을 바이너리로 변환하는 방법은 다음과 같습니다.

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

바이너리를 다시 텍스트 형식으로 변환하는 방법은 다음 표시와 가능합니다.

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

사용 방법에 대한 데모는 다음과 가변합니다.

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

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

마지막으로 조회 및 활용을 위해 항상 이진 형식을 사용하십시오.

최신 정보 :

SQL Server에서는 UDF의 고유 한 성능 문제를 해결하기 위해 한 가지 방법을 추가하고 싶었지만 함수의 코드를 유지하는 대신 iTVF (인라인 테이블 반환 함수)를 사용하는 것입니다. 위의 첫 번째 번째 함수 (문자열에서 바이너리로)를 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

예를 들면 다음과 같습니다.

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

그리고 다음은 INSERT에서 사용하는 방법입니다.

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

varchar를 사용할 수 있습니다. IPv4의 길이는 고정적이지만 IPv6의 길이는 매우 가변적입니다.

바이너리로 표현 이유가없는 한 문자열 (텍스트) 유형을 사용하십시오.

다음은 varchar 형식의 IPV4 또는 IPv6를 바이너리 (16)로 변환하고 그 변환하는 코드입니다. 이것은 제가 생각할 수있는 가장 작은 형태입니다. 인덱싱이 잘되어야하며 서브넷을 필터링하는 쉬운 방법을 제공합니다. SQL Server 2005 이상이 필요합니다. 확실하게 방탄인지 확실하지 않습니다. 도움이 되셨기를 바랍니다.

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

IPv4및을 모두 처리하고 싶기 때문에 및 다음 함수를 IPv6사용 하여 IP 주소 표시를 바이트 및 그 반대로 변환합니다 .VARBINARY(16)SQL CLRtext

[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);
}

sys.dm_exec_connectionsSQL Server 2005 SP1 이후에 varchar (48)를 사용합니다. 특히 당신이 당신의 가치와 비교하여 그것을 사용하고 싶다면 나에게 충분하게 들립니다.

현실적으로, 당신은 아직 IPv6를 주류로 보지 않을 것이기 때문에 저는 4 개의 tinyint 경로를 선호합니다. 즉, 내가 사용해야하기 때문에 varchar (48)을 사용하고 있습니다 sys.dm_exec_connections...

그렇지 않으면. Mark Redman의 답변은 이전 SO 토론 질문을 언급합니다 .

.NET을 사용하는 사람들은 IPAddress 클래스를 사용하여 IPv4 / IPv6 문자열을 구문 분석하고 VARBINARY(16). 동일한 클래스를 사용하여 byte[]문자열 로 변환 할 수 있습니다 . VARBINARYin 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

나는 일반적으로 IPAddress에 대해 일반 오래된 VARCHAR 필터링을 사용합니다.

IP 주소 범위를 필터링하려면 네 개의 정수로 나눌 것입니다.

감사합니다 RBarry. IP 블록 할당 시스템을 구성하고 바이너리로 저장하는 것이 유일한 방법입니다.

IP 블록의 CIDR 표현 (예 : 192.168.1.0/24)을 varchar 필드에 저장하고 2 개의 계산 된 필드를 사용하여 블록의 시작과 끝의 이진 형식을 유지합니다. 거기에서 빠른 쿼리를 실행하여 주어진 블록이 이미 할당되었거나 할당 가능한지 확인할 수 있습니다.

다음과 같이 끝 IP 주소를 계산하도록 함수를 수정했습니다.

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

나는 SandRock의 기능을 좋아합니다. 하지만 dbo.fn_ConvertIpAddressToBinary 코드에서 오류를 발견했습니다 . @delim을 연결할 때 @ipAddress VARCHAR (39)의 수신 매개 변수가 너무 작습니다.

SET @ipAddress = @ipAddress + @delim

40으로 늘릴 수 있습니다. 더 큰 새 변수를 사용하고 내부적으로 사용하는 것이 더 좋습니다. 그렇게하면 많은 수에서 마지막 쌍을 잃지 않습니다.

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

나는 지금 varchar(15)까지 모든 것이 나를 위해 일하고 있습니다. 삽입, 업데이트, 선택. 아직 개발 작업을 많이하지 않았지만 IP 주소가있는 앱을 막 시작했습니다.

다음은 select 문입니다.

select * From dbo.Server 
where  [IP] = ('132.46.151.181')
Go

참고 URL : https://stackoverflow.com/questions/1385552/datatype-for-storing-ip-address-in-sql-server

반응형

'IT' 카테고리의 다른 글

언제 .gitignore 대신 .git / info / exclude를 사용하여 파일을 제외하고?  (0) 2020.08.10
겹치는 원의 결합 영역  (0) 2020.08.10
Visual Studio의 기본 제공 ASP.NET 개발 서버가있는 HTTPS  (0) 2020.08.10
Letsencrypt가 기존 인증서에 도메인 추가  (0) 2020.08.10
예외 안전을 위해 "범위가 지정된 동작"을 포함 할 수있는 수단으로 IDisposable 및 "사용"을 사용하는 것이 악의적입니까?  (0) 2020.08.10

'IT'의 다른글

  • 현재글SQL Server에 IP 주소를 저장하기위한 데이터 유형

관련글

댓글

티스토리툴바