SQLSever：Binayデータの検索について

Question

SQLSever2012で、Binary型で格納したデータと一致するかどうかの検索を行いたいと考えています。

具体的には、

①Binary型のカラム（BinaryData）に、ENCRYPTBYPASSPHRASE(Binary型が戻る）で暗号化したデータを格納
②「ユーザ入力値」を、ENCRYPTBYPASSPHRASEして、①と比較

ということをしようとして、

Select * From XXX Where BinaryData = ENCRYPTBYPASSPHRASE('Password','ユーザ入力値')

という検索をしているのですが、存在するはずの「ユーザの入力データ」でも、ヒットしてくれません。

Binayデータの比較の場合、何か特殊な処理が必要なのでしょうか？ お知恵をお借りできれば幸いです。

Answer 1

暗号化されたものとの比較ではなく、復号したものと比較する必要があります。

Select * From XXX 
Where DecryptByPassphrase('Password', BinaryData) = convert(varbinary, 'ユーザ入力値')

Comments

[Everlasting]

ありがとうございます。
実は一旦復号して検索するように実装してみたのですが、この方法だと非常に負荷（CPU）がかかるようで、サーバがダウンしてしまったのです。復号しないで検索する方法があれば・・・と思ったのですが、やっぱり無理なんでしょうか？

[sazi]

純粋に検索用途を考えておられるなら、インデックスも必要です。
鍵が固定で構わないなら、ファンクションインデックスまたは式インデックスを試して下さい。
復号の負荷はインデックス作成時となり、検索時の負荷は下がります。
但し、他のDBMSではありますが、SQLSERVERでは実証したことはありませんので、インデックスの作成自体できないかもしれませんが。

[sazi]

もう一つの方法としては、SQLの構成自体を変更し、暗号化項目の検索を一番外側にして、対象件数を極力少なくすること。

[umyu]

＞Everlastingさんへ
完全一致で良ければ、blind indexも検討してみてくださいな。
https://github.com/ankane/blind_index
https://blogs.msdn.microsoft.com/raulga/2006/03/12/indexing-encrypted-data/

[sazi]

>umyuさん
フォローありがとうございます。
こんなのがあるということは、暗号化項目を検索対象にするのは色々とあるって事なんでしょうね。

[sazi]

そもそも、行ごとに鍵が必要って言うのがそれだけで嫌な感じがします。

[umyu]

＞saziさんへ
回答のコメント欄をお借りして失礼しました。
データベースに格納している暗号文を復号するときに、毎回復号するためCPU負荷が掛かってしまうので、そのための対応策ですね。暗号キーとは別の鍵で平文をHMAC化して、検索するときはHMACデータと比較する。

[Everlasting]

遅くなりすみません。ご回答頂き、ありがとうございました！
Binayデータのなかなか一筋縄ではいかないことが判りましたので、別の方法も併せて検討してみたいと思います。