Data Types, Encoding, and Size of Encrypted Values

The data and corresponding encrypted_data arguments can be of any built-in character type (CHAR, LVARCHAR, NCHAR, NVARCHAR, or VARCHAR), or can be a smart large object of type BLOB or CLOB. (Use CLOB in place of TEXT, which these functions do not support.)

Corresponding data and encrypted_data values that the encryption or decryption functions return have the same character, BLOB, or CLOB data type, except in cases where encryption of a VARCHAR or NVARCHAR string would return an overflow error. For operations on CHAR, LVARCHAR, NCHAR, NVARCHAR, or VARCHAR data and on encrypted_data values, the encryption and decryption functions follow the data-type promotion rules of CONCAT and the other SQL string manipulation functions for the data type of their return value, as in the following examples.

If the VARCHAR data argument to ENCRYPT_TDES (with no hint) is a 200 byte string, then Informix® automatically promotes the returned value to an LVARCHAR data type, because the encrypted value exceeds the 255 byte limit for VARCHAR objects.
If the NVARCHAR encrypted_data argument to ENCRYPT_AES (with a hint) is a string 200 bytes long, then Informix automatically promotes the returned value to an NCHAR data type, because the encrypted value exceeds the 255 byte limit for NVARCHAR objects.

For more information about return type promotion for character strings that the encryption, decryption, and certain other string-manipulation functions return, see Return Types from the CONCAT Function. The first table in that section describes data or encrypted_data arguments that are not smart large objects. (For smart large object arguments, the return type is a BLOB or CLOB object.)

The encryption or decryption function call returns overflow error -881, however, if the return value exceeds the 32,767-byte limit for CHAR, NCHAR strings, or the 32,739-byte limit for LVARCHAR strings. To avoid this error, use BLOB or CLOB objects as the data or encrypted_data argument, rather than a character data type, when the encryption or decryption operation requires an argument or a return value that might be larger than the (approximately 32Kb) limit for character data types.

Except for original data of BLOB or CLOB data types, the encrypted_data value is encoded in BASE64 format. An encrypted value requires more space than the corresponding plain text, because the database must also store the information (except for the encryption key) that is needed for decryption. If a hint is used, it adds to the length of encrypted_data.

The BASE64 encoding scheme stores 6 bits of input data as 8 bits of output. To encode N bytes of data, BASE64 requires at least ((4N+3)/3) bytes of storage, where the slash character ( / ) represents integer division. Padding and headers can increase BASE64 storage requirements above this ((4N+3)/3) ratio. Example of Column Level Encryption lists formulae to estimate the size of data values encrypted in BASE64 format. It typically requires changes to the schema of an existing table that will store BASE64 format encrypted data, especially if a hint will also be stored.

The following table shows how the data type of the input string corresponds to the data type of the value that ENCRYPT_AES or ENCRYPT_TDES returns:

Table 1. Data Types for ENCRYPT_AES and ENCRYPT_TDES Functions
Plain Text Data Type	Encrypted Data Type	Decryption Function
CHAR	CHAR	DECRYPT_CHAR
NCHAR	NCHAR	DECRYPT_CHAR
VARCHAR	VARCHAR or CHAR	DECRYPT_CHAR
NVARCHAR	NVARCHAR or NCHAR	DECRYPT_CHAR
LVARCHAR	LVARCHAR	DECRYPT_CHAR
BLOB	BLOB	DECRYPT_BINARY
CLOB	BLOB	DECRYPT_CHAR

Columns of type VARCHAR and NVARCHAR store no more than 255 bytes. If the data string is too long for these data types to store both the encrypted data and encryption overhead, then the value returned by the encryption function is automatically changed from VARCHAR or NVARCHAR into a fixed CHAR or NCHAR value, with no trailing blanks in the encoded encrypted value.

Encrypted values of type BLOB or CLOB are not in BASE64 encoding format, and their size increase after encryption is independent of the original data size. For BLOB or CLOB values, the encrypted size (in bytes) has the following formula, where N is the original size of the plain text, and H is the size of the unencrypted hint string, if encryption is performed by ENCRYPT_TDES:

N + H + 24 bytes.

For BLOB or CLOB values that ENCRYPT_AES encrypts, the overhead is larger:

N + H + 32 bytes.