Encryption Functions
Encryption functions encrypt data stored in X100 tables. The encryption functions include AES_ENCRYPT_VARCHAR and AES_DECRYPT_VARCHAR. For more information, see the Security Guide.
AES_ENCRYPT_VARCHAR
AES_ENCRYPT_VARCHAR(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 can be read only by using the AES_DECRYPT_VARCHAR function, supplying the encryption passphrase. The key size, if not specified, defaults to 128.
The resulting string is a 7-bit ASCII string (no high bytes or special characters but includes quotes).
Examples:
SELECT * FROM mytable WHERE column = AES_ENCRYPT_VARCHAR(‘secret_value’, ‘passphrase’)
INSERT INTO socsec2 SELECT
fname, lname,
AES_ENCRYPT_VARCHAR(socsec,'user function encryption')
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.
AES_DECRYPT_VARCHAR
AES_DECRYPT_VARCHAR(column_name,'encryption_passphrase'[,128|192|256])
Operand type: VARCHAR
Result type: VARCHAR
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.
Example:
SELECT columnname, columnname,
AES_DECRYPT_VARCHAR(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.