SAP Sybase IQ ensures that the original data type of 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.
SAP Sybase IQ compares the target data type of the CAST function with the data type of the originally encrypted data. If the two data types do not match, you see a -1001064 error that includes details about the original and target data types.
For example, given an encrypted VARCHAR(1) value and this 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 the CAST or the data type parameter are supplied. Otherwise, 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’))
the data type of 1 is ambiguous; it can be a TINYINT, SMALLINT, INTEGER, UNSIGNED INT, BIGINT, UNSIGNED BIGINT, or possibly other data types.
You should explicitly use 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.