AES_ENCRYPTで暗号化したテーブルをORDER BYで正常に並び替える

前文

最近バックエンドシステム開発のお仕事でDBを暗号化する機会が多くなってきました。

特に顧客情報や受注情報を扱う際なんかは直接varchar型やらtext型にデータを収めていてはDBを抜かれた瞬間にアウトということで、INSERT・UPDATE時に暗号化した値を挿入し、SELECT時には値を復号して使用するというケースがスタンダートになりつつあります。

さて、DBにはデータ保存、PHPやRuby等のアプリケーションには鍵の保存という明確な役割分担が存在しますが、大抵どちらにも暗号化・復号処理が用意されているため、どちらにこの処理を割り振るかは開発者に選択の余地があります。

アプリケーション側で処理を行えば、そのアプリケーションを通してでなければ復号されたデータが見えないので安全性は高いのですが、SQL側でWHEREやORDER BYが使えなくなるというデメリットがあり、大量のデータの中から目的のデータを探す用途には向きません。

逆にデータベース側に暗号化・復号処理を任せておけばLIKE等、普段使うSQL構文がほぼ使用可能です（個人的にMATCH AGAINSTが使えなかったのは痛かったのですが……どなたか使用方法をご存じの方がいらっしゃればコメントを頂けると超嬉しいです）。

なので少量のデータはアプリケーション側での暗号化、大量のデータ（特にユーザーが使うフロントエンドで一覧として出力したり、様々な条件で検索されたりするデータ）はデータベース側の暗号化というふうに使い分けているのですが、今回は後者、MySQL側でAES_ENCRYPT/AES_DECRYPTを用いたデータをORDER BYで並び替える際の話になります。

サンプルテーブル

今回は例としてユーザーの名前と貯蓄額を暗号化して保存するテーブルを作成してみましょう。

CREATE TABLE IF NOT EXISTS `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` BLOB DEFAULT NULL,
  `savings` BLOB DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1;

こんな感じでnameとsavingをBLOB型に指定しておきます。

そしてデータを暗号化しつつINSERTで突っ込みます。SQLの文中に出てくる'password'が暗号化・復号に使われる鍵文字列です。

INSERT INTO `user` (`name`, `savings`)
VALUES (AES_ENCRYPT('めぐろ', 'password'), AES_ENCRYPT(100000, 'password')),
(AES_ENCRYPT('しながわ', 'password'), AES_ENCRYPT(8000000, 'password')),
(AES_ENCRYPT('しぶや', 'password'), AES_ENCRYPT(200, 'password')),
(AES_ENCRYPT('いけぶくろ', 'password'), AES_ENCRYPT(-50000, 'password')),
(AES_ENCRYPT('えびす', 'password'), AES_ENCRYPT(7005000, 'password')),
(AES_ENCRYPT('おおさき', 'password'), AES_ENCRYPT(0, 'password')),
(AES_ENCRYPT('にしにっぽり', 'password'), AES_ENCRYPT(9, 'password')),
(AES_ENCRYPT('はらじゅく', 'password'), AES_ENCRYPT(-99999999, 'password')),
(AES_ENCRYPT('ごたんだ', 'password'), AES_ENCRYPT(777777777777, 'password'));

並び替えの結果が分かりやすくなるように名前はワザとひらがなで指定しました。

「いけぶくろ」さんと「はらじゅく」さんが負債を負ってますね。特に恨みはありませんが、savingにはUNSIGNEDを指定していないので負の数も扱えます。というかsavingはBLOB型なので文字ならなんでも入ります。

とりあえずソート無しで復号しつつ取得してみる

試しにいつものノリで取得してみましょう。

SELECT *
FROM `user`;

