MySQL/MariaDBのデータをPHP側で暗号化・復号する

なぜデータを暗号化・復号するのか？
AES_ENCRYPTとHEXを使ったINSERT文
AES_ENCRYPTとHEXを使ったUPDATE文
AES_DECRYPTとUNHEXを使ったSELECT文
暗号化するSQL文
復号するSQL文

php関連記事

なぜデータを暗号化・復号するのか？

データベースに個人情報などを暗号化せずそのまま保存した場合、万が一データベースの情報が流出した時に第三者が見れてしまうのを防ぐためにデータを暗号化します。よくパスワードはphpのmd5でハッシュ化して保存しますね。

今回は氏名やメールアドレスなどを暗号化した後、データベースから呼び出すときに復号してデータが読めるよう元のデータに戻します。

AES_ENCRYPTとHEXを使ったINSERT文

SQL文

INSERT INTO `users` (
  name
) VALUES (
  HEX(AES_ENCRYPT('渡辺', 'password'))
)

AES_ENCRYPTとHEXを使ったINSERT文で「渡辺」の値を暗号化して追加します。

AES_ENCRYPT・AES_DECRYPTはMySQLの暗号化関数です。
HEXという関数で16進数化して保存します。
passwordは任意のパスワードを設定し、暗号化・復号の際に同じPWを使用します。
16桁以内にしておくとよいそうです。

AES_ENCRYPTとHEXを使ったUPDATE文

SQL文

UPDATE `users`
SET 
  name = HEX(AES_ENCRYPT('渡辺', 'password'))
WHERE `id` = :user_id

暗号化についてはINSERT文と同じ内容でUPDATE文を作成します。
暗号化後のデータはバイナリになるので、カラムの型に注意しないと正しくデータの書き込みができず、復号できません。

AES_DECRYPTとUNHEXを使ったSELECT文

SQL文

SELECT 
id, 
convert(
  AES_DECRYPT(UNHEX(name), 'password')
  USING utf8
) as name,
convert(
  AES_DECRYPT(UNHEX(kana), 'password')
  USING utf8
) as kana 
FROM `users`

暗号化したデータを復号しています。
暗号化されているカラム毎に指定が必要です。

暗号化するSQL文

PHP

const PASSWORD = 'password';
function colum_enc($value) {
  $enc = "HEX(AES_ENCRYPT('".$value."', '".PASSWORD."'))";
  return $enc;
}

暗号化するカラムごとにSQL文を書いていくと大変なことになるのでまとめてみました。

復号するSQL文

PHP

function colum_dec($value) {
  $dec = "
  convert(
    AES_DECRYPT(UNHEX(".$value."), '".PASSWORD."')
    USING utf8
  ) as ".$value;
  return $dec;
}

復号するカラムごとにSQL文を書いていくと大変なことになるのでまとめてみました。