[SQL Server] 해외 IP 접속에 대한 개발 건 - GeoIP

MS-SQL / SQL Server

해외 IP 접속에 대한 개발 건 - GeoIP 

첨부파일의 csv 는 웹에서 검색하여 찾아낸 IP 국가별 대역으로 유상으로 업그레이드 해주는 해외 사이트가 있습니다. 이 파일은 아마도 최근 업그레이드 되지 않은 기본 파일인듯 합니다. GeoIP 라는 이름으로 알려져 있습니다.

 

1. DB 구성(MSSQL 예제)

 

CSV를 MSSQL에서 import 합니다.

DB 구성은 아래의 구성에 , 구분자에 의해 합니다.

csv 중 한 레코드 -> 58.29.0.0,58.29.255.255,974979072,975044607,KR,Korea, Republic of

파일을 분석해 보면 시작IP, 종결IP, 시작IP 10진수 표현, 종결IP 10진수 표현, 국가코드 , 상세 등입니다.

 

2. 이제는 펑션을 하나 만듭니다. 엔드유저의 IP에 대해 비교를 위한 10진수 전환이 필요 합니다.

아마도 이부분이 핵심이 아닌가 합니다.

 

create function [dbo].[FN_IPConvert] (@ipaddr as varchar(50)) returns bigint as

begin

declare @c1 varchar(50) , @c2 varchar(50) , @c3 varchar(50) , @c4 varchar(50)

declare @ipaddr1 varchar(50)

set @c1 = ''

set @c2 = ''

set @c3 = ''

set @c4 = ''

set @ipaddr1 = ''

select @c1 = substring(@ipaddr,0,Charindex('.',@ipaddr,0))

select @ipaddr1 = replace(@ipaddr,@c1+'.','')

select @c2 = substring(@ipaddr1,0,Charindex('.',@ipaddr1,0))

select @ipaddr1 = replace(@ipaddr1,@c2+'.','')

select @c3 = substring(@ipaddr1,0,Charindex('.',@ipaddr1,0))

select @c4 = replace(@ipaddr1,@c3+'.','')

return (cast(@c1 as bigint)*256*256*256)+(cast(@c2 as bigint)*256*256)+(cast(@c3 as int)*256)+(cast(@c4 as bigint))

end

ip 대역중 각 클래스 별로 256 의 3승,2승,1승,0승을 곱하여 모두 더합니다. 이렇게 하면 256 진법상의 주소를 10진수로 표현한것과 같습니다.

 

3. 비교 프로시져 작성

 

제법 레코드수가 많으므로 국가코드에 인덱스를 지정한 후 'KR' 비교를 합니다. 본 예제는 한국 IP 가 아니면 액션을 취할 목적 이므로 한국을 비교 합니다. etc1,etc2,NN 은 각각 시작IP 10진수 표현, 종결IP 10진수 표현, 국가코드 필드 입니다. 즉, IP 주소는 비교상 난해 하므로 10진수로 변환하여 그 대역에 있는지를 비교하는 절차 입니다.

 

create procedure [dbo].[USP_iparea](@ipaddress as varchar(20)) as

select * from iparea where FN_IPConvert(@ipaddress) between 

cast (etc1 as bigint) and cast (etc2 as bigint) and NN = 'KR'