[SQL Server / MS-SQL] 특정 IP 주소별 국가(코드) 조회

 

 

---

 

 

특정 IP 주소를 가지고 국가코드(혹은 국가) 를 조회하려고 한다.

하지만 IPv4 형식으로 DB 에서 조회 시 원하는 데이터를 얻을 수 없다.

 

이는 IP 주소를 정수형으로 변환하여 해결 할 수 있으며, 아래의 3가지 작업이 필요하다.

 

1. 국가별 IP 대역 테이블 생성

2. IP 주소 정수화 변환 함수 생성

3. IP 주소별 국가 코드 조회 SP 생성 (이건 필요에 따라 생성해도 되고 안해도 됨!)

 

 

우선, 국가별 IP 대역 데이터를 가져오자.

국가별 IP 대역 데이터는 한국인터넷정보센터(KRNIC) 에서 가져올 수 있다.

https://xn--3e0bx5euxnjje69i70af08bea817g.xn--3e0b707e/jsp/statboard/IPAS/ovrse/natal/IPaddrBandCurrent.jsp

한국인터넷정보센터(KRNIC)

 

[전체국가 csv 다운로드] 를 통해 데이터를 csv 로 가져올 수 있다.

 

 

국가(국가코드)별 IP 대역 테이블을 생성한다.

-- 국가코드별 IP 대역 테이블 생성
CREATE TABLE [dbo].[IPRange](
	[Idx] [int] IDENTITY(1,1) NOT NULL, -- 일련 번호
	[CountryCode] [varchar](4) NULL, -- 국가 코드
	[StartIP] [varchar](24) NULL, -- 시작 IP 대역
	[EndIP] [varchar](24) NULL, -- 종료 IP 대역
	[Prefix] [varchar](10) NULL, -- Prefix
CONSTRAINT [PK_IPRange_Idx] PRIMARY KEY CLUSTERED ([Idx] ASC)
GO

 

테이블 생성 후, 위 csv 파일의 필요한 데이터만 INSERT 한다.

 

그리고 IP 주소 정수형 변환을 위한 함수를 만들자.

-- IP 주소 정수형 변환 함수 생성
CREATE FUNCTION [dbo].[FN_LongIP]( @IP varchar(24) )
RETURNS bigint
AS
BEGIN
   DECLARE @ipA bigint, @ipB bigint, @ipC bigint, @ipD bigint, @ipI bigint 

   -- PARSENAME 함수 이용하여 IP 대역별 SPLIT
   SELECT @ipA = PARSENAME(@ip, 4)
   SELECT @ipB = PARSENAME(@ip, 3)
   SELECT @ipC = PARSENAME(@ip, 2)
   SELECT @ipD = PARSENAME(@ip, 1)

   RETURN ( @ipA * 256*256*256 ) + ( @ipB * 256*256 ) + ( @ipC * 256 ) + @ipD
END

 

 

특정 IP 주소의 국가 코드 조회 SP 를 생성해보자.두가지 방법을 통해 생성할 수 있으며, 필요한 기능에 따라 선택하면 된다.

 

1. 테이블에 정수형 IP 대역 컬럼 추가 (정수형 IP 를 DB 테이블 내에서 관리하고 싶을때 사용)

2. SP 내에 함수 이용하여 정수형 IP 대역 변환 (별도로 정수형IP 대역 데이터 관리가 필요 없을때 사용)

 

 

방법1. 국가(코드)별 IP 대역 테이블에 정수형 IP 대역 컬럼 추가

---

국가(코드)별 IP 대역 테이블에 정수형 IP 주소 컬럼을 추가하자.

-- 정수형 시작IP 대역 컬럼 추가
ALTER TABLE IPRange ADD i_StartIP bigint NULL
GO
-- 정수형 종료IP 대역 컬럼 추가
ALTER TABLE IPRange ADD i_EndIP bigint NULL
GO

 

추가한 컬럼에 각 정수형 IP 주소를 일괄 UPDATE 하여 테이블 내에 정수형 IP 주소까지 넣어준다.

BEGIN TRAN

    UPDATE  A
    SET A.[i_StartIP] = dbo.fn_LongIP(A.StartIP),
        A.[i_EndIP] = dbo.fn_LongIP(A.EndIP)
    FROM IPRange A
    INNER JOIN IPRange B ON A.Idx = B.Idx

COMMIT

 

 

 

테이블까지 완료됐다면, 특정 IP 의 국가(코드) 조회 SP 를 생성해보자.

CREATE PROCEDURE [dbo].[USP_CountryCode_IPRange_Chk]
	@Ip NVARCHAR(24) -- 10.10.10.10 형식
AS
BEGIN
	...
    	(생략)
    	...
    
	DECLARE @IpCheck BIGINT;

	-- 정수형 IP 주소 가져오기
	SELECT @IpCheck = dbo.FN_LongIP(@Ip)

	-- 해당 IP 주소의 CountryCode 조회
    	SELECT  TOP 1 CountryCode
    	FROM	dbo.IPRange
    	WHERE   i_StartIP <= @IpCheck and  @IpCheck <= i_EndIP
    
    	...
    	(생략)
   	...
    
END

 

 

 

 

방법2. SP 내에 함수 이용하여 정수형 IP 대역 변환 

---

특정 IP 의 국가(코드) 조회 SP 내에서 정수형 IP 주소 변환 함수를 이용하여 SP 를 생성한다. 

CREATE PROCEDURE [dbo].[USP_CountryCode_IPRange_Chk]
	@Ip NVARCHAR(24) -- 10.10.10.10 형식
AS
BEGIN
	...
    	(생략)
    	...
    
	DECLARE @IpCheck BIGINT;

	-- 정수형 IP 주소 가져오기
	SELECT @IpCheck = dbo.FN_LongIP(@Ip)

	-- 해당 IP 주소의 CountryCode 조회
    	SELECT  TOP 1 CountryCode
    	FROM	dbo.IPRange
    	WHERE   dbo.FN_LongIP(StartIP) <= @IpCheck AND @IpCheck <= dbo.FN_LongIP(EndIP)
    
    	...
    	(생략)
   	...
    
END

 

해당 SP 를 통하여 특정 IP 의 국가(코드)를 확인 할 수 있다.