![AES_ENCRYPT function [String]](arrow-right.gif){kind=icon}
This section lists the supported and unsupported data types for encrypted columns and discusses the preservation of the original data type of an encrypted column.
The first parameter of the AES_ENCRYPT function must be one of the following supported data types:
CHAR |
NUMERIC |
VARCHAR |
FLOAT |
TINYINT |
REAL |
SMALLINT |
DOUBLE |
INTEGER |
DECIMAL |
BIGINT |
DATE |
BIT |
TIME |
BINARY |
DATETIME |
VARBINARY |
TIMESTAMP |
UNSIGNED INT |
SMALLDATETIME |
UNSIGNED BIGINT |
The LOB data type is not currently supported for Sybase IQ column encryption.
Sybase IQ ensures that the original data type of the plaintext is preserved when decrypting data, if the AES_DECRYPT function is given the data type as a parameter, or is within a CAST function. Sybase IQ compares the target data type of the CAST with the data type of the originally encrypted data. If the two data types do not match, a -1001064 error is returned with details about the original and target data types.
For example, given an encrypted VARCHAR(1) value and the following valid decryption statement:
SELECT AES_DECRYPT ( thecolumn, ‘theKey’, VARCHAR(1) ) FROM thetable
If you attempt to decrypt the data using:
SELECT AES_DECRYPT ( thecolumn, ‘theKey’, SMALLINT ) FROM thetable
the error returned is:
Decryption error: Incorrect CAST type smallint(5,0) for decrypt data of type varchar(1,0).
This data type check is made only when supplied. Without the CAST or the data type parameter, the query returns the ciphertext as binary data.
When using the AES_ENCRYPT function
on literal constants, as in this statement:
INSERT INTO t (cipherCol) VALUES (AES_ENCRYPT (1, ‘key’))
be aware that the data type of 1 is ambiguous. The data type of 1 can be a TINYINT, SMALLINT, INTEGER, UNSIGNED INT, BIGINT, UNSIGNED BIGINT or possibly other data types.
Sybase recommends explicit use of the CAST function to resolve any potential ambiguity, as in:
INSERT INTO t (cipherCol) VALUES ( AES_ENCRYPT (CAST (1 AS UNSIGNED INTEGER), ‘key’))
Explicitly converting the data type using the CAST function when encrypting data prevents problems using the CAST function when the data is decrypted.
There is no ambiguity if the data being encrypted is from a column or if the encrypted data was inserted by LOAD TABLE.