+----+------------------+------------------+
| id | name             | savings          |
+----+------------------+------------------+
|  1 | ﾗjZﾔ:,X8蕃・| 9ﾖfﾉ殉｢ﾀ$uj |
'ﾝ鋗ﾜ｣ |｡=截ｻﾁﾇ難寛襯  | ｴ^ﾜw2-ｽ
|  3 | ｨ>ｹﾋ"#p鬘~禽ｩ/ﾉ | ｸｿｽr 竓倢Hｦﾇ |
|  4 |  /妻ﾌﾄ4ｵ・ ｨeﾚﾜ | U・ﾏ{uwI・ﾙgs |
諚k7#ｭﾈﾀｺ2ﾙ｣ |0・ﾕ>ｼ | ｿ
|  6 | 僴嶸b・Vｵ|"ｹ･ﾔ | M・ｽcﾊﾊ?"･ |
|  7 | 穆wC､ｰ1J瞹*IｪLﾍ | 倭Gﾎﾁﾏ-jｴp煉 |
|  8 | 鶴｣錠ﾆo・=ｱﾘﾊ | <Kｦ嚔・⑰TLq |
|  9 | ∑･'N確aｲ・｣Т | ,0'ｱｰｮ&|d_・|
+----+------------------+------------------+

読めねぇ

復号に使う鍵文字列を持たない攻撃者にはデータベースがこう見えています。これでいいんです。

で、実際に取得する際にはこんなSQLを書きます。

SELECT `id`, AES_DECRYPT(`name`, 'password'), AES_DECRYPT(`savings`, 'password')
FROM `user`;

+----+---------------------------------+------------------------------------+
| id | AES_DECRYPT(`name`, 'password') | AES_DECRYPT(`savings`, 'password') |
+----+---------------------------------+------------------------------------+
|  1 | めぐろ                          | 100000                             |
|  2 | しながわ                        | 8000000                            |
|  3 | しぶや                          | 200                                |
|  4 | いけぶくろ                      | -50000                             |
|  5 | えびす                          | 7005000                            |
|  6 | おおさき                        | 0                                  |
|  7 | はらじゅく                      | -99999999                          |
|  8 | ごたんだ                        | 99999999999999                     |
+----+---------------------------------+------------------------------------+

ちゃんと復号しつつ取得できていますね。

第一教訓: ORDER BY に指定するカラムもAES_DECRYPTしましょう

では、今度は名前順に並び替えて取得してみましょう。

以下、期待に応えてくれないSQLです。

SELECT `id`, AES_DECRYPT(`name`, 'password'), AES_DECRYPT(`savings`, 'password')
FROM `user`
ORDER BY `name`;

+----+---------------------------------+------------------------------------+
| id | AES_DECRYPT(`name`, 'password') | AES_DECRYPT(`savings`, 'password') |
+----+---------------------------------+------------------------------------+
|  6 | おおさき                        | 0                                  |
|  4 | いけぶくろ                      | -50000                             |
|  2 | しながわ                        | 8000000                            |
|  5 | えびす                          | 7005000                            |
|  9 | ごたんだ                        | 777777777777                       |
|  7 | にしにっぽり                    | 9                                  |
|  3 | しぶや                          | 200                                |
|  1 | めぐろ                          | 100000                             |
|  8 | はらじゅく                      | -99999999                          |
+----+---------------------------------+------------------------------------+

「お」から始まっているので期待通りかと思いきや、そういえば「いけぶくろ」さんがいらっしゃいました。「いけぶくろ」と「えびす」の間に「しながわ」があるのも決定的な間違いでしょう（駅的な話ではありません、念のため）。

何がいけなかったのかというと、取得する値はSELECT文の後に書いてあるもの、つまり今回は`id`, AES_DECRYPT(`name`, 'password'), AES_DECRYPT(`savings`, 'password')部分なので、ちゃんと復号された値が取得できているのですが、並び替え条件の部分でAES_DECRYPTを使用していないので暗号化された生のバイナリデータでソートをしてしまっているのが問題です。

SQLを修正し、改めて取得してみましょう。
以下、期待に応えてくれるSQLです。

SELECT `id`, AES_DECRYPT(`name`, 'password'), AES_DECRYPT(`savings`, 'password')
FROM `user`
ORDER BY AES_DECRYPT(`name`, 'password');

+----+---------------------------------+------------------------------------+
| id | AES_DECRYPT(`name`, 'password') | AES_DECRYPT(`savings`, 'password') |
+----+---------------------------------+------------------------------------+
|  4 | いけぶくろ                      | -50000                             |
|  5 | えびす                          | 7005000                            |
|  6 | おおさき                        | 0                                  |
|  9 | ごたんだ                        | 777777777777                       |
|  2 | しながわ                        | 8000000                            |
|  3 | しぶや                          | 200                                |
|  7 | にしにっぽり                    | 9                                  |
|  8 | はらじゅく                      | -99999999                          |
|  1 | めぐろ                          | 100000                             |
+----+---------------------------------+------------------------------------+

いいですねぇ～。

第二教訓: 数値としてソートする場合はちゃんとCASTしましょう

さて、名前ときたら今度は貯蓄額でソートしたいですよね。

以下、期待に応えてくれないSQLです。

SELECT `id`, AES_DECRYPT(`name`, 'password'), AES_DECRYPT(`savings`, 'password')
FROM `user`
ORDER BY AES_DECRYPT(`savings`, 'password');

+----+---------------------------------+------------------------------------+
| id | AES_DECRYPT(`name`, 'password') | AES_DECRYPT(`savings`, 'password') |
+----+---------------------------------+------------------------------------+
|  4 | いけぶくろ                      | -50000                             |
|  8 | はらじゅく                      | -99999999                          |
|  6 | おおさき                        | 0                                  |
|  1 | めぐろ                          | 100000                             |
|  3 | しぶや                          | 200                                |
|  5 | えびす                          | 7005000                            |
|  9 | ごたんだ                        | 777777777777                       |
|  2 | しながわ                        | 8000000                            |
|  7 | にしにっぽり                    | 9                                  |
+----+---------------------------------+------------------------------------+

？？？

並んでいるようで全然並んでいません。
9円しか貯蓄のない「にしにっぽり」さんが一番の金持ちとして君臨し、負債TOPの「はらじゅく」さんが何か汚い手を使ってワースト一位を「いけぶくろ」さんに押し付けています。

しかし、よく見てみるとこのソートは数値のソートではなく、文字列のソートになっていることが分かるでしょう。

文字列として値を評価した際、例えば「ごたんだ」さんと「にしにっぽり」さんの値に着目してみると一文字目が7の五反田さんは一文字目が9の「にしにっぽり」さんより小さい値として並べられるのです。「いけぶくろ」さんと「はらじゅく」さんにも同じことが起きていますね。

そういえばsavingsカラムはもともと文字列型の一種であるBLOB型なので、なるほど確かに、先ほどのSQLではこうなるのが自然です。

しかしアプリケーション側でいちいちキャストしてからソートしてはパフォーマンスがよろしくないのでSQLにもうひと手間加えましょう。
以下、期待に応えてくれるSQLです。

SELECT `id`, AES_DECRYPT(`name`, 'password'), AES_DECRYPT(`savings`, 'password')
FROM `user`
ORDER BY CAST(AES_DECRYPT(`savings`, 'password') AS SIGNED);

+----+---------------------------------+------------------------------------+
| id | AES_DECRYPT(`name`, 'password') | AES_DECRYPT(`savings`, 'password') |
+----+---------------------------------+------------------------------------+
|  8 | はらじゅく                      | -99999999                          |
|  4 | いけぶくろ                      | -50000                             |
|  6 | おおさき                        | 0                                  |
|  7 | にしにっぽり                    | 9                                  |
|  3 | しぶや                          | 200                                |
|  1 | めぐろ                          | 100000                             |
|  5 | えびす                          | 7005000                            |
|  2 | しながわ                        | 8000000                            |
|  9 | ごたんだ                        | 777777777777                       |
+----+---------------------------------+------------------------------------+

素晴らしい。