ENCRYPT_AES

data-string

An expression that returns the string value to be encrypted. The string expression must be a built-in string data type.

The length attribute for the data type of data-string is limited to 24 bytes (or 32 bytes) less than the maximum length of the result data type without a hint-string argument and 56 bytes (or 64 bytes) less than the maximum length of the result data type when the hint-string argument is specified.

password-string

An expression that returns a character string value with at least 6 bytes and no more than 127 bytes. The expression must not be a CLOB and the CCSID of the expression must not be 65535. The value represents the password used to encrypt the data-string. If the value of the password argument is null or not provided, the data will be encrypted using the ENCRYPTION PASSWORD value, which must have been set using the SET ENCRYPTION PASSWORD statement.

hint-string

An expression that returns a character string value with up to 32 bytes that will help data owners remember passwords (For example, 'Ocean' is a hint to remember 'Pacific'). The expression must not be a CLOB and the CCSID of the expression must not be 65535. If a hint value is specified, the hint is embedded into the result and can be retrieved using the GETHINT function. If the password-string is specified and this argument is the null value or not provided, no hint will be embedded in the result. If the password-string is not specified, the hint may be specified using the SET ENCRYPTION PASSWORD statement.

The data type of the result is determined by the first argument as shown in the following table:

The length attribute of the result depends on the arguments that are specified:

• When a password-string is specified but a hint-string is not specified, the length attribute of data-string plus 24 plus the number of bytes to a 16 byte boundary.
• Otherwise, the length attribute of data-string plus 64 plus the number of bytes to a 16 byte boundary.

If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.

Note that the encrypted result is longer than the data-string value. Therefore, when assigning encrypted values, ensure that the target is declared with sufficient size to contain the entire encrypted value.

Notes

Password protection: To prevent inadvertent access to the encryption password, do not specify password-string as a string constant in the source for a program, procedure, or function. Instead, use the SET ENCRYPTION PASSWORD statement or a host variable.

When connected to a remote relational database, the specified password itself is sent "in the clear". That is, the password itself is not encrypted. To protect the password in these cases, consider using a communications encryption mechanism such as IPSEC (or SSL if connecting between IBM® i products).

Encryption algorithm: The internal encryption algorithm used is from the CLiC Toolkit from IBM Research. The 128-bit encryption key is derived from the password using a SHA1 message digest.

Encryption passwords and data: It is the user's responsibility to perform password management. Once the data is encrypted only the password used to encrypt it can be used to decrypt it. Be careful when using CHAR variables to set password values as they may be padded with blanks. The encrypted result may contain a null terminator and other non-printable characters.

Table column definition: When defining columns and distinct types to contain encrypted data:

• The column must be defined with a data type of CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, VARBINARY, or BLOB.
• The length attribute of the column must include an additional n bytes, where n is the overhead necessary to encrypt the data as described above.

Any assignment or cast to a column without one of these data types or with a length shorter than the suggested data length may result in an assignment error or, if the assignment is successful, a failure and lost data when the data is subsequently decrypted. Blanks are valid encrypted data values that may be truncated when stored in a column that is too short.

Some sample column length calculations:

Maximum length of non-encrypted data           6 bytes
Number of bytes to the next 16 byte boundary  10 bytes 
Overhead                                      24 bytes (or 32 bytes)
                                              --------
Encrypted data column length                  40 bytes (or 48 bytes)


Maximum length of non-encrypted data          32 bytes
Number of bytes to a 16 byte boundary          0 bytes 
Overhead                                      24 bytes (or 32 bytes)
                                              --------
Encrypted data column length                  56 bytes

Administration of encrypted data: Encrypted data can only be decrypted on servers that support the decryption functions that correspond to the ENCRYPT_AES function. Hence, replication of columns with encrypted data should only be done to servers that support the decryption functions.