MySQL 5.6 リファレンスマニュアル  /  関数と演算子  /  暗号化関数と圧縮関数

12.13 暗号化関数と圧縮関数

表 12.17 暗号化関数

名前 説明
AES_DECRYPT() AES を使用して復号化します
AES_ENCRYPT() AES を使用して暗号化します
COMPRESS() バイナリ文字列として結果を返します
DECODE() ENCODE() を使用して暗号化された文字列をデコードします
DES_DECRYPT() 文字列を復号化します
DES_ENCRYPT() 文字列を暗号化します
ENCODE() 文字列をエンコードします
ENCRYPT() 文字列を暗号化します
MD5() MD5 チェックサムを計算します
OLD_PASSWORD() (deprecated 5.6.5) 4.1 より前の PASSWORD 実装の値を返します
PASSWORD() パスワード文字列を計算して返します
RANDOM_BYTES() ランダムなバイトベクトルを返します
SHA1(), SHA() SHA-1 160 ビットチェックサムを計算します
SHA2() SHA-2 チェックサムを計算します
UNCOMPRESS() 圧縮された文字列を圧縮解除します
UNCOMPRESSED_LENGTH() 圧縮前の文字列長を返します
VALIDATE_PASSWORD_STRENGTH() パスワードの強度を判断します

多くの暗号化関数および圧縮関数では、結果に任意のバイト値が含まれている可能性のある文字列が返されます。これらの結果を格納する場合は、VARBINARY または BLOB バイナリ文字列データ型のカラムを使用します。これにより、末尾の空白を削除したり、文字セットを変換したりするとデータ値が変更される可能性があるという問題を回避できます。たとえば、非バイナリ文字列のデータ型 (CHARVARCHARTEXT) を使用した場合に、これが発生する可能性があります。

一部の暗号化関数では、ASCII 文字 (MD5()OLD_PASSWORD()PASSWORD()SHA()SHA1()SHA2()) の文字列が返されます。MySQL 5.6 では、戻り値は、character_set_connection および collation_connection システム変数で決定された文字セットおよび照合順序を含む非バイナリ文字列です。

MD5()SHA1() などの関数がバイナリ文字列として 16 進数の文字列を返すバージョンでは、戻り値を大文字に変換したり、大文字と小文字が区別されない方法でそのまま比較したりできません。値を非バイナリ文字列に変換する必要があります。セクション12.10「キャスト関数と演算子」で、バイナリ文字列の変換の説明を参照してください。

アプリケーションで 16 進数の文字列を返す関数 (MD5()SHA1() など) からの値を格納する場合は、UNHEX() を使用して 16 進表現をバイナリに変換し、その結果を BINARY(N) カラムに格納すれば、より効率的な格納および比較を実現できます。16 進数の各ペアには、バイナリ形式での 1 バイトが必要であるため、N の値は、16 進文字列の長さによって異なります。N は、MD5() 値の場合は 16、SHA1() の場合は 20 です。SHA2() の場合、N の範囲は、結果の目的のビット長を指定する引数に応じて 28 から 32 までです。

utf8 文字セット (文字ごとに 4 バイト使用されます) が使用されるカラムに値が格納される場合に、16 進文字列を CHAR カラムに格納する際のサイズのペナルティーは最小で 2 回、最大で 8 回です。また、文字列を格納すると、値が大きくなり、文字セットの照合順序ルールを考慮に入れる必要があるため、比較が遅くなります。

アプリケーションで MD5() 文字列値が CHAR(32) カラムに格納されると仮定します。

CREATE TABLE md5_tbl (md5_val CHAR(32), ...);
INSERT INTO md5_tbl (md5_val, ...) VALUES(MD5('abcdef'), ...);

16 進文字列をよりコンパクトな形式に変換するには、次のように、代わりに UNHEX() および BINARY(16) が使用されるようにアプリケーションを変更します。

CREATE TABLE md5_tbl (md5_val BINARY(16), ...);
INSERT INTO md5_tbl (md5_val, ...) VALUES(UNHEX(MD5('abcdef')), ...);

ハッシュ関数が 2 つの異なる入力値に同じ値を生成するという非常にまれなケースに対応できるように、アプリケーションが準備されるはずです。競合を検出可能にする方法の 1 つは、ハッシュカラムを主キーにすることです。

注記

MD5 および SHA-1 アルゴリズムの悪用が知られています。代わりに、このセクションで説明するその他の暗号化関数 (SHA2() など) のいずれか使用することを検討してください。

注意

