MySQL格納データの暗号化

CREATE DATABASE test

--バイナリデータ用のBOLB型でテーブル作成。MySQLリファレンス推奨型。
CREATE TABLE login_tb (no BOLB,id BLOB,pass BLOB);
--※BOLB型だとデータ取り出し時に文字化けしたのでVARCHARAに変更。
--16進数文字列が入るのでサイズ大きめで。
CREATE TABLE login_tb (no VARCHARA(500),id VARCHARA(500),pass VARCHARA(500));

--AES_ENCRYPT('暗号化前のデータ', '暗号を解くキー')で、暗号化前のデータが暗号化（バイナリデータ化）されて格納される。
--しかし！このままでは文字化けした状態で表示される。
INSERT INTO login_tb(no,id,pass) VALUES(1, AES_ENCRYPT('aaa', 'id_key'), AES_ENCRYPT('111', 'pass_key'));

--そこで暗号化された物を、HEX()関数で１６進数に変換する。これで16進数アルファベット文字として表示される。
INSERT INTO login_tb(no,id,pass) VALUES(1, HEX(AES_ENCRYPT('aa', 'key')), HEX(AES_ENCRYPT('11', 'key')));
INSERT INTO login_tb(no,id,pass) VALUES(2, HEX(AES_ENCRYPT('bb', 'key')), HEX(AES_ENCRYPT('22', 'key')));
INSERT INTO login_tb(no,id,pass) VALUES(2, HEX(AES_ENCRYPT('cc', 'key')), HEX(AES_ENCRYPT('33', 'key')));
INSERT INTO login_tb(no,id,pass) VALUES(4, HEX(AES_ENCRYPT('bb', 'key')), HEX(AES_ENCRYPT('44', 'key')));
追加していく・・・

--注意点。キーをバラバラに設定するとややこしくなる。キーは全部同じものでいい。（必要であれば管理できる範囲で設定）
INSERT INTO login_tb(no,id,pass) VALUES(2, HEX(AES_ENCRYPT('aa', 'id_key')), HEX(AES_ENCRYPT('11', 'pass_key')));

--暗号化とは関係ない。普通の条件指定select文。
SELECT * FROM login_tb WHERE no=1;

--1６進数の文字列でselect。結果。(id='EF85045714A54BAEEECC87285BE529BA'のカラム&レコード。)
SELECT * FROM login_tb WHERE id='EF85045714A54BAEEECC87285BE529BA';

--暗号化前の元の文字列でselect。結果。(id='EF85045714A54BAEEECC87285BE529BA'のカラム&レコード。)結果は上記と同じ
SELECT * FROM login_tb WHERE id= HEX(AES_ENCRYPT('bb', 'key'));

--１６進数文字列でselect。元の文字列で表示。結果（bb）
SELECT AES_DECRYPT(UNHEX('EF85045714A54BAEEECC87285BE529BA'), 'key') AS new_column;

--idカラムを暗号化前の文字列で表示。
SELECT AES_DECRYPT(UNHEX(id), 'key') AS new_column FROM login_tb;

--idカラムとpassカラムを暗号化前の文字列で表示。
SELECT AES_DECRYPT(UNHEX(id), 'key'), AES_DECRYPT(UNHEX(pass), 'key') FROM login_tb ;

--暗号化前の元の文字列でselectし、元の文字列で表示。結果（aa）
SELECT AES_DECRYPT(UNHEX(id), 'key') AS new_tb FROM login_tb WHERE id= HEX(AES_ENCRYPT('aa', 'key'));


---暗号化とは関係ない・・・---
--カラム型変換
--ALTER TABLE テーブル名 MODIFY カラム名　新しいデータ型;
ALTER TABLE member ALTER COLUMN name BOLB;
ALTER TABLE member MODIFY name BLOB;
ALTER TABLE users MODIFY password VARCHAR(500);
--テーブル内データ削除
DELETE FROM member;
---ここまで関係ない----


--上記方法でもよいが、ストアドファンクションを使わないと、取り出す際、日本語が文字化けする。
--また、ソースに暗号化のキーがもろに登場するので、セキュリティ上よくない。

--functionをいじると、そのfunctionを使ったプログラム全てに影響が出るため、root権限者しかいじれない（作れない）様になっている。
--下記処理は全てのユーザがfunctionをいじれる様にする為の処理。
--こいつもrootで接続して実行しないと、rootでないと設定権限がないよー！とエラー！！
--また、この設定をしないとcreate functionで権限がないよー！とエラー。
MySQLコマンド
SET GLOBAL log_bin_trust_function_creators = 1;
を実行し、functionを作成。
functionを作ったら元に戻す。

SET GLOBAL log_bin_trust_function_creators = 0;

--上記処理に関して
MySQL5.0.16から導入されました。
この値はバイナリログが有効のときに適用されます。
Stored Function(以下関数)を作成できるユーザが、
信用できない関数を作成する可能性があるかどうかを制御します。
関数の内容によっては、
危険な状況を引き起こすようなバイナリログへの書き込みがなされる事があります。

この値を0(デフォルト)にすると、
SUPER および CREATE ROUTINE(ALTER ROUTINE) 権限を持たないユーザは
関数を作成(更新)することを許可されません。
0にすることはまた、関数を
DETERMINISTIC / READS SQL DATA / NO SQL のいずれかの特性を持った
形で作らなければならないという事を意味します。
この値を1にすると、そのような制限は課せられません。


--簡単なストアドファンクションの例。hello()という関数をつくる。concat()は元々MySQLに登録されている文字連結関数。
CREATE FUNCTION hello(input VARCHAR(200)) RETURNS VARCHAR(100) RETURN concat('Hello, ', input, '!');
--hello()に引数'ttttttttttt'を渡して実行。（表示）
SELECT hello('tttttttttttt');
--結果（Hello,ttttttttttt!）

--同じ名前のファンクションは作れないので間違ったときは一旦削除。
DROP FUNCTION hello ;
DROP FUNCTION decodes ;

--同様に暗号化の為のdecodes関数を作成。格納できる文字数は必要に応じて設定。
CREATE FUNCTION decodes(input VARCHAR(500)) RETURNS VARCHAR(64) RETURN AES_DECRYPT(UNHEX(input) ,'key');

SELECT decodes(引数);