オートナンバーをランダムユニークな値に変換する

MySQL（MariaDB）のオートナンバー型ってもちろん便利なのですが、数年システムを運用していくと、こんな課題が出てきてしまいます。 とあるWEBシステムで、規則性のあるデータを見つけてしまう。ちなみに、これ、セキュリティの話ではなく、あくまでも、外部から見て、規則性を隠蔽するって意味で説明します。

三菱電機 アルカリ乾電池(シュリンクパック) 単4形 10本パック LR03N/10S

連番の弊害

例えば、単純なデータとして、コメントテーブルとかあったりします。

初心者用のプログラム本とか見ていると、

http://aaa.bbb.ccc/comments/id=1; http://aaa.bbb.ccc/comments/id=2; http://aaa.bbb.ccc/comments/id=3; http://aaa.bbb.ccc/comments/id=4; http://aaa.bbb.ccc/comments/id=5;

とか連番で出ている時がありますよね。この場合は、id=6にすれば次が見れるとか簡単に予測できてしまいます。もちろん、これで良いからそうしているのであってやっているのですが（例が悪かった。。）

連番を暗号化する

でわ、上記の連番を暗号化して、次は何かを予測するする方法を考えてみました。

sha2で数字を暗号化する

こんなストアドファンクションを作成してみました

• ストアドファンクション

与えられた数値を引数にして、結果sha2で暗号化された結果を返すファンクションです。sha2で暗号化したものは、事実上複合することは不可能なので、内部で、idとsha2の暗号化のマッピングテーブルを作成するようにしております

DELIMITER $$

DROP FUNCTION IF EXISTS sf_enc_id $$


CREATE FUNCTION `sf_enc_id`(_id int(11)) RETURNS varchar(128)
BEGIN
   DECLARE _result varchar(128) ;

   INSERT INTO
       id_mappings (id,id_hash)
   VALUES
       (_id,sha2(_id,224))
   ON DUPLICATE KEY UPDATE
        id = _id,
        id_hash = sha2(_id,224)
   ;

    RETURN sha2(_id,224) ;
    

END $$

DELIMITER ;

• 例

こんな感じで使います。上記ストアドファンクションの作り的に、該当のマッピングがない場合はINSERTを実施し、存在する場合は、マッピングテーブルをUPDATEします。あればINSERT なければ、UPDATE は、

hit.hateblo.jp

で書きました

• SQL

mysql> SET @id = 1123 ; SELECT @id,sf_enc_id(@id) AS sha2_id ;                                                                      
Query OK, 0 rows affected (0.00 sec)

+------+----------------------------------------------------------+
| @id  | sha2_id                                                  |
+------+----------------------------------------------------------+
| 1123 | 5e07355ae7d3c6d86de4b55e21f955944b07c3448e01a490626a4de2 |
+------+----------------------------------------------------------+

sha2_idは、sf_enc_idのresultsetです。マッピングテーブルが存在しなくても、sha2暗号化はできるので、結果は必ず返します

処理実行後のマッピングデータは、こんな感じの紐付け表が作られるイメージです

MariaDB [wsales]> SELECT * FROM id_mappings;
+----+---------------------+---------------------+-----------+----------------------------------------------------------+
| id | created_at          | updated_at          | a_id | id_hash                                                       |
+----+---------------------+---------------------+-----------+----------------------------------------------------------+
|  1 | 2016-10-24 20:35:57 | 2016-10-24 20:35:57 |       123 | 78d8045d684abd2eece923758f3cd781489df3a48e1278982466017f |
|  3 | 2016-10-24 20:39:01 | 2016-10-24 20:39:01 |       122 | 794293b5706d6f85da86d164393511bb834cd222812a2016e68bb54c |
|  5 | 2016-10-24 20:39:39 | 2016-10-24 20:39:39 |       124 | aca0ccb40a7b1b92a27d0767202f694db1488d4925eacc5838aa20d9 |
|  6 | 2016-10-24 20:39:42 | 2016-10-24 20:39:42 |       125 | bb4a94cd430ccdce63146651fa502b28b960dc6279a61b8d85c8d795 |
|  7 | 2016-10-24 21:22:26 | 2016-10-24 21:22:26 |      1208 | 6e50e78e471c1b4dd4c9d6a0e12e8d1cede1a4104b648b39d76df74d |
|  8 | 2016-10-24 21:22:31 | 2016-10-24 21:22:31 |      1209 | 1e7ff0216fcfca68eb8fa018ea0e19b552e9f5b3780c54a44bfd2641 |
| 10 | 2016-10-25 15:31:31 | 2016-10-25 15:31:31 |      1123 | 5e07355ae7d3c6d86de4b55e21f955944b07c3448e01a490626a4de2 |
+----+---------------------+---------------------+-----------+----------------------------------------------------------+

マッピング表を作成するのは、前述の通り、エンコードはかけれても、デコードはできない特性があるためであり例えば、上記の78d8045d684abd2eece923758f3cd781489df3a48e1278982466017f の暗号化キーのidが何かを求める時は

SELECT a_id,id_hash FROM id_mappings WHERE id_hash = '78d8045d684abd2eece923758f3cd781489df3a48e1278982466017f' ;

とすれば、取得できるのは、わかるかと思います。

MySQLの暗号化については、いくつか種類があります。他の方法を試してみたい！！なんて方は、

MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.13 暗号化関数と圧縮関数

を見ていただけますと、幸せになれると思います