fc2ブログ

記事一覧

暗号化された文字列をあいまい検索する方法 | SQL Server


今回はSQL Server にて暗号化されたデータに対してあいまい検索する方法をご紹介いたします。暗号化といってもDB丸ごと暗号化する機能の事ではなく、今回はデータ単位で暗号化してあるレコードに対してあいまい検索する方法です。
これがまた厄介なのです。



前提条件
・Windows 7 professional 以降 / Windows Server 2008 R2 以降
・SQL Server 2008 Developer / Standard / Enterprise 以降



例えば 山田太郎 という個人情報を暗号化した場合、

T_Customerテーブル
ID,  NAME
-------------------------------
 1,  0x8E52936391BE9859   ←「山田太郎」を暗号化したデータ

上記のようにDBに登録されているとします。
これに対して、
SELECT * FROM T_Customer 
WHERE [NAME] LIKE '山田%';
というSQLを発行しても当然検索結果は0件になります。
仮に「山田」を暗号化したデータが "ABC" とすると、
SELECT * FROM T_Customer
WHERE [NAME] LIKE 'ABC%';
としても、検索はヒットしません。
暗号化されている時点で1文字目から異なる文字列だからです。
ではどのようにしたらあいまい検索ができるかという点について説明していきます。

暗号化に対応した関数については、MySQLの AES_Encrypt 関数と AES_Decrypt 関数が有名です。
AES_Encrypt で暗号化し、
AES_Decrypt で復号化します。

同様にSQLServerでも暗号化の機能がSQL Server 2008から搭載されました。
それが、EncryptByPassPhrase 関数と DecryptByPassPhrase 関数です。
EncryptByPassPhrase で暗号化し、
DecryptByPassPhrase で復号化します。


引数は以下の通り
EncryptByPassPhrase(暗号鍵文字列, 暗号化したい文字列)
DecryptByPassPhrase(暗号鍵文字列, 復号化したい文字列)



1.サンプルソース

使用方法は以下の通り

--DBへ暗号化したデータを作成する
INSERT INTO T_Customer (
     ID, [NAME]
) VALUES (
     1
    ,EncryptByPassPhrase('password', '山田太郎')
);
--DBから復号化したデータに対してあいまい検索をする
SELECT
     ID
    ,Convert(varchar, DecryptByPassPhrase('password', [NAME])) AS DecryptName
FROM T_Customer
WHERE DecryptByPassPhrase('password', [NAME]) LIKE '%太郎%'
;


復号化した文字列に対し、平文の文字列であいまい検索をかけます。
この場合、全てのデータが一度復号化され、そのデータに対しあいまい検索がかかりますので、当然インデックススキャンにはならず、フルスキャンになります。
ただ、そもそもLikeを用いたあいまい検索自体がフルスキャンになりますので、パフォーマンスに関しては妥協せざるを得ないでしょう。



2.実行結果


AESDecrypt_02.png   


3.補足

今回は対称暗号化についてご紹介しましたが、他にも
EncryptByKey と DecryptByKey
非対称暗号化では
EncryptByAsmKey と DecryptByAsmKey
EncryptByCert と DecryptByCert
などの関数が用意されています。

関数の詳細は以下のURLにて
https://msdn.microsoft.com/ja-jp/library/ms173744(v=sql.120).aspx






最後までお読みいただき、ありがとうございます。
他にも SQL Server に関する記事をご紹介しておりますのでご参考頂ければ幸いです。


関連記事

コメント

コメントの投稿

※名前とタイトルが入力されていないコメントでは他のコメントとの区別ができません。

 入力されていないコメントには返信しませんのであらかじめご了承くださいませ。

※ニックネームでも良いので必ずご入力ください。

    

※必ずご入力ください。

    
    

※必ずご入力ください。

※技術的な質問には環境やエラーについて正確かつ詳細にお教えください。

・正確なエラーの内容

・Windowsのバージョン番号

・Visual Studioのバージョン

・機器の型番

・アプリやソフトのバージョン

    

カテゴリ別記事一覧

広告

プロフィール

石河 純


著者名 :石河 純
自己紹介:素人上がりのIT技術者。趣味は卓球・車・ボウリング

IT関連の知識はざっくりとこんな感じです。
【OS関連】
WindowsServer: 2012/2008R2/2003/2000/NT4
Windows: 10/8/7/XP/2000/me/NT4/98
Linux: CentOS RedHatLinux9
Mac: macOS Catalina 10.15 / Mojave 10.14 / High Sierra 10.13 / Sierra 10.12 / OSX Lion 10.7.5 / OSX Snow Leopard 10.6.8
【言語】
VB.net ASP.NET C#.net Java VBA
Xamarin.Forms
【データベース】
Oracle 10g/9i
SQLServer 2016/2008R2/2005/2000
SQLAnywhere 16/11/8
【BI/レポートツール】
Cognos ReportNet (IBM)
Microsoft PowerBI
ActiveReport (GrapeCity)
CrystalReport
【OCX関連】
GrapeCity InputMan SPREAD MultiRow GridView
【ネットワーク関連】
CCNP シスコ技術者認定
Cisco Catalyst シリーズ
Yamaha RTXシリーズ
FireWall関連
【WEB関連】
SEO SEM CSS jQuery IIS6/7 apache2

休みの日は卓球をやっています。
現在、卓球用品通販ショップは休業中です。