Encryption Functions
Encryption functions encrypt data stored in tables. For more information, see the Security Guide.
AES_ENCRYPT_IV
AES_ENCRYPT_IV(column_name,'encryption_passphrase'[,128|192|256])
Operand type: VARCHAR. Compatible types are automatically converted to VARCHAR.
Result type: VARCHAR
Encrypts the values to be inserted or updated in a table. The data is encrypted using a random initialization vector (IV) with cipher block chaining (CBC) mode.
The data can be read only by using the AES_DECRYPT_IV function, supplying the encryption passphrase. The key size, if not specified, defaults to 128.
The resulting string is a 7-bit ASCII string.
An error message will be generated in case the 'encryption_passphrase' is NULL.
NULL would be returned if 'column_name' is NULL.
Examples:
SELECT * FROM mytable WHERE column = AES_ENCRYPT_IV('secret_value', 'passphrase')
INSERT INTO socsec2 SELECT
fname, lname,
AES_ENCRYPT_IV(socsec,'user function encryption')
FROM socsec1
The following will fail because the encrypted result cannot be converted to an integer because it contains non-numeric characters:
CREATE TABLE table1 (a INT);
INSERT INTO table1 VALUES (AES_ENCRYPT_IV(2, '278435'))
The following, however, will work:
CREATE TABLE table2 (a VARCHAR(100));
INSERT INTO table2 VALUES (AES_ENCRYPT_IV(2, '278435'))
Caution! Do not truncate the result when storing because decryption will fail. For information on error handling for string truncation, see
String Truncation.
WARNING! Data will be silently lost if the result of the function is longer than the defined column width. Use the following calculation to determine how much space the resulting cipher will require: cipher_length = ( (string_length in bytes) / 16 + 2 ) * 20.
AES_DECRYPT_IV
AES_DECRYPT_IV(column_name,'encryption_passphrase'[,128|192|256])
Operand type: VARCHAR
Result type: VARCHAR
Decrypts data (allows encrypted data to be read in plain text) that has been encrypted with the AES_DECRYPT_IV function by supplying the encryption passphrase. The key size, if not specified, defaults to 128.
An error message will be generated in case the 'encryption_passphrase' is NULL.
NULL would be returned if 'column_name' is NULL.
Example:
SELECT columnname, columnname,
AES_DECRYPT_IV(columnname,'encryption_passphrase') AS columnname
FROM table2
Note: You may need to explicitly cast the VARCHAR result back to the original encrypted input data type.
AES_ENCRYPT
AES_ENCRYPT(column_name,'encryption_passphrase'[,KEY_SIZE=128|192|256])
Operand type: Any character type. The function accepts only VARBYTE, so compatible types are automatically converted to VARBYTE. If the value being encrypted is integer, float or another data type incompatible with VARBYTE, it is necessary to explicitly cast the data.
Result type: VARBYTE
Encrypts the values to be inserted or updated in a table. The data can be read only by using the AES_DECRYPT function, supplying the encryption passphrase. The key size, if not specified, defaults to 128.
An error message will be generated in case the 'encryption_passphrase' is NULL.
NULL would be returned if 'column_name' is NULL.
Example:
INSERT INTO socsec2 SELECT
fname, lname,
AES_ENCRYPT(socsec,'user function encryption')
FROM socsec1
AES_DECRYPT
AES_DECRYPT(column_name,'encryption_passphrase'[,KEY_SIZE=128|192|256])
Operand type: VARBYTE
Result type: VARBYTE
Decrypts data (allows encrypted data to be read in plain text) by supplying the encryption passphrase. The key size, if not specified, defaults to 128.
An error message will be generated in case the 'encryption_passphrase' is NULL.
NULL would be returned if 'column_name' is NULL.
Example:
SELECT columnname, columnname,
AES_DECRYPT(columnname,'encryption_passphrase') AS columnname
FROM table2