SQL Tips

 

MaxMind GEOIP Function for Microsoft SQL SERVER

MS-SQL / SQL Server
MaxMind GEOIP Function for Microsoft SQL SERVER

GeoIPを利用してIPアドレスの国情報を取得することができます。
GeoIPのデータは世界のIPの国情報があるデータで、毎月上旬に更新されます。

ダウンロードは下記のリンクを参考してください。

GeoIPCountryWhois.csv download : 

上記のページで「GeoLite Country」のCSV/ZIPをダウンロードしてください。

では、「GeoIPCountryWhois.csv」データをDBテーブルに保存する為に下記のテーブルを作成しましょう。

-- #### テーブル作成
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[GeoIP](
[StartIp] [varchar](39) NOT NULL,
[EndIp] [varchar](39) NOT NULL,
[StartNo] [numeric](18, 0) NOT NULL,
[EndNo] [numeric](18, 0) NOT NULL,
[NationCode] [varchar](5) NOT NULL,
[NationName] [varchar](80) NULL
) ON [PRIMARY]

-- #### INDEX作成
CREATE NONCLUSTERED INDEX [IX_GeoIP_StartNo_EndNo] ON [dbo].[GeoIP] 
(
[StartNo] ASC,
[EndNo] ASC
)
GO


ダウンロードした「GeoIPCountryWhois.csv」ファイルの中には","(ダブルコーディション)区切りのCSVですので、
BULK INSERTする時はXMLフォーマットを使って登録します。
「GeoIp.xml」ファイルを作成して下記のxml内容を入れてください。

-- #### GeoIp.xml 作成。BULK INSERTを利用してデータを一括登録するため準備
<?xml version="1.0"?>
<BCPFORMAT
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="&quot;"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="&quot;\n"/>
</RECORD>
<ROW>
<COLUMN SOURCE="2" NAME="StartIp" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="EndIp" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="4" NAME="StartNo" xsi:type="SQLBIGINT"/>
<COLUMN SOURCE="5" NAME="EndNo" xsi:type="SQLBIGINT"/>
<COLUMN SOURCE="6" NAME="Con1" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="7" NAME="Con2" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>


では、下記のSQLを使ってデータを登録してください。

-- #### BULK INSERTを利用してデータを一括登録
BULK INSERT dbo.GeoIP
FROM
'C:\innoya\GeoIPCountryWhois.csv'
WITH 
(
FORMATFILE = 'C:\innoya\GeoIp.xml'
)

これでデータは一括で登録された。

では、チェックするストアドプロシージャを作成しましょう。

CREATE FUNCTION [dbo].[fnGetGeoIPNationCode] (
    @IpAddr varchar(39)
)
RETURNS VARCHAR(5) AS  
BEGIN
DECLARE @RtnVal varchar(5)
SET @RtnVal = ''

-- #### IPを数字に変更 #### START ####

DECLARE @IpNum numeric(18,0)
DECLARE @ip1 bigint, @ip2 bigint, @ip3 bigint, @ip4 bigint

SET @ip1 = PARSENAME(@IpAddr, 4)
SET @ip2 = PARSENAME(@IpAddr, 3)
SET @ip3 = PARSENAME(@IpAddr, 2)
SET @ip4 = PARSENAME(@IpAddr, 1)

SET @IpNum = @ip1*(256*256*256)+@ip2*(256*256)+@ip3*(256)+@ip4


-- #### IPを数字に変更 #### END ####

-- 国のIPをチェック
SELECT TOP 1 @RtnVal = NationCode 
FROM dbo.GeoIP WITH (NOLOCK) 
WHERE StartNo <= @IpNum AND EndNo >= @IpNum

    RETURN @RtnVal                                                                          

END



IP Addressを渡せば国のコードがリータンされます。
実際に下記の例文で実行して結果を確認してみましょう。

SELECT dbo.fnGetGeoIPNationCode('74.119.76.100')

このように接続IPの国を確認することができます。
活用例としては日本のみ接続を許可することもできますし、
特定の国のみ遮断することもできます。

ですが、ウェブサイトの場合、Google-bot, Bing-botなどの検索エンジンまで遮断する場合がありますので、
少し工夫をして開発する必要があります。

このエントリーをはてなブックマークに追加
2016-02-16 15:00:38   706

コメント

[SQL Server] リンクサーバーのリストを取得する方法 - sp_linkedservers

-.日付:2017-01-16   カテゴリ : -   閲覧数:1811

[SQL Server] ガチャ(GACHA)システムを作ろう!

-.日付:2016-08-18   カテゴリ : -   閲覧数:889

[SQL Server] クーポンコード(シリアルコード)発行する方法

-.日付:2016-08-12   カテゴリ : -   閲覧数:591

[SQL Server] 使い捨てメールサービス遮断(Block)してください。

-.日付:2016-03-18   カテゴリ : -   閲覧数:1638

MaxMind GEOIP Function for Microsoft SQL SERVER

-.日付:2016-02-16   カテゴリ : -   閲覧数:706

[SQL Server] IPAddressからCクラス(C Class)まで抽出(取得)する方法

-.日付:2016-02-10   カテゴリ : -   閲覧数:633

Copyright © 2015 INNOYA.COM All rights reserved. RSS