コメント
コメントの投稿
※名前とタイトルが入力されていないコメントでは他のコメントとの区別ができません。
入力されていないコメントには返信しませんのであらかじめご了承くださいませ。
暗号化された文字列をあいまい検索する方法 | SQL Server
- 2016/12/01
- 22:49
今回は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というSQLを発行しても当然検索結果は０件になります。
WHERE [NAME] LIKE '山田%';
仮に「山田」を暗号化したデータが "ABC" とすると、
SELECT * FROM T_Customerとしても、検索はヒットしません。
WHERE [NAME] LIKE 'ABC%';
暗号化されている時点で１文字目から異なる文字列だからです。
ではどのようにしたらあいまい検索ができるかという点について説明していきます。
暗号化に対応した関数については、MySQLの AES_Encrypt 関数と AES_Decrypt 関数が有名です。
AES_Encrypt で暗号化し、
AES_Decrypt で復号化します。
同様にSQLServerでも暗号化の機能がSQL Server 2008から搭載されました。
それが、EncryptByPassPhrase 関数と DecryptByPassPhrase 関数です。
EncryptByPassPhrase で暗号化し、
DecryptByPassPhrase で復号化します。
引数は以下の通り
EncryptByPassPhrase(暗号鍵文字列, 暗号化したい文字列)
DecryptByPassPhrase(暗号鍵文字列, 復号化したい文字列)
１．サンプルソース
使用方法は以下の通り--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を用いたあいまい検索自体がフルスキャンになりますので、パフォーマンスに関しては妥協せざるを得ないでしょう。
２．実行結果
３．補足
今回は対称暗号化についてご紹介しましたが、他にもEncryptByKey と DecryptByKey
非対称暗号化では
EncryptByAsmKey と DecryptByAsmKey
EncryptByCert と DecryptByCert
などの関数が用意されています。
関数の詳細は以下のURLにて
https://msdn.microsoft.com/ja-jp/library/ms173744(v=sql.120).aspx
最後までお読みいただき、ありがとうございます。
他にも SQL Server に関する記事をご紹介しておりますのでご参考頂ければ幸いです。
Xamarin 4.2.1.64 がリリースされたので検証してみました。 | Xamarin ホーム
SQL Server Developer Edition をダウンロードする方法 | SQL Server
