AES_ENCRYPTとLIKE演算子の利用

AES_ENCRYPTされた文字列の曖昧検索をしたいと思っています。AES_ENCRYPTで暗号化した文字列をBLOB型のフィールドに書き込んでいますが、SELECT文で文字列を抽出時、完全に一致する文字列しか抽出できません。

テーブル名：
member

フィールド名：
id blob
name blob
email blob


テーブルに登録されている文字列:

id name email
----------------------------------------
0001, dokumori, [e-mail]
0002, blahblah, [e-mail]
0003, dokublah, [e-mail]
(上記は全てAES_ENCRYPTされているものとします)

そして、以下のコマンドを走らせても、該当が見つかりません:
SELECT * FROM member WHERE name LIKE AES_ENCRYPT ('%doku%','password');

以下のようにすると、当然ですが、該当するレコードは表示されます：
SELECT * FROM member WHERE name = AES_ENCRYPT ('dokumori','password');


AES_ENCRYPTに関しては、文献自体が少ないようで、なかなか答えが見つかりません。この件に関してもしご存じでしたらアドバイスをお願いします。

No.2 ベストアンサー 回答者： taka451213 回答日時： 2004/12/30 14:05

こんにちは。

すみません、私の勘違いのようでした・・・。
この式の場合のポイントは、
「'dokumori'をENCRYPTしたものと'%doku%'をENCRYPTしたものが、果たしてLIKEで拾えるか？」
というところですね。
そこでAES_ENCRYPTについて調べてみました・・・。
AES はブロックレベルのアルゴリズムであるため、不揃いの長さの文字列のエンコード時には埋め込みが行われる。したがって、結果の文字列の長さは 16*(trunc(string_length/16)+1) として計算することができる。
とありました・・・。
「doku」をAES_ENCRYPTした結果と、「dokumori」をAES_ENCRYPTした結果が、前方ビットが同じであるなら、
SELECT * FROM member WHERE name LIKE CONCAT('%', AES_ENCRYPT('doku', 'password'));
で、前方一致検索はできそうですが、
実際のデータは'dokumoriXXXXXXXX'を暗号化したもの。
検索条件が'dokuXXXXXXXXXXXX'を暗号化したもの。
のような感じで、１６ビットを一括りとして変換してそうなので、まったく違う結果になってそうです・・・。
（推測ばっかりですみません・・・）
１文字ずつ暗号化しているのならできそうなんですが・・・。

お役に立てずにすみません・・・。
(^^ゞ

この回答へのお礼

ご回答有り難うございました。お返事遅れて申し訳ありません。

なるほど、埋め込みとはそういう意味だったのか。。。
確かに、埋め込み＋暗号化した文字列をマッチしようとしている訳ですから、マッチし得ないですね。仰るとおりだと思います。

まず、暗号化されたレコードたちを復号化して一時テーブルにはき出した後、ふつうにSELECTで抽出するのが、おそらく一番楽な方法ですね。

先月中頃から悩み続けていたのですが、ようやくすっきりしました。有り難うございました。

お礼日時：2005/01/04 21:45

No.1 回答者： taka451213 回答日時： 2004/12/29 10:58

こんにちは。

このSQLでは、LIKEの前にAES_ENCRYPT ('%doku%','password')が評価されていると思いますが・・・？
'%doku%'というキー（%も含んで文字列）では、該当する'password'が見つからなかった。
よって、AES_ENCRYPT関数の戻り値はNULL。
LIKE NULLは当然・・・ですよね？
(^^ゞ

この回答への補足

taka451213さん、コメントありがとうございます。


"LIKE"の代わりに"=" を使い、検索文字列を"%"で囲まない時には検索が成功するので、LIKEを利用した際の特殊な文法上のルールがあるか、またはLIKE自体が使えないのではないかと思っています。前者であることを望みつつ質問させていただいてます。

なお、"password"というのは、encrypt時に設定するパスワード文字列であり、検索対象ではありません。

AES_ENCRYPTを用いる場合、DBへの書き込みは:
INSERT INTO member (id, name, email) values AES_ENCRYPT('0001','password'), AES_ENCRYPT('dokumori','password'), AES_ENCRYPT([e-mail]','password');
というようなSQLを使って行います。

そして暗号化->バイナリ化された文字列を検索する際、キーワードを暗号化した上でマッチさせるため：

...AES_ENCRYPT ('doku','password')

としています。AES_ENCRYPTで暗号化せずに検索した場合、stringとbinaryがマッチし得ないため、望むような結果が表示されません。

ということで、ご指摘頂いた部分が原因ではないと思っていますが、如何でしょう？

補足日時：2004/12/29 22:09