未分類

base64で暗号化、復号化のコードを書いてみる for MySQL

更新日:

まず、暗号化、復号化の仕組みを自分で書いてみようと思った経緯ですが、とあるDBでMySql5.5を使用していまして、

開発中に、base64の暗号化をするto_base64()をsqlで使用したくなったのですが、動かない。

それもそのはず。

だって、その関数が追加されたのは、MySql5.6からなのだから。

バージョンアップをしようか考えましたが、初めての作業ですし、なにやら大変そうだったので、断念。

でもto_base64()を使いたい。

そうだ、作ればいいじゃないか。

そんなこんなでまた無駄な作業に没頭してしまいました笑

 

base64での暗号化、復号化を作成するにあたって、こちらを参考にしました。

  1. 元データ
    • 文字列: "ABCDEFG"
    • 16進表現: 41, 42, 43, 44, 45, 46, 47
    • 2進表現: 0100 0001, 0100 0010, 0100 0011, 0100 0100, 0100 0101, 0100 0110, 0100 0111
  2. 6ビットずつに分割
    • 010000 010100 001001 000011 010001 000100 010101 000110 010001 11
  3. 2ビット余るので、4ビット分0を追加して6ビットにする
    • 010000 010100 001001 000011 010001 000100 010101 000110 010001 110000
  4. 変換表により、4文字ずつ変換
    • "QUJD", "REVG", "Rw"
  5. 2文字余るので、2文字分 = 記号を追加して4文字にする
    • "QUJD", "REVG", "Rw=="
  6. Base64文字列
    • "QUJDREVGRw=="

