MySQLでJSON型の既存データを暗号化する方法

• 概要
• 環境
• 平文データの作成
• 暗号化する
• ポイント

概要

特定のキーだけ暗号化したい場合に便利です

環境

• Ubuntu 18.04
• MySQL 5.7.41

平文データの作成

user_tbl の profile という JSON型のカラムに対して暗号します

まずは平文で更新や挿入してください

update user_tbl set profile = json_set(`profile`, "$.password", "hoge");

平文になっていることを確認します

select json_unquote(json_extract(`profile`, "$.password")) from user_tbl;

暗号化する

update + aes_encrypt + json_set で暗号化します

update user_tbl set profile = json_set(`profile`, "$.password", hex(aes_encrypt(profile->>"$.password", unhex(sha2('xxx',512)))));

確認します

select cast(aes_decrypt(unhex(json_unquote(json_extract(`profile`, "$.password"))), unhex(sha2('xxx',512))) as char character set utf8) from user_tbl;

ポイント

暗号化する際に既存データの参照を profile->>"$.password" とすることで実現できます
password というキーだけを暗号化します

取得する際には json_unquote + json_extract でダブルクォートを外した状態の暗号化文字列に対して復号化します
unquote していない状態だと復号化に失敗して NULL が返ってくることが確認できると思います