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_ENCRYPT_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.
Note: AES_ENCRYPT_IV and AES_DECRYPT_IV functions are not suited for BYTE and VARBYTE arguments.
AES_ENCRYPT_BINARY_IV
AES_ENCRYPT_BINARY_IV(column_name,'encryption_passphrase'[,128|192|256])
Operand type: VARBYTE. Other data types must be explicitly converted to VARBYTE before encryption.
Result type: VARBYTE
Encrypts the binary 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_BINARY_IV function, supplying the encryption passphrase. The key size, if not specified, defaults to 128.
The resulting string is a VARBYTE which may contain any byte values including '\0'.
An error message will be generated in case the 'encryption_passphrase' is NULL.
NULL is returned if 'column_name' is NULL.
Example:
INSERT INTO socsec2 SELECT
fname, lname,
AES_ENCRYPT_BINARY_IV(socsec,'mypassphrase')
FROM socsec1
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 = (plain length in bytes + 1) rounded up to the nearest multiple of 16 +16.
AES_DECRYPT_BINARY_IV
AES_DECRYPT_BINARY_IV(column_name,'encryption_passphrase'[,128|192|256])
Operand type: VARBYTE
Result type: VARBYTE
Decrypts data (allows encrypted data to be read in plain text) that has been encrypted with the AES_ENCRYPT_BINARY_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 is returned if 'column_name' is NULL.
Example:
SELECT columnname, columnname,
AES_DECRYPT_BINARY_IV(columnname,'encryption_passphrase') AS columnname
FROM table2
Note: You may need to explicitly cast the VARBYTE 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
Using the AES_ENCRYPT Function
The AES_ENCRYPT function accepts as input a string of type VARBYTE and encrypts the entire string, including the 2-byte prefix that holds the VARBYTE length. It does not add an initialization vector to the resulting cipher.
To compute the length of the encrypted cipher of the AES_ENCRYPT function, use the following algorithm:
1. Start with the length of the input as VARBYTE (that is, after casting to that data type if the original input is of another type).
2. Add 2 for the length prefix.
3. Round up to the nearest multiple of 16.
If you store AES_ENCRYPT encrypted data in a database table, be sure to allocate sufficient space for the full encrypted data length. Truncated encrypted data cannot be decrypted successfully.
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
Note: It is recommended to use AES_ENCRYPT_BINARY_IV and AES_DECRYPT_BINARY_IV functions as they provide greater security.