ENCRYPT_AES Function

The ENCRYPT_AES function returns an encrypted value that it derives by applying the AES (Advanced Encryption Standard) algorithm to its first argument, which must be an unencrypted character expression or a smart large object (that is, a BLOB or CLOB data type). A character argument can have a length of up to 32640 bytes if an explicit or default hint is used, or 32672 bytes if no hint (or a NULL hint) is specified. Theoretical size limits on BLOB or CLOB arguments are many orders of magnitude larger, but practical limits might be imposed by your hardware, or by time required for encryption and decryption.

You must specify a password as its second argument, unless a SET ENCRYPTION statement has specified a session password, which the database server uses by default if you omit the second argument. If a session password has been set, any password that you specify overrides the session password for the returned value of this function call. The explicit or default password will also be required for any subsequent decryption of the returned encrypted value. A valid password must have at least 6 bytes but no more than 128.

You can optionally specify a hint as the third argument. If the SET ENCRYPTION statement specified a default hint for this session, and you specify no hint, that default hint is stored in an encrypted form within the returned value. Any hint that you specify overrides the default hint. A valid hint can be no longer than 32 bytes. You can use consecutive quotation marks ( '' ) to specify a NULL hint. If you specify an explicit hint, you must also specify an explicit password.

The purpose of the hint is to help users to remember the password. For example, if the password is "buggy," you might define the hint as "whip." Neither string is restricted to a single word, but the size of the hint contributes to the size of the returned value. If you subsequently cannot remember the hint, use the returned value from ENCRYPT_AES as the argument to GETHINT to retrieve the hint.

The following example calls ENCRYPT_AES from the VALUES clause of an INSERT statement that stores in tab1 a plain-text string and an encrypted_data value that ENCRYPT_AES returns from its 12-byte first argument. Here SET ENCRYPTION defines a session password and hint that are used as default second and third arguments to the ENCRYPT_AES function:

EXEC SQL SET ENCRYPTION PASSWORD 'CHARYBDIS' WITH HINT 'messina'
EXEC SQL INSERT INTO tab1 VALUES ('abcd', ENCRYPT_AES("111-222-3333"));

The call to ENCRYPT_AES fails with an error if the password argument is omitted when no session password has been set, or if the length of an explicit password argument is shorter than 6 bytes or longer than 128 bytes.

In some contexts, an error is issued if the encrypted returned value is too large to be stored by the data type that receives it.

For additional information about using data encryption in column values of Dynamic Server databases, see Encryption and Decryption Functions, and SET ENCRYPTION PASSWORD.