(Wikipediaより引用:https://ja.wikipedia.org/wiki/Base64)

スポンサードサーチ

これだけの情報があれば余裕ですね。

ということで、書いたコードがこちらになります。

まずは、暗号化。

delimiter //
 
 CREATE FUNCTION to_base64(param varchar(10)) RETURNS varchar(100) DETERMINISTIC
     BEGIN
       DECLARE $SECOND varchar(100);
       DECLARE $FONT_CODE varchar(100);
       DECLARE $RESULT varchar(100);
       DECLARE $RETURN_LIST varchar(100);
       DECLARE $S_POSITION int;
       DECLARE $DIFF_CODE int;
       DECLARE $SPLIT_CHA varchar(30);
       SET $RETURN_LIST = '';
       
       -- 文字列を16進数表現->2進数に変換後、2進数変換時にconvで省略された、0を追加
       SELECT conv(hex(param),16,2) into $SECOND;
       loop_add8: LOOP
         IF CHAR_LENGTH($SECOND) % 8 = 0 THEN
           LEAVE loop_add8;
         END IF;
         SELECT CONCAT(0,$SECOND) into $SECOND;
         ITERATE loop_add8;
       END LOOP loop_add8;
    
       -- 2進数を6桁ごとに区切り、6桁に満たない部分は0で補う
       loop_split6: LOOP
         IF CHAR_LENGTH($SECOND) % 6 = 0 THEN
           LEAVE loop_split6;
         END IF;
         SELECT concat($SECOND,0) into $SECOND;
         ITERATE loop_split6;
       END LOOP loop_split6;
       
       -- 変換表により変換
       SET $S_POSITION = 1;
       loop_conversion: LOOP
         SELECT substring($SECOND, $S_POSITION , 6) into $SPLIT_CHA;
         SELECT conv($SPLIT_CHA,2,10) into $FONT_CODE;
         
         SET $DIFF_CODE =(
         CASE WHEN $FONT_CODE <= 25 THEN  65
	          WHEN $FONT_CODE <= 51 THEN  61
	          WHEN $FONT_CODE <= 61 THEN  -4
	          WHEN $FONT_CODE  = 62 THEN -19
	          WHEN $FONT_CODE  = 63 THEN -16
	     END);

         SELECT unhex(conv(conv($SPLIT_CHA,2,10)+$DIFF_CODE,10,16)) into $RESULT;
         SELECT concat($RETURN_LIST,$RESULT) into $RETURN_LIST;
         SET $S_POSITION  = $S_POSITION + 6;
    	 IF  $S_POSITION >= char_length($SECOND) THEN 
    	   LEAVE loop_conversion;
         END IF;
         ITERATE loop_conversion;
       END LOOP  loop_conversion;
	   
	   -- 4文字ごとに区切り、4文字に見たいない部分に=を追加する
       loop_add_equal: LOOP
         IF char_length($RETURN_LIST) % 4 = 0 THEN
           LEAVE loop_add_equal;
         END IF;
         SELECT concat($RETURN_LIST,'=') into $RETURN_LIST;
         ITERATE loop_add_equal;
       END LOOP loop_add_equal;
       RETURN $RETURN_LIST;
     END;
     
//

 

そして、復号化のコードがこちらです。

暗号化のほとんど逆の手順を踏めば良いため、コメントはあまり記載していません。

delimiter //
 
 CREATE FUNCTION from_base64(param varchar(100)) RETURNS varchar(100) DETERMINISTIC
 
     BEGIN
       DECLARE $EQUAl_TRIM varchar(100);
       DECLARE $FONT_CODE varchar(100);
       DECLARE $RESULT varchar(100);
       DECLARE $RETURN_LIST varchar(100);
       DECLARE $TMP_LIST varchar(100);
       DECLARE $CUR int;
       DECLARE $CUR_SPL int;
       DECLARE $DIFF_CODE int;
       DECLARE $SPLIT_CHA varchar(30);
       SET $RETURN_LIST = '';
       SET $TMP_LIST = '';
       SET $EQUAl_TRIM = TRIM( '=' FROM param );
       SET $FONT_CODE = conv(hex($EQUAL_TRIM),16,10);

       SET $CUR = 1;
       SET $CUR_SPL = 1;
       loop_conversion: LOOP
         SELECT substring($EQUAl_TRIM, $CUR , 1) into $SPLIT_CHA;
         
         SELECT conv(hex($SPLIT_CHA),16,10) into $FONT_CODE;
         
         SET $DIFF_CODE =(
         CASE WHEN $FONT_CODE <=  57 THEN   4
         	  WHEN $FONT_CODE <=  90 THEN -65
         	  WHEN $FONT_CODE <= 122 THEN -71
              WHEN $FONT_CODE =   47 THEN  16
	          WHEN $FONT_CODE =   43 THEN  19
	     END);
	     
         SELECT conv($FONT_CODE + $DIFF_CODE,10,2) into $RESULT;
         
       loop_add6: LOOP
         IF CHAR_LENGTH($RESULT) % 6 = 0 THEN
           LEAVE loop_add6;
         END IF;
         SELECT CONCAT(0,$RESULT) into $RESULT;
         ITERATE loop_add6;
       END LOOP loop_add6;
       
         SELECT concat($TMP_LIST,$RESULT) into $TMP_LIST;

         SET $CUR  = $CUR + 1;
    	 IF  $CUR > char_length($EQUAl_TRIM) THEN 
    	   LEAVE loop_conversion;
         END IF;
         
         ITERATE loop_conversion;
       END LOOP  loop_conversion;

       loop_split8: LOOP
         SELECT substring($TMP_LIST, $CUR_SPL , 8) into $SPLIT_CHA;
         
         SELECT concat($RETURN_LIST,unhex(conv($SPLIT_CHA,2,16))) into $RETURN_LIST;
         
         SET $CUR_SPL  = $CUR_SPL + 8;
         
    	 IF  $CUR_SPL >= char_length($TMP_LIST) THEN 
    	   LEAVE loop_split8;
         END IF;
                
         ITERATE loop_split8;
       END LOOP  loop_split8;
       SELECT TRIM( $RETURN_LIST ) into $RETURN_LIST;
       RETURN $RETURN_LIST;
       
     END;
     
//

 

こちらのコードは、Githubにも載せています。

https://github.com/k-takeuchi220/base64_for_mysql

 

MySqlのバージョンアップがめんどくさいという方は使ってみてください。

-未分類

Copyright© エンジニアの階段 , 2019 All Rights Reserved Powered by STINGER.