SSL 接続が使用されていなければ、暗号化関数への引数として指定されたパスワードやその他の機密の値は、プレーンテキストで MySQL サーバーに送信されます。また、このような値は、書き込み先の MySQL ログにも表示されます。このようなタイプの露出を回避するために、アプリケーションはクライアント側で機密の値を暗号化してから、サーバーに送信できます。同じ考慮事項が暗号化鍵にも適用されます。これらの露出を回避するために、アプリケーションはストアドプロシージャーを使用して、サーバー側で値を暗号化および復号化できます。

  • AES_DECRYPT(crypt_str,key_str[,init_vector])

    この関数は、公式の AES (Advanced Encryption Standard) アルゴリズムを使用してデータを復号化します。詳細は、AES_ENCRYPT() の説明を参照してください。

    MySQL 5.6.17 の時点では、オプションの初期化ベクトル引数 init_vector を使用できます。そのバージョンでは、AES_DECRYPT() が使用されるステートメントはステートメントベースのレプリケーションに対して安全ではなく、クエリーキャッシュ内に格納できません。

  • AES_ENCRYPT(str,key_str[,init_vector])

    AES_ENCRYPT() および AES_DECRYPT() では、AES (Advanced Encryption Standard) アルゴリズム (以前は Rijndael と呼ばれていました) を使用したデータの暗号化および復号化が実装されます。AES の標準では、さまざまな鍵の長さが許可されます。デフォルトでは、これらの関数で鍵の長さが 128 ビットの AES が実装されます。MySQL 5.6.17 の時点では、あとで説明するように、使用できる鍵の長さは 196 または 256 ビットです。鍵の長さは、パフォーマンスとセキュリティーの間でのトレードオフです。

    AES_ENCRYPT() は、鍵文字列 key_str を使用して文字列 str を暗号化し、暗号化された出力を含むバイナリ文字列を返します。AES_DECRYPT() は、鍵文字列 key_str を使用して暗号化された文字列 crypt_str を復号化し、元のプレーンテキスト文字列を返します。関数引数のいずれかが NULL の場合は、関数で NULL が返されます。

    str および crypt_str 引数は任意の長さにすることができ、AES などのブロックベースのアルゴリズムによって、必要に応じてブロックの倍数になるように、自動的にパディングが str に追加されます。このパディングは、AES_DECRYPT() 関数によって自動的に削除されます。crypt_str の長さは、次の公式を使用して計算できます。

    16 * (trunc(string_length / 16) + 1)
    

    鍵の長さが 128 ビットの場合、key_str 引数に鍵を渡すもっともセキュアな方法は、完全にランダムな 128 ビット値を作成し、それをバイナリ値として渡すことです。例:

    INSERT INTO t
    VALUES (1,AES_ENCRYPT('text',UNHEX('F3229A0B371ED2D9441B830D21A390C3')));
    

    パスフレーズを使用すると、パスフレーズをハッシュ化することで AES 鍵を生成できます。例:

    INSERT INTO t VALUES (1,AES_ENCRYPT('text', SHA2('My secret passphrase',512)));
    

    パスワードまたはパスフレーズは直接 crypt_str に渡さず、最初にハッシュ化してください。このドキュメントの以前のバージョンでは、従来のアプローチが提案されていましたが、ここで示す例の方がセキュアであるため、推奨されなくなりました。

    AES_DECRYPT() で無効な日付または不正なパディングが検出された場合は、NULL が返されます。ただし、入力データまたは鍵が無効になっている場合は、AES_DECRYPT()NULL 以外の値 (ごみの可能性もあります) が返される可能性があります。

    MySQL 5.6.17 の時点では、AES_ENCRYPT() および AES_DECRYPT() でブロック暗号化モードの制御が許可され、オプションの init_vector 初期化ベクトル引数が指定されます。

    • block_encryption_mode システム変数は、ブロックベースの暗号化アルゴリズムのモードを制御します。そのデフォルト値は、128 ビットの鍵の長さと ECB モードを使用した暗号化を表す aes-128-ecb です。この変数で許可されている値については、セクション5.1.4「サーバーシステム変数」を参照してください。

    • オプションの init_vector 引数では、必要とするブロック暗号化モードに対応する初期化ベクトルが提供されます。

    オプションの init_vector 引数が必要なモードでは、16 バイト以上の長さにする必要があります (16 を超えるバイトは無視されます)。init_vector が欠落している場合は、エラーが発生します。

    init_vector が必要ないモードでは、これが無視され、指定されている場合は警告が生成されます。

    RANDOM_BYTES(16) を呼び出すと、初期化ベクトルに使用されるバイトのランダム文字列を生成できます。初期化ベクトルが必要な暗号化モードでは、暗号化および復号化でも同じベクトルを使用する必要があります。

    mysql> SET block_encryption_mode = 'aes-256-cbc';
    mysql> SET @key_str = SHA2('My secret passphrase',512);
    mysql> SET @init_vector = RANDOM_BYTES(16);
    mysql> SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);
    mysql> SELECT AES_DECRYPT(@crypt_str,@key_str,@init_vector);
    +-----------------------------------------------+
    | AES_DECRYPT(@crypt_str,@key_str,@init_vector) |
    +-----------------------------------------------+
    | text                                          |
    +-----------------------------------------------+
    

    次の表には、許可されている各ブロック暗号化モード、サポートされている SSL ライブラリ、および初期化ベクトル引数が必須であるかどうかを一覧表示します。

    ブロック暗号化モード モードがサポートされている SSL ライブラリ 初期化ベクトルが必要
    ECB OpenSSL、yaSSL いいえ
    CBC OpenSSL、yaSSL はい
    CFB1 OpenSSL はい
    CFB8 OpenSSL はい
    CFB128 OpenSSL はい
    OFB OpenSSL はい

    MySQL 5.6.17 では、AES_ENCRYPT() または AES_DECRYPT() が使用されるステートメントはステートメントベースのレプリケーションに対して安全ではなく、クエリーキャッシュ内に格納できません。

  • COMPRESS(string_to_compress)

    文字列を圧縮し、その結果をバイナリ文字列として返します。この関数を使用するには、MySQL が zlib などの圧縮ライブラリを使用してコンパイルされている必要があります。そうでない場合、戻り値は常に NULL になります。圧縮された文字列は、UNCOMPRESS() を使用して圧縮解除できます。

    mysql> SELECT LENGTH(COMPRESS(REPEAT('a',1000)));
            -> 21
    mysql> SELECT LENGTH(COMPRESS(''));
            -> 0
    mysql> SELECT LENGTH(COMPRESS('a'));
            -> 13
    mysql> SELECT LENGTH(COMPRESS(REPEAT('a',16)));
            -> 15
    

    圧縮された文字列の内容は、次の方法で格納されます。

    • 空の文字列は、空の文字列として格納されます。

    • 空以外の文字列は、4 バイトの長さの圧縮されていない文字列として格納され (低いバイトが 1 番目)、そのあとに圧縮された文字列が続きます。文字列が空白文字で終わる場合は、結果が CHAR または VARCHAR カラムに格納されていても、末尾の空白文字が削除されるという問題が回避されるように、. 文字が追加されます。(ただし、CHARVARCHAR などの非バイナリ文字列のデータ型を使用して圧縮された文字列を格納すると、文字セットの変換が発生する可能性があるため、いずれにしても推奨されません。代わりに、VARBINARY または BLOB バイナリ文字列のカラムを使用してください。)

  • DECODE(crypt_str,pass_str)

    暗号化された文字列 crypt_str は、パスワードとして pass_str を使用して復号化します。crypt_str は、ENCODE() から返された文字列にするようにしてください。

  • DES_DECRYPT(crypt_str[,key_str])

    DES_ENCRYPT() を使用して暗号化された文字列を復号化します。エラーが発生した場合、この関数は NULL を返します。

    この関数は、MySQL が SSL サポートで構成されている場合のみ機能します。セクション6.3.10「セキュアな接続のための SSL の使用」を参照してください。

    key_str 引数が指定されていない場合、DES_DECRYPT() は暗号化された文字列の最初のバイトを調査して、元の文字列を暗号化したときに使用した DES 鍵番号を特定してから、DES 鍵ファイルからメッセージを復号化するための鍵を読み取ります。これが機能するには、ユーザーが SUPER 権限を持っている必要があります。鍵ファイルは、--des-key-file サーバーオプションを使用して指定できます。

    この関数を key_str 引数に渡すと、その文字列がメッセージを復号化するための鍵として使用されます。

    crypt_str 引数が暗号化された文字列として表示されない場合は、MySQL では指定された crypt_str が返されます。

  • DES_ENCRYPT(str[,{key_num|key_str}])

    Triple-DES アルゴリズムを使用して、指定された鍵で文字列を暗号化します。

    この関数は、MySQL が SSL サポートで構成されている場合のみ機能します。セクション6.3.10「セキュアな接続のための SSL の使用」を参照してください。

    使用する暗号化鍵は、指定されていれば、DES_ENCRYPT() への 2 番目の引数に基づいて選択されます。引数を付けない場合は、DES 鍵ファイルの最初の鍵が使用されます。key_num 引数を付けると、DES 鍵ファイルの指定された鍵番号 (0 - 9) が使用されます。key_str 引数を付けた場合は、指定された鍵文字列を使用して str が暗号化されます。

    鍵ファイルは、--des-key-file サーバーオプションを使用して指定できます。

    返される文字列は、最初の文字が CHAR(128 | key_num) であるバイナリ文字列です。エラーが発生した場合、DES_ENCRYPT()NULL を返します。

    暗号化された鍵を認識しやすくするために、128 が追加されます。文字列鍵を使用する場合、key_num は 127 です。

    結果の文字列の長さは、次の公式で指定されます。

    new_len = orig_len + (8 - (orig_len % 8)) + 1
    

    DES 鍵ファイルの各行の書式は、次のとおりです。

    key_num des_key_str
    

    key_num 値は、0 から 9 までの範囲内の数字にする必要があります。ファイル内の行は、任意の順序で指定できます。des_key_str は、メッセージを暗号化する際に使用される文字列です。数字と鍵の間には、少なくとも 1 つの空白文字を入れるようにしてください。最初の鍵は、DES_ENCRYPT() への鍵引数を指定しなかった場合に使用されるデフォルトのキーです。

    FLUSH DES_KEY_FILE ステートメントで鍵ファイルから新しいキー値を読み取るように、MySQL に指示できます。これには、RELOAD 権限が必須です。

    デフォルト鍵のセットを持つ利点は、これらの値を復号化する権利をエンドユーザーに付与せずに、暗号化されたカラム値の有無をチェックする方法がアプリケーションに提供されることです。

    mysql> SELECT customer_address FROM customer_table 
         > WHERE crypted_credit_card = DES_ENCRYPT('credit_card_number');
    
  • ENCODE(str,pass_str)

    パスワードとして pass_str を使用して、str を暗号化します。結果は、str と同じ長さのバイナリ文字列です。結果を復号化するには、DECODE() を使用します。

    ENCODE() 関数は使用されるべきではなくなっています。まだ ENCODE() を使用する必要がある場合は、リスクを軽減するために、一緒に salt 値を使用する必要があります。例:

    ENCODE('plaintext', CONCAT('my_random_salt','my_secret_password'))
    

    パスワードを更新するたびに、新しいランダムな salt 値を使用する必要があります。

  • ENCRYPT(str[,salt])

    Unix crypt() システム呼び出しを使用して str を暗号化し、バイナリ文字列を返します。salt 引数は、2 つ以上の文字を含む文字列にする必要があります。それ以外の場合は、結果が NULL になります。salt 引数が指定されていない場合は、ランダムな値が使用されます。

    mysql> SELECT ENCRYPT('hello');
            -> 'VxuFAJXVARROc'
    

    少なくとも一部のシステムでは、ENCRYPT()str の最初の 8 文字以外のすべてを無視します。この動作は、ベースとなる crypt() システム呼び出しの実装によって決まります。

    システム呼び出しではゼロバイトで終了する文字列が要求されるため、ucs2utf16utf16le、または utf32 マルチバイト文字セットを含む ENCRYPT() の使用は推奨されません。

    システムで crypt() を使用できない場合 (Windows の場合など)、ENCRYPT() は常に NULL を返します。

  • MD5(str)

    文字列の MD5 128 ビットチェックサムを計算します。この値は、32 桁の 16 進数の文字列として返されます。引数が NULL だった場合は、NULL になります。たとえば、戻り値をハッシュ鍵として使用できます。効率的なハッシュ値の格納については、このセクションの冒頭で示した注記を参照してください。

    戻り値は、接続文字セット内の非バイナリ文字列です。

    mysql> SELECT MD5('testing');
            -> 'ae2b1fca515949e5d54fb22b8ed95575'
    

    これは、RSA Data Security, Inc. MD5 Message-Digest Algorithm です。

    このセクションの冒頭で示した MD5 アルゴリズムに関する注記を参照してください。

  • OLD_PASSWORD(str)

    OLD_PASSWORD() は、セキュリティーを改善するために、MySQL 4.1 で PASSWORD() の実装が変更されたときに追加されました。OLD_PASSWORD() は、PASSWORD() の 4.1 よりも前の実装の値を文字列として返します。これは、バージョン 5.6 の MySQL サーバーに接続する必要のある 4.1 よりも前のクライアントをロックアウトせずに、それらのクライアントのパスワードをリセットするよう許可するためです。セクション6.1.2.4「MySQL でのパスワードハッシュ」を参照してください。

    戻り値は、接続文字セット内の非バイナリ文字列です。

    注記

    4.1 より前のハッシュ方式を使用するパスワードはネイティブのパスワードハッシュ方式を使用するパスワードよりもセキュアでないため、使用しないようにしてください。4.1 よりも前のパスワードは非推奨であり、これらのサポートは今後の MySQL リリースで削除される予定です。最終的に、OLD_PASSWORD() も非推奨になりました。

  • PASSWORD(str)

    平文のパスワード str から計算してハッシュ化されたパスワード文字列を返します。戻り値は、接続文字セット内の非バイナリ文字列です。引数が NULL の場合は、NULL になります。この関数は、mysql.user 付与テーブルに格納する MySQL パスワードを暗号化する際に、サーバーで使用されるアルゴリズムへの SQL インタフェースです。

    old_passwords システム変数は、PASSWORD() 関数で使用されるパスワードのハッシュ化方法を制御します。これは、IDENTIFIED BY 句を使用してパスワードを指定する CREATE USER および GRANT ステートメントによって実行されるパスワードハッシュにも影響します。

    次の表は、old_passwords の許可される値、それぞれの値に対するパスワードハッシュ方式、およびそれぞれの方式でハッシュされたパスワードを使用する認証プラグインを示します。これらの値は MySQL 5.6.6 以降で許可されます。5.6.6 より前では、許可される値は 0 (または OFF) および 1 (または ON) です。

    パスワードハッシュ方式 関連付けられた認証プラグイン
    0 MySQL 4.1 ネイティブハッシュ mysql_native_password
    1 4.1 以前の (古い) ハッシュ mysql_old_password
    2 SHA-256 ハッシュ sha256_password

    old_passwords=1 の場合、PASSWORD(str)OLD_PASSWORD(str) と同じ値を返します。後者の関数は old_passwords の値によって影響を受けません。

    mysql> SET old_passwords = 0;
    mysql> SELECT PASSWORD('mypass'), OLD_PASSWORD('mypass');
    +-------------------------------------------+------------------------+
    | PASSWORD('mypass')                        | OLD_PASSWORD('mypass') |
    +-------------------------------------------+------------------------+
    | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | 6f8c114b58f2ce9e       |
    +-------------------------------------------+------------------------+
    
    mysql> SET old_passwords = 1;
    mysql> SELECT PASSWORD('mypass'), OLD_PASSWORD('mypass');
    +--------------------+------------------------+
    | PASSWORD('mypass') | OLD_PASSWORD('mypass') |
    +--------------------+------------------------+
    | 6f8c114b58f2ce9e   | 6f8c114b58f2ce9e       |
    +--------------------+------------------------+
    

    SHA-256 パスワードのハッシュ化 (old_passwords=2) では、PASSWORD() からの結果を非決定的にするランダムな salt 値が使用されます。その結果、この関数が使用されるステートメントはステートメントベースのレプリケーションで安全ではなく、クエリーキャッシュ内に格納できません。

    PASSWORD() で実行される暗号化は一方向です (可逆ではありません)。暗号化のタイプは、Unix パスワードに使用されるものと同じではありません。そのタイプを使用するには、ENCRYPT() を使用します。

    注記

    PASSWORD() 関数は、MySQL サーバーの認証システムで使用されます。独自のアプリケーションでは、使用しないようにしてください。そのためには、代わりに MD5() または SHA2() を使用してください。アプリケーションでのセキュアなパスワードの処理および認証セキュリティーについての詳細は、RFC 2195 のセクション 2 (「Challenge-Response Authentication Mechanism (CRAM)」)も参照してください。

    注記

    4.1 より前のハッシュ方式を使用するパスワードはネイティブのパスワードハッシュ方式を使用するパスワードよりもセキュアでないため、使用しないようにしてください。4.1 よりも前のパスワードは非推奨であり、これらのサポートは今後の MySQL リリースで削除される予定です。その結果、PASSWORD() で 4.1 以前のパスワードハッシュを生成する old_passwords=1 も非推奨となります。アカウントのアップグレード手順については、セクション6.3.8.3「4.1 よりも前のパスワードハッシュ方式と mysql_old_password プラグインからの移行」を参照してください。

    注意

    状況によっては、サーバーログまたはクライアント側の ~/.mysql_history などの履歴ファイルに、PASSWORD() を呼び出すステートメントが記録される可能性があります。これは、その情報への読み取りアクセス権を持っているユーザーならだれでも、平文のパスワードを読み取る可能性があることを意味します。これがサーバーログで発生する条件およびこれを制御する方法については、セクション6.1.2.3「パスワードおよびロギング」を参照してください。クライアント側のロギングに関する同様の情報については、セクション4.5.1.3「mysql のロギング」を参照してください。

  • RANDOM_BYTES(len)

    この関数は、SSL ライブラリ (OpenSSL または yaSSL) の乱数ジェネレータを使用して生成されたランダムな len バイトのバイナリ文字列を返します。len で許可されている値は、1 から 1024 までの範囲内です。値がこの範囲外の場合、RANDOM_BYTES() は警告を生成し、NULL を返します。

    RANDOM_BYTES() を使用すると、AES_DECRYPT() および AES_ENCRYPT() 関数に初期化ベクトルを提供できます。このコンテキストで使用するには、len を 16 以上にする必要があります。さらに大きい値も許可されますが、16 を超えるバイトは無視されます。

    RANDOM_BYTES() は、その結果を非決定的にするランダムな値を生成します。その結果、この関数が使用されるステートメントはステートメントベースのレプリケーションに対して安全ではなく、クエリーキャッシュ内に格納できません。

    この関数は、MySQL 5.6.17 の時点で使用可能です。

  • SHA1(str)SHA(str)

    RFC 3174 (Secure Hash Algorithm) で説明されているように、文字列の SHA-1 160 ビットチェックサムを計算します。この値は、40 桁の 16 進数の文字列として返されます。引数が NULL だった場合は、NULL になります。この関数を使用する一例として、ハッシュ鍵が考えられます。効率的なハッシュ値の格納については、このセクションの冒頭で示した注記を参照してください。SHA1() は、パスワードを格納する暗号化関数としても使用できます。SHA()SHA1() のシノニムです。

    戻り値は、接続文字セット内の非バイナリ文字列です。

    mysql> SELECT SHA1('abc');
            -> 'a9993e364706816aba3e25717850c26c9cd0d89d'
    

    SHA1()MD5() と同等ですが、暗号化に関してはよりセキュアであると考えられます。ただし、このセクションの冒頭で示した MD5 と SHA-1 アルゴリズムに関する注記を参照してください。

  • SHA2(str, hash_length)

    SHA-2 ファミリのハッシュ関数 (SHA-224、SHA-256、SHA-384、および SHA-512) を計算します。1 番目の引数は、ハッシュ化される平文の文字列です。2 番目の引数には、結果の目的のビット長が指定されます。値は、224、256、384、512、または 0 (256 と同等です) にする必要があります。引数のいずれかが NULL の場合や、ハッシュの長さが許可される値のいずれでもない場合は、戻り値が NULL になります。それ以外の場合は、関数の結果が目的のビット数が含まれるハッシュ値になります。効率的なハッシュ値の格納については、このセクションの冒頭で示した注記を参照してください。

    戻り値は、接続文字セット内の非バイナリ文字列です。

    mysql> SELECT SHA2('abc', 224);
            -> '23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7'
    

    この関数は、MySQL が SSL サポートで構成されている場合のみ機能します。セクション6.3.10「セキュアな接続のための SSL の使用」を参照してください。

    SHA2()MD5()SHA1() よりも、暗号化に関してはよりセキュアであると考えられます。

  • UNCOMPRESS(string_to_uncompress)

    COMPRESS() 関数で圧縮された文字列を圧縮解除します。引数が圧縮された値でない場合は、結果が NULL になります。この関数を使用するには、MySQL が zlib などの圧縮ライブラリを使用してコンパイルされている必要があります。そうでない場合、戻り値は常に NULL になります。

    mysql> SELECT UNCOMPRESS(COMPRESS('any string'));
            -> 'any string'
    mysql> SELECT UNCOMPRESS('any string');
            -> NULL
    
  • UNCOMPRESSED_LENGTH(compressed_string)

    圧縮された文字列が圧縮される前の長さを返します。

    mysql> SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a',30)));
            -> 30
    
  • VALIDATE_PASSWORD_STRENGTH(str)

    平文のパスワードを表す引数が指定された場合、この関数はパスワードの強度を示す整数を返します。戻り値は、0 (弱) から 100 (強) までの範囲内です。

    パスワードは、一段と厳密になったテストの対象であり、戻り値は、次の表に示すように、どのテストに合格したのかを示します。

    パスワードテスト 戻り値
    長さ < 4 0
    長さ ≥ 4 および < validate_password_length 25
    ポリシー 1 を満たす (LOW) 50
    ポリシー 2 を満たす (MEDIUM) 75
    ポリシー 3 を満たす (STRONG) 100

    VALIDATE_PASSWORD_STRENGTH() によるパスワードの評価は、validate_password プラグインで実行されます。このプラグインがインストールされていない場合、関数は常に 0 を返します。validate_password プラグインのインストールについては、セクション6.1.2.6「パスワード検証プラグイン」を参照してください。パスワードのテストに影響を与えるパラメータを確認または構成するには、validate_password プラグインで実装されているシステム変数をチェックまたは設定します。セクション6.1.2.6.2「パスワード検証プラグインのオプションおよび変数」を参照してください。

    この関数は、MySQL 5.6.6 で追加されました。


User Comments
  Posted by Ralf Hauser on January 14, 2005
before storing an AES key to the server, please investigate whether it will be swapped out to disk on that server or not (http://bugs.mysql.com/bug.php?id=7846)
  Posted by Patrick Denny on January 20, 2005
Note,
ENCODE and DECODE don't seem to accept a row name as the second argument. so the following WILL FAIL:

select * from `table_name` where `encrypted_row` = ENCODE('passed_value',`salt_row`)

however, the first argument can be a row name, as follows:

select * from `table_name` where `encrypted_row` = ENCODE(`salt_row`,'passed_value')

  Posted by Stewart Smith on February 11, 2005
The first example of using md5 to store passwords to a web app is not ideal. It is vulnerable to dictonary attacks.

For a start, the users password may pass over the network (if your web app and mysql server are on different machines). If you're not using SSL to mysql, then this will be in plain text. PHP has an md5 function, it may be better to use that (especially if this is a secure web app running over SSL).

If I were to get a dump of your password table, and I had a list of pre-computed md5 sums for possible passwords, i could quite easily do a compare to see if any user has a password in my list.

The way the UNIX password file (now) does it is to add some 'salt' to the password. You add an extra field to your password table, 'salt'. This is a random string (generated each time the user changes their password). This salt is stored in plain text. When you are computing the md5 of the password, you prepend (or append, it doesn't matter - as long as you're consistent) the salt to the password. e.g. md5($salt . $password). When they try to log in, you do the same thing md5($salt . $entered_password). If that equals the value of the password field in the database, you allow access!

this means that if an attacker gets a dump of your password table, they are going to have to get their list of passwords and md5 sum every single one with every single salt value (in your table) to do a dictionary attack.

Instantly you now have better security!
  Posted by Robert Nice on April 28, 2005
If I understand correctly that the keys to all these algorithms are sent in plain text to the SQL server so that it can perform the crypto then using any of these is insanely dangerous.

Most likely your keys will end up in update logs, packet sniffer logs, replication logs, error logs....who knows.

Do the crypto in your application before inserting and after selecting. THERE SHOULD BE A BIG WARNING ABOUT THIS.
  Posted by Rolf Martin-Hoster on March 10, 2006
AES_ENCRYPT: if strlen(str) % 16 == 0 then AES_ENCRYPT will add an axtra block of chr(16). This is particularly useful to know when trying to use PHP's mcrypt.
  Posted by Mark Hedges on April 7, 2006
That's totally correct about the use of SQL encryption functions. If you don't use a localhost or SSL connection to your database server, the plaintext and key string are sent in the clear and encryption does not protect any data from attackers along that path.

One way to protect more sensitive information, for instance, credit card numbers, is to use GnuPG to encrypt the data with the public part of a key whose private counterpart only lives on a very secure machine that runs the batch transaction, and requires a passphrase to load it into memory in your charging program. The encrypted block is stored in a text field, the plaintext never crosses the network, and an attack on the web server or database cannot compromise the data.

The slightly stronger exception might be the use of DES_ENCRYPT, which if you have configured your keyring on your server, does not need to transmit the locking key with the plaintext data. (Though it still transmits the plaintext in the clear.)

Also, regarding the mentioned exploit of sha1, there are stronger versions like sha256, sha384, sha512 etc. but mysql does not implement them; they would have to be implemented in code.
  Posted by Philip Mather on April 22, 2007
If you've implemented SSL, see...

http://dev.mysql.com/doc/refman/5.0/en/secure-create-certs.html

...you can use Triggers and DES_ENCRYPT to move your password encryption to the database level and enforce it in a way that stops developers forgeting to use it (or bypassing it) with the following triggers...

CREATE TRIGGER user_insert BEFORE INSERT ON `user` FOR EACH ROW SET NEW.TimeStampCreated = NOW(), NEW.Password = DES_ENCRYPT(NEW.Password);

CREATE TRIGGER user_update BEFORE UPDATE ON `user` FOR EACH ROW SET NEW.Password = DES_ENCRYPT(NEW.Password);

...you'll also notice the first one enforces auditing in a way that saves you from relying on developers getting that right as well.

You could give your dev's a nice stored proc to retrieve or comapre their submitted password but hopefully they can remember either DES_ENCRYPT/_DECRYPT or your phone number ;^).

Whilst bearing in mind that this doesn't magically make your entire system "secure" by some magic wave of a wand, given that you've implemented SSL it should be trivial to secure the link between web and database server (if there even is a gap) and then you can use HTTPS and only a little more careful thought to implement a system that is secure from submission page through to backup system in such a way that only someone physically stood at the server with the server's and Mysql's root password could decrypt the password/data.
  Posted by Steve Brendtro on October 22, 2007
If you are using MySQL to store any sort of encrypted credit card information, note that you will want NOT want to do the encryption using the built in encryption functions, as encryption key AND the CLEAR TEXT VERSION OF THE DATA will end up in your replication logs, and possibly error logs. Do your encryption in your application.
  Posted by John Bayly on February 11, 2008
As noted in Bug #16713 (AES_Encrypt / AES_Decrypt functions is low documented), the full specs of the encryption function are not given, so Steve Brendtro's suggestion made it difficult to implement a client side AES_ENCRYPT method.
It took a lot of searching to come across the bug report whilst trying to find the option when using the MS .Net RijndaelManaged methods, so I thought I'd share the code:

public byte[] AESEncrypt(byte[] plaintext, byte[] key) {
/* Simulate MySQL AES_ENCRYPT function
* Block Length: 128bit
* Block Mode: ECB
* Data Padding: Padded by bytes which Asc() equal for number of padded bytes (done automagically)
* Key Padding: 0x00 padded to multiple of 16 bytes
* IV: None
*/
RijndaelManaged aes = new RijndaelManaged();
aes.BlockSize = 128;
aes.Mode = CipherMode.ECB;
aes.Key = key;

// Create the Encrypter & streams needed
ICryptoTransform encryptor = aes.CreateEncryptor();
MemoryStream mem = new MemoryStream();
CryptoStream cryptStream = new CryptoStream(mem, encryptor,
CryptoStreamMode.Write);

// Write the Plaintext & flush
cryptStream.Write(plaintext, 0, plaintext.Length);
cryptStream.FlushFinalBlock();

// Get the encrypted bytes
byte[] cypher = mem.ToArray();

// Tidy up
cryptStream.Close();
cryptStream = null;
encryptor.Dispose();
aes = null;

return cypher;
}

Hopefully this will help anyone who's been trying to get around this issue.
  Posted by Pongrac Nemeth on November 5, 2008
Shorter MD5 :) here in my idea:
In a MySQL function:

declare $s char(32);
set $s=md5($word);
return concat(conv(substr($s,1,16),16,36),'x',conv(substr($s,17),16,36));

'x' must be a char which is not in result of conv()!!!
'x' must NOT be 0-9 and A-Z ! For example '-' is also good.

It is nice, not much slower then alone md5 function.
Length of this is 25-27 characters instead of 32.
(Max. 27 (2x13+1) because length of conv('FFFFFFFFFFFFFFFF',16,36) is 13!)

  Posted by Devon McCullough on February 2, 2009
MySQL UNCOMPRESS sans MySQL - yes, generic *n*x tools can recover the data.

Using only non-MySQL tools:

wget http://www.zlib.net/zpipe.c
gcc -o zpipe zpipe.c -lz
mysql -B -e "SELECT HEX(COMPRESS('Test data!'))" | xxd -r -ps | dd bs=1 skip=4 2>/dev/null | ./zpipe -d; echo
Test data!

Wrap it with HEX (mysql is binary-unsafe)
and unwrap it with xxd
then drop the header with dd
and uncompress with zpipe, i.e.,
(0) start with the string 'Test data!'
(1) COMPRESS compresses it to binary
(2) HEX renders it to text
(3) xxd reverts it to binary
(4) dd discards the UNCOMPRESSED_LENGTH header
(5) zpipe uncompresses the rest
(6) echo adds a newline

Repeat using MySQL:

mysql -B -e "SELECT HEX(COMPRESS('Test data!'))" | tail +2 | mysql -B -e "SELECT UNCOMPRESS(UNHEX('`cat`'))" | tail +2
Test data!

i.e.,
(7) same as 0-2
(8) tail discards the echoed SQL command
(9) cat obtains the piped data
(10) UNHEX same as 3
(11) UNCOMPRESS same as 4-5
(12) tail same as 8

Nice to have the added safety of a second source.
  Posted by Shamus Husheer on November 23, 2009
One of the main benefits of the encryption features in MySQL, as opposed to in the language of your choice, is that Stored Procedures can be used to perform encryption without exposing keys to the client. At http://www.duofertility.com we transmit medical data between client and database, for example, so secure authentication is critical - however cryptographic authentication is not included in MySQL natively.

However the below implements HMAC-MD5 with 128-bit keys (see FRC2104) as a Stored Procedure. The keytable has "id" and "key", the calling code simply passes the "id" and the message to HMACMD5, and is returned the HMAC as a binary string. Access to the keytable must be limited, but access to call HMAC-MD5 can be given out freely.

Modifying the size of the key and the hash function would yield HMAC-SHA1, however beware that binary XOR only operates on up to 64 bit values, hence the convoluted ipad/opad generation.

DELIMITER //
CREATE PROCEDURE HMACMD5(IN keynumber INTEGER, IN message BLOB, OUT output BINARY(16))
BEGIN
DECLARE ipad,opad BINARY(64);
DECLARE hexkey CHAR(32);

SELECT LPAD(HEX(`key`),32,"0") INTO hexkey FROM `keytable` WHERE `id` = keynumber;

SET ipad = UNHEX(CONCAT(LPAD(RIGHT(CONV(CONV( MID(hexkey,1,11) , 16, 10 ) ^ CONV( '36363636363', 16, 10 ),10,16),11),11,"0"),
LPAD(RIGHT(CONV(CONV( MID(hexkey,12,11) , 16, 10 ) ^ CONV( '63636363636' , 16, 10 ),10,16),11),11,"0"),
LPAD(RIGHT(CONV(CONV( MID(hexkey,23,10) , 16, 10 ) ^ CONV( '3636363636' , 16, 10 ),10,16),10),10,"0"),'363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636'));

SET opad = UNHEX(CONCAT(LPAD(RIGHT(CONV(CONV( MID(hexkey,1,11) , 16, 10 ) ^ CONV( '5c5c5c5c5c5', 16, 10 ),10,16),11),11,"0"),
LPAD(RIGHT(CONV(CONV( MID(hexkey,12,11) , 16, 10 ) ^ CONV( 'c5c5c5c5c5c' , 16, 10 ),10,16),11),11,"0"),
LPAD(RIGHT(CONV(CONV( MID(hexkey,23,10) , 16, 10 ) ^ CONV( '5c5c5c5c5c' , 16, 10 ),10,16),10),10,"0"),'5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c'));

SET output = UNHEX(MD5(CONCAT(opad,UNHEX(MD5(CONCAT(ipad,message))))));

END //
DELIMITER ;

  Posted by Fernando Claudio dos Santos Junior on November 4, 2010
Hi people,

These functions in few steps helps crypt in SSHA.

DROP FUNCTION IF EXISTS fc_ssha_encode;
DELIMITER /
CREATE FUNCTION fc_ssha_encode(_senha VARCHAR(255))
RETURNS CHAR (46)
DETERMINISTIC
BEGIN
/*
Funcao para criptografar em Salted SHA {SSHA}
Muito útil para LDAP com MySQL Backend.

Por Fernando Claudio dos Santos Junior (04/11/2010)

Inspirado no Post de vovó Vicki (http://www.numaboa.com/criptografia/codigos/codigos-abertos/492-base64)
e no Utilitário Javascript UTF-8 Decoder and Encoder - base64 Encoder and Decoder de Tobias Kieslich.

Uso livre.
Sem qualquer garantia de funcionamento ou seguranca.
*/
DECLARE MAPA CHAR(64);
DECLARE SALT CHAR(10);
DECLARE SALTBITS CHAR(80);
DECLARE B_ALEATORIO CHAR(8);
DECLARE C_ALEATORIO CHAR(1);
DECLARE SHASED CHAR(40);
DECLARE SHASEDBITS CHAR(160);
DECLARE SSHABITS CHAR(240);
DECLARE SSHA CHAR(46);
DECLARE CONT TINYINT UNSIGNED;

SET MAPA = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/'; #BASE64

/* Passo 1) Gerar um salt aleatorio com 10 caracteres. Obs.: 10 é importante para garantir o tamanho final da senha criptografada. */
/* Passo 2) Coverter caracter por caracter do salt em byte com 8 bits cada. */
SET SALT = '';
SET SALTBITS = '';
SET CONT = 1;
WHILE (CONT < 11) DO
SET B_ALEATORIO = CONCAT(ROUND(RAND()), ROUND(RAND()), ROUND(RAND()), ROUND(RAND()), ROUND(RAND()), ROUND(RAND()));
SET C_ALEATORIO = SUBSTRING(MAPA, CONV(B_ALEATORIO,2,10)+1, 1);
SET SALT = CONCAT(SALT, C_ALEATORIO);
SET SALTBITS = CONCAT(SALTBITS,LPAD(CONV(ORD(C_ALEATORIO),10,2),8,'0'));
SET CONT = CONT + 1;
END WHILE;

/*
#FORCE SALT (para obter hash constante)
SET SALTBITS = '01110011010101100110110000110010010000010110010001100100010010010110111001000110';
SET SALT = 'sVl2AddInF';
*/

/* Passo 3) Criptografar utilizando SHA1(), o salt gerado irá garantir distintos resultados a cada tentativa. */
SET SHASED = SHA1(CONCAT(_senha, SALT));

/* Passo 4) Obter bits do hash gerado pelo SHA1(), para isso converter 2 a 2 caracteres de hexadecimal para base binaria, em byte com 8 bits cada. */
SET SHASEDBITS = '';
SET CONT = 1;
WHILE CONT < 40 DO
SET SHASEDBITS = CONCAT(SHASEDBITS, LPAD(CONV(SUBSTRING(SHASED,CONT,2),16,2),8,'0'));
SET CONT = CONT + 2;
END WHILE;

/* Passo 5) Obter todos bits da senha criptografada em SSHA, juntar bits do Passo4 com os bits do Passo2. */
SET SSHABITS = CONCAT(SHASEDBITS,SALTBITS);

/* Passo 6) Transformar bits do Passo5 em BASE64, para isso ler de 6 bits em 6 bits, comparado o respectivo valor decimal com a posicao no MAPA. */
SET SSHA = '{SSHA}';
SET CONT = 1;
WHILE CONT < 240 DO
SET SSHA = CONCAT(SSHA,SUBSTRING(MAPA,CONV(SUBSTRING(SSHABITS,CONT,6),2,10)+1,1));
SET CONT = CONT + 6;
END WHILE;

RETURN SSHA;
END /

DELIMITER ;

DROP FUNCTION IF EXISTS fc_bind_ssha_password;
DELIMITER /
CREATE FUNCTION fc_bind_ssha_password(_senha VARCHAR(255), _hash VARCHAR(255))
RETURNS VARCHAR (10)
DETERMINISTIC
BEGIN
/*
Funcao para validar senhas criptografadas com Salted SHA {SSHA}
Muito útil para LDAP com MySQL Backend.

Por Fernando Claudio dos Santos Junior (04/11/2010)

Inspirado no Post de vovó Vicki (http://www.numaboa.com/criptografia/codigos/codigos-abertos/492-base64)
e no Utilitário Javascript UTF-8 Decoder and Encoder - base64 Encoder and Decoder de Tobias Kieslich.

Uso livre.
Sem qualquer garantia de funcionamento ou seguranca.
*/
DECLARE MAPA CHAR(64);
DECLARE SSHA, SHASED CHAR(40);
DECLARE SSHABITS, SSHABITS2 CHAR(240);
DECLARE SALTBITS CHAR(80);
DECLARE SALT CHAR(10);
DECLARE SHASEDBITS CHAR(160);
DECLARE CONT TINYINT UNSIGNED;
SET MAPA = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/'; #BASE64

/* Passo 1) Tranformar caracter em caracter do _hash em bytes de 6 bits, de acordo com o MAPA Base64 */

SET SSHA = SUBSTRING(_hash, 7, 40);
SET SSHABITS = '';
SET CONT = 1;
WHILE CONT < 41 DO
SET SSHABITS = CONCAT(SSHABITS, LPAD( CONV( POSITION(SUBSTRING(SSHA,CONT,1) IN CONVERT(MAPA USING BINARY))-1, 10, 2),6,'0'));
SET CONT = CONT + 1;
END WHILE;

/* Passo 2) Pegar bits apartir da posicao 161 a 240 */
SET SALTBITS = SUBSTRING(SSHABITS,161,80);

/* Passo 3) Obter salt, Ler de 8 em 8 bits, transformado o octeto em caracteres */
SET SALT = '';
SET CONT = 1;
WHILE CONT < 80 DO
SET SALT = CONCAT(SALT, CHAR(CONV(SUBSTRING(SALTBITS,CONT,8), 2, 10)));
SET CONT = CONT + 8;
END WHILE;

/* Passo 4) Criptografar senha testada com salt encontrado no rash, transformar em seguida, 2 a 2 de hexadecimal para binario. */
SET SHASED = SHA1(CONCAT(_senha, SALT));
SET SHASEDBITS = '';
SET CONT = 1;
WHILE CONT < 40 DO
SET SHASEDBITS = CONCAT(SHASEDBITS, LPAD(CONV(SUBSTRING(SHASED,CONT,2),16,2),8,'0'));
SET CONT = CONT + 2;
END WHILE;

/* Passo 5) Obter todos bits da senha criptografada em SSHA, juntar bits do Passo4 com os bits do Passo2. */
SET SSHABITS2 = CONCAT(SHASEDBITS,SALTBITS);

/* Passo 6) Testar se os hashs conferem */
RETURN (SSHABITS2 = SSHABITS);
END /
DELIMITER ;

/* Testando: */

>SET @_HASH = `fc_ssha_encode`('y0uR_Pa$$W0Rd');

>SELECT @_HASH AS SSHA_HASH, `fc_bind_ssha_password`('y0uR_Pa$$W0Rd',@_HASH) AS VALIDATED;
  Posted by Jake Gelbman on December 17, 2010
Ive wrote a function that can be used to generate an arbitrary length base64 encoded value. Here it is:

create function hex2b64 (hex text)
returns text
comment 'Converts a string containing hex values into base64'
deterministic
begin
declare b64set text default
"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789./";
declare bin text default '';
declare b64 text default '';
declare chars int;
declare chip int;
declare n int default 0;
declare d char(1);
-- mysql's conv function has a limit on the length of the hex string
-- it can convert in one shot, so convert it one character at a time.
HEX2BIN: loop
if n = length(hex) then
leave HEX2BIN;
end if;
set n = n + 1;
set d = substr(hex, n, 1);
set bin = concat(bin, conv(d, 16, 2));
end loop;
-- Chip away at the binary representation of the hex string 6 bits at
-- a time. 6 bits => 2**6 => base64. The binary number can then be
-- used as an index into b64set to get the next base64 character.
B64DIGIT: loop
set chars = length(bin);
if !chars then
leave B64DIGIT;
end if;
set chip = if(chars % 6, chars % 6, 6);
set n = conv(substr(bin, 1, chip), 2, 10);
set d = substr(b64set, n + 1, 1);
set b64 = concat(b64, d);
set bin = substr(bin, chip + 1);
end loop;
return b64;
end

And it can be used as:

mysql> select hex2b64('deadbeef');
+---------------------+
| hex2b64('deadbeef') |
+---------------------+
| Derb7v |
+---------------------+
1 row in set (0.00 sec)

mysql> select hex2b64(md5('deadbeef'));
+--------------------------+
| hex2b64(md5('deadbeef')) |
+--------------------------+
| T5pxPtaep/diRuXm |
+--------------------------+
1 row in set (0.00 sec)

Might be useful...
  Posted by Le Datica on February 12, 2012
I found a good site that explains how to use AES_ENCRYPT(), AES_DECRYPT(). Here is the link to it

http://thinkdiff.net/mysql/encrypt-mysql-data-using-aes-techniques/
  Posted by Dan Cappannari on November 13, 2014
If you have data which you used ENCODE on with earlier ODBC connectors such as 3.51, and find that DECODE fails to recover data on later ODBC connectors such as 5.1 (some characters show, most do not), change:

DECODE(field, 'key') to:

CONVERT(DECODE(field, 'key') USING latin1)
Sign Up Login You must be logged in to post a comment.