Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.1Mb
PDF (A4) - 31.1Mb
PDF (RPM) - 29.3Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb
HTML Download (RPM) - 6.2Mb
Man Pages (TGZ) - 175.8Kb
Man Pages (Zip) - 286.0Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  Functions and Operators  /  Encryption and Compression Functions

12.13 Encryption and Compression Functions

Table 12.17 Encryption Functions

Name Description
AES_DECRYPT() Decrypt using AES
AES_ENCRYPT() Encrypt using AES
ASYMMETRIC_DECRYPT() Decrypt ciphertext using private or public key
ASYMMETRIC_DERIVE() Derive symmetric key from asymmetric keys
ASYMMETRIC_ENCRYPT() Encrypt cleartext using private or public key
ASYMMETRIC_SIGN() Generate signature from digest
ASYMMETRIC_VERIFY() Verify that signature matches digest
COMPRESS() Return result as a binary string
CREATE_ASYMMETRIC_PRIV_KEY() Create private key
CREATE_ASYMMETRIC_PUB_KEY() Create public key
CREATE_DH_PARAMETERS() Generate shared DH secret
CREATE_DIGEST() Generate digest from string
DECODE() Decodes a string encrypted using ENCODE()
DES_DECRYPT() Decrypt a string
DES_ENCRYPT() Encrypt a string
ENCODE() Encode a string
ENCRYPT() Encrypt a string
MD5() Calculate MD5 checksum
OLD_PASSWORD() (deprecated 5.6.5) Return the value of the pre-4.1 implementation of PASSWORD
PASSWORD() Calculate and return a password string
RANDOM_BYTES() Return a random byte vector
SHA1(), SHA() Calculate an SHA-1 160-bit checksum
SHA2() Calculate an SHA-2 checksum
UNCOMPRESS() Uncompress a string compressed
UNCOMPRESSED_LENGTH() Return the length of a string before compression
VALIDATE_PASSWORD_STRENGTH() Determine strength of password

Many encryption and compression functions return strings for which the result might contain arbitrary byte values. If you want to store these results, use a column with a VARBINARY or BLOB binary string data type. This will avoid potential problems with trailing space removal or character set conversion that would change data values, such as may occur if you use a nonbinary string data type (CHAR, VARCHAR, TEXT).

Some encryption functions return strings of ASCII characters: MD5(), OLD_PASSWORD(), PASSWORD(), SHA(), SHA1(), SHA2(). Their return value is a string that has a character set and collation determined by the character_set_connection and collation_connection system variables. This is a nonbinary string unless the character set is binary.

If an application stores values from a function such as MD5() or SHA1() that returns a string of hex digits, more efficient storage and comparisons can be obtained by converting the hex representation to binary using UNHEX() and storing the result in a BINARY(N) column. Each pair of hexadecimal digits requires one byte in binary form, so the value of N depends on the length of the hex string. N is 16 for an MD5() value and 20 for a SHA1() value. For SHA2(), N ranges from 28 to 32 depending on the argument specifying the desired bit length of the result.

The size penalty for storing the hex string in a CHAR column is at least two times, up to eight times if the value is stored in a column that uses the utf8 character set (where each character uses 4 bytes). Storing the string also results in slower comparisons because of the larger values and the need to take character set collation rules into account.

Suppose that an application stores MD5() string values in a CHAR(32) column:

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

To convert hex strings to more compact form, modify the application to use UNHEX() and BINARY(16) instead as follows:

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

Applications should be prepared to handle the very rare case that a hashing function produces the same value for two different input values. One way to make collisions detectable is to make the hash column a primary key.

Note

Exploits for the MD5 and SHA-1 algorithms have become known. You may wish to consider using another one-way encryption function described in this section instead, such as SHA2().

Caution

Passwords or other sensitive values supplied as arguments to encryption functions are sent in cleartext to the MySQL server unless an SSL connection is used. Also, such values will appear in any MySQL logs to which they are written. To avoid these types of exposure, applications can encrypt sensitive values on the client side before sending them to the server. The same considerations apply to encryption keys. To avoid exposing these, applications can use stored procedures to encrypt and decrypt values on the server side.

  • AES_DECRYPT(crypt_str,key_str[,init_vector])

    This function decrypts data using the official AES (Advanced Encryption Standard) algorithm. For more information, see the description of AES_ENCRYPT().

    The optional initialization vector argument, init_vector, is available as of MySQL 5.6.17. As of that version, statements that use AES_DECRYPT() are unsafe for statement-based replication and cannot be stored in the query cache.

  • AES_ENCRYPT(str,key_str[,init_vector])

    AES_ENCRYPT() and AES_DECRYPT() implement encryption and decryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as Rijndael. The AES standard permits various key lengths. By default these functions implement AES with a 128-bit key length. As of MySQL 5.6.17, key lengths of 196 or 256 bits can be used, as described later. The key length is a trade off between performance and security.

    AES_ENCRYPT() encrypts the string str using the key string key_str and returns a binary string containing the encrypted output. AES_DECRYPT() decrypts the encrypted string crypt_str using the key string key_str and returns the original cleartext string. If either function argument is NULL, the function returns NULL.

    The str and crypt_str arguments can be any length, and padding is automatically added to str so it is a multiple of a block as required by block-based algorithms such as AES. This padding is automatically removed by the AES_DECRYPT() function. The length of crypt_str can be calculated using this formula:

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

    For a key length of 128 bits, the most secure way to pass a key to the key_str argument is to create a truly random 128-bit value and pass it as a binary value. For example:

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

    A passphrase can be used to generate an AES key by hashing the passphrase. For example:

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

    Do not pass a password or passphrase directly to crypt_str, hash it first. Previous versions of this documentation suggested the former approach, but it is no longer recommended as the examples shown here are more secure.

    If AES_DECRYPT() detects invalid data or incorrect padding, it returns NULL. However, it is possible for AES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key is invalid.

    As of MySQL 5.6.17, AES_ENCRYPT() and AES_DECRYPT() permit control of the block encryption mode and take an optional init_vector initialization vector argument:

    • The block_encryption_mode system variable controls the mode for block-based encryption algorithms. Its default value is aes-128-ecb, which signifies encryption using a key length of 128 bits and ECB mode. For a description of the permitted values of this variable, see Section 5.1.7, “Server System Variables”.

    • The optional init_vector argument provides an initialization vector for block encryption modes that require it.

    For modes that require the optional init_vector argument, it must be 16 bytes or longer (bytes in excess of 16 are ignored). An error occurs if init_vector is missing.

    For modes that do not require init_vector, it is ignored and a warning is generated if it is specified.

    A random string of bytes to use for the initialization vector can be produced by calling RANDOM_BYTES(16). For encryption modes that require an initialization vector, the same vector must be used for encryption and decryption.

    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                                          |
    +-----------------------------------------------+

    The following table lists each permitted block encryption mode, the SSL libraries that support it, and whether the initialization vector argument is required.

    Block Encryption Mode SSL Libraries that Support Mode Initialization Vector Required
    ECB OpenSSL, yaSSL No
    CBC OpenSSL, yaSSL Yes
    CFB1 OpenSSL Yes
    CFB8 OpenSSL Yes
    CFB128 OpenSSL Yes
    OFB OpenSSL Yes

    As of MySQL 5.6.17, statements that use AES_ENCRYPT() or AES_DECRYPT() are unsafe for statement-based replication and cannot be stored in the query cache.

  • COMPRESS(string_to_compress)

    Compresses a string and returns the result as a binary string. This function requires MySQL to have been compiled with a compression library such as zlib. Otherwise, the return value is always NULL. The compressed string can be uncompressed with 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

    The compressed string contents are stored the following way:

    • Empty strings are stored as empty strings.

    • Nonempty strings are stored as a 4-byte length of the uncompressed string (low byte first), followed by the compressed string. If the string ends with space, an extra . character is added to avoid problems with endspace trimming should the result be stored in a CHAR or VARCHAR column. (However, use of nonbinary string data types such as CHAR or VARCHAR to store compressed strings is not recommended anyway because character set conversion may occur. Use a VARBINARY or BLOB binary string column instead.)

  • DECODE(crypt_str,pass_str)

    Decrypts the encrypted string crypt_str using pass_str as the password. crypt_str should be a string returned from ENCODE().

  • DES_DECRYPT(crypt_str[,key_str])

    Decrypts a string encrypted with DES_ENCRYPT(). If an error occurs, this function returns NULL.

    This function works only if MySQL has been configured with SSL support. See Section 6.4, “Using Encrypted Connections”.

    If no key_str argument is given, DES_DECRYPT() examines the first byte of the encrypted string to determine the DES key number that was used to encrypt the original string, and then reads the key from the DES key file to decrypt the message. For this to work, the user must have the SUPER privilege. The key file can be specified with the --des-key-file server option.

    If you pass this function a key_str argument, that string is used as the key for decrypting the message.

    If the crypt_str argument does not appear to be an encrypted string, MySQL returns the given crypt_str.

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

    Encrypts the string with the given key using the Triple-DES algorithm.

    This function works only if MySQL has been configured with SSL support. See Section 6.4, “Using Encrypted Connections”.

    The encryption key to use is chosen based on the second argument to DES_ENCRYPT(), if one was given. With no argument, the first key from the DES key file is used. With a key_num argument, the given key number (0 to 9) from the DES key file is used. With a key_str argument, the given key string is used to encrypt str.

    The key file can be specified with the --des-key-file server option.

    The return string is a binary string where the first character is CHAR(128 | key_num). If an error occurs, DES_ENCRYPT() returns NULL.

    The 128 is added to make it easier to recognize an encrypted key. If you use a string key, key_num is 127.

    The string length for the result is given by this formula:

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

    Each line in the DES key file has the following format:

    key_num des_key_str

    Each key_num value must be a number in the range from 0 to 9. Lines in the file may be in any order. des_key_str is the string that is used to encrypt the message. There should be at least one space between the number and the key. The first key is the default key that is used if you do not specify any key argument to DES_ENCRYPT().

    You can tell MySQL to read new key values from the key file with the FLUSH DES_KEY_FILE statement. This requires the RELOAD privilege.

    One benefit of having a set of default keys is that it gives applications a way to check for the existence of encrypted column values, without giving the end user the right to decrypt those values.

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

    Encrypt str using pass_str as the password. The result is a binary string of the same length as str. To decrypt the result, use DECODE().

    The ENCODE() function should no longer be used. If you still need to use ENCODE(), a salt value must be used with it to reduce risk. For example:

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

    A new random salt value must be used whenever a password is updated.

  • ENCRYPT(str[,salt])

    Encrypts str using the Unix crypt() system call and returns a binary string. The salt argument must be a string with at least two characters or the result will be NULL. If no salt argument is given, a random value is used.

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

    ENCRYPT() ignores all but the first eight characters of str, at least on some systems. This behavior is determined by the implementation of the underlying crypt() system call.

    The use of ENCRYPT() with the ucs2, utf16, utf16le, or utf32 multibyte character sets is not recommended because the system call expects a string terminated by a zero byte.

    If crypt() is not available on your system (as is the case with Windows), ENCRYPT() always returns NULL.

  • MD5(str)

    Calculates an MD5 128-bit checksum for the string. The value is returned as a string of 32 hexadecimal digits, or NULL if the argument was NULL. The return value can, for example, be used as a hash key. See the notes at the beginning of this section about storing hash values efficiently.

    The return value is a string in the connection character set.

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

    This is the RSA Data Security, Inc. MD5 Message-Digest Algorithm.

    See the note regarding the MD5 algorithm at the beginning this section.

  • OLD_PASSWORD(str)

    OLD_PASSWORD() was added when the implementation of PASSWORD() was changed in MySQL 4.1 to improve security. OLD_PASSWORD() returns the value of the pre-4.1 implementation of PASSWORD() as a string, and is intended to permit you to reset passwords for any pre-4.1 clients that need to connect to your MySQL server without locking them out. See Section 6.1.2.4, “Password Hashing in MySQL”.

    The return value is a string in the connection character set.

    Note

    Passwords that use the pre-4.1 hashing method are less secure than passwords that use the native password hashing method and should be avoided. Pre-4.1 passwords are deprecated and support for them will be removed in a future MySQL release. Consequently, OLD_PASSWORD() is also deprecated.

  • PASSWORD(str)

    Returns a hashed password string calculated from the cleartext password str. The return value is a string in the connection character set, or NULL if the argument is NULL. This function is the SQL interface to the algorithm used by the server to encrypt MySQL passwords for storage in the mysql.user grant table.

    The old_passwords system variable controls the password hashing method used by the PASSWORD() function. It also influences password hashing performed by CREATE USER and GRANT statements that specify a password using an IDENTIFIED BY clause.

    The following table shows, for each password hashing method, the permitted value of old_passwords and which authentication plugins use the hashing method. These values are permitted as of MySQL 5.6.6. Before 5.6.6, the permitted values are 0 (or OFF) and 1 (or ON).

    Password Hashing Method old_passwords Value Associated Authentication Plugin
    MySQL 4.1 native hashing 0 mysql_native_password
    Pre-4.1 (old) hashing 1 mysql_old_password
    SHA-256 hashing 2 sha256_password
    Note

    Passwords that use the pre-4.1 hashing method are less secure than passwords that use the native password hashing method and should be avoided. Pre-4.1 passwords are deprecated and support for them will be removed in a future MySQL release. Consequently, old_passwords=1, which causes PASSWORD() to generate pre-4.1 password hashes, is also deprecated. For account upgrade instructions, see Section 6.5.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.

    If old_passwords=1, PASSWORD(str) returns the same value as OLD_PASSWORD(str). The latter function is not affected by the value of 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 password hashing (old_passwords=2) uses a random salt value, which makes the result from PASSWORD() nondeterministic. Consequently, statements that use this function are not safe for statement-based replication and cannot be stored in the query cache.

    Encryption performed by PASSWORD() is one-way (not reversible). It is not the same type of encryption as used for Unix passwords; for that, use ENCRYPT().

    Note

    PASSWORD() is used by the authentication system in MySQL Server; you should not use it in your own applications. For that purpose, consider a more secure function such as AES_ENCRYPT() or SHA2() instead. Also see RFC 2195, section 2 (Challenge-Response Authentication Mechanism (CRAM)), for more information about handling passwords and authentication securely in your applications.

    Caution

    Under some circumstances, statements that invoke PASSWORD() may be recorded in server logs or on the client side in a history file such as ~/.mysql_history, which means that cleartext passwords may be read by anyone having read access to that information. For information about the conditions under which this occurs for the server logs and how to control it, see Section 6.1.2.3, “Passwords and Logging”. For similar information about client-side logging, see Section 4.5.1.3, “mysql Logging”.

  • RANDOM_BYTES(len)

    This function returns a binary string of len random bytes generated using the random number generator of the SSL library. Permitted values of len range from 1 to 1024. For values outside that range, RANDOM_BYTES() generates a warning and returns NULL.

    RANDOM_BYTES() can be used to provide the initialization vector for the AES_DECRYPT() and AES_ENCRYPT() functions. For use in that context, len must be at least 16. Larger values are permitted, but bytes in excess of 16 are ignored.

    RANDOM_BYTES() generates a random value, which makes its result nondeterministic. Consequently, statements that use this function are unsafe for statement-based replication and cannot be stored in the query cache.

    This function is available as of MySQL 5.6.17.

  • SHA1(str), SHA(str)

    Calculates an SHA-1 160-bit checksum for the string, as described in RFC 3174 (Secure Hash Algorithm). The value is returned as a string of 40 hexadecimal digits, or NULL if the argument was NULL. One of the possible uses for this function is as a hash key. See the notes at the beginning of this section about storing hash values efficiently. SHA() is synonymous with SHA1().

    The return value is a string in the connection character set.

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

    SHA1() can be considered a cryptographically more secure equivalent of MD5(). However, see the note regarding the MD5 and SHA-1 algorithms at the beginning this section.

  • SHA2(str, hash_length)

    Calculates the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384, and SHA-512). The first argument is the cleartext string to be hashed. The second argument indicates the desired bit length of the result, which must have a value of 224, 256, 384, 512, or 0 (which is equivalent to 256). If either argument is NULL or the hash length is not one of the permitted values, the return value is NULL. Otherwise, the function result is a hash value containing the desired number of bits. See the notes at the beginning of this section about storing hash values efficiently.

    The return value is a string in the connection character set.

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

    This function works only if MySQL has been configured with SSL support. See Section 6.4, “Using Encrypted Connections”.

    SHA2() can be considered cryptographically more secure than MD5() or SHA1().

  • UNCOMPRESS(string_to_uncompress)

    Uncompresses a string compressed by the COMPRESS() function. If the argument is not a compressed value, the result is NULL. This function requires MySQL to have been compiled with a compression library such as zlib. Otherwise, the return value is always NULL.

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

    Returns the length that the compressed string had before being compressed.

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

    Given an argument representing a cleartext password, this function returns an integer to indicate how strong the password is. The return value ranges from 0 (weak) to 100 (strong).

    Password assessment by VALIDATE_PASSWORD_STRENGTH() is done by the validate_password plugin. If that plugin is not installed, the function always returns 0. For information about installing validate_password, see Section 6.5.3, “The Password Validation Plugin”. To examine or configure the parameters that affect password testing, check or set the system variables implemented by validate_password. See Section 6.5.3.2, “Password Validation Plugin Options and Variables”.

    The password is subjected to increasingly strict tests and the return value reflects which tests were satisfied, as shown in the following table.

    Password Test Return Value
    Length < 4 0
    Length ≥ 4 and < validate_password_length 25
    Satisfies policy 1 (LOW) 50
    Satisfies policy 2 (MEDIUM) 75
    Satisfies policy 3 (STRONG) 100

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  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.