MySQL in a Nutshell [38]
Name
DECODE( )
Synopsis
DECODE(string, password)
This function decrypts a given string that was encrypted with a given password. See the ENCODE( ) function later in this chapter:
SELECT ENCODE(pwd, 'oreilly')
FROM teachers
WHERE teacher_id = '730522';
This function decrypts the contents of the pwd column and unlocks it using the oreilly password, which was used to encrypt it originally using ENCODE( ).
Name
DES_DECRYPT( )
Synopsis
DES_DECRYPT(string, [key])
This function decrypts text that was encrypted using the triple Data Encryption Standard (DES) algorithm with a 128-bit key length, reversing the DES_ENCRYPT( ) function. It returns NULL if an error occurs. The function will work only if MySQL has been configured for Secure Sockets Layer (SSL) support. It is available as of version 4.0.1 of MySQL. Here is an example:
SELECT DES_DECRYPT(credit_card_nbr, 0)
FROM orders
WHERE order_nbr = '8347';
In this example, the value for the credit_card_nbr column is decrypted using the first key string in the key file. See the description of DES_ENCRYPT( ) next for more information on key files.
Name
DES_ENCRYPT( )
Synopsis
DES_ENCRYPT(string, [key])
This function returns encrypted text using the triple DES algorithm with a 128-bit key length. It returns NULL if an error occurs. The function is available as of version 4.0.1 of MySQL.
This function requires MySQL to be configured for SSL support. In addition, a key file must be created and the mysqld daemon must be started with the --des-key-file option. The key file should be set up with a separate key string on each line. Each line should begin with a single-digit number (0–9) as an index, followed by a space before the key string (e.g., key_number des_string).
The key given as the second argument to the function can either be the actual key to use for encryption or a number that refers to a key in the key file. If the second argument is omitted, the function uses the first key in the key file:
UPDATE orders
SET credit_card_nbr = DES_ENCRYPT('4011-7839-1234-4321')
WHERE order_nbr = '8347';
The results of this function can be reversed with DES_DECRYPT( ).
Name
ENCODE( )
Synopsis
ENCODE(string, password)
This function encrypts a given string in binary format and locks it with the password. You should not use this function for the password column in the user table of the mysql database. Use PASSWORD( ) instead. Here is an example:
UPDATE teachers
SET pwd = ENCODE('test', 'oreilly')
WHERE teacher_id = '730522';
The function here encrypts the word test and locks it with the oreilly password. The results are stored in the pwd column for the chosen teacher. To unlock the results, use the DECODE( ) function with the same password.
Name
ENCRYPT( )
Synopsis
ENCRYPT(string[, seed])
This function returns encrypted text using the C-language crypt function. A two-character string may be given in the second argument to increase the randomness of encryption. The resulting string cannot be decrypted. You should not use this function for the password column in the user table of the mysql database. Use PASSWORD( ) instead. Here is an example:
UPDATE teachers
SET pwd = ENCRYPT('test', 'JT')
WHERE teacher_id = '730522';
Name
MD5( )
Synopsis
MD5(string)
This function uses a Message-Digest algorithm 5 (MD5) 128-bit checksum to return a 32-character hash value of string from the Request for Comments (RFC) 1321 standard. Here is an example:
SELECT MD5('Test') AS 'MD5( ) Test';
+----------------------------------+
| MD5( ) Test |
+----------------------------------+
| 0cbc6611f5540bd0809a388dc95a615b |
+----------------------------------+
Name
OLD_PASSWORD( )
Synopsis
OLD_PASSWORD(string)
This function encrypts a given string based on the password encryption method used prior to version 4.1 of MySQL. The result cannot be decrypted. Here is an example:
UPDATE teachers
SET pwd = OLD_PASSWORD('test')
WHERE teacher_id = '730522';
Name
PASSWORD