MySQL in a Nutshell [48]
UPDATE teachers
SET personal = AES_ENCRYPT('text', 'my_password')
WHERE teacher_id = '730522';
Name
CURRENT_USER( )
Synopsis
CURRENT_USER( )
This function returns the username and the host that were given by the user for the current MySQL connection. There are no arguments for the function. It may not always return the same results as USER( ). Here is an example:
SELECT CURRENT_USER( ), USER( );
+-----------------+------------------+
| CURRENT_USER( ) | USER( ) |
+-----------------+------------------+
| ''@localhost | russel@localhost |
+-----------------+------------------+
In this example, the user logged in to the mysql client with the username russel (missing one “l” in the name), but because there isn’t an account for that user, the client logged in with the anonymous (i.e., '') account.
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