Effect of Different Data Types on Ciphertext

To produce identical ciphertext for different datatypes, cast the input of AES_ENCRYPT to the same data type to produce identical ciphertext.

The ciphertext produced by AES_ENCRYPT differs for two different data types given the same input value and same key. A join of two ciphertext columns that holds encrypted values of two different data types may therefore not return identical results.

For example, assume:

CREATE TABLE tablea(c1 int, c2 smallint);
INSERT INTO tablea VALUES (100,100);

The value AES_ENCRYPT(c1, 'key') differs from AES_ENCRYPT(c2,'key') and the value AES_ENCRYPT(c1,'key') differs from AES_ENCRYPT(100,'key').

To resolve this issue, cast the input of AES_ENCRYPT to the same data type. For example, the results of these code fragments are the same:

AES_ENCRYPT(c1, 'key');

AES_ENCRYPT(CAST(c2 AS INT), 'key');

AES_ENCRYPT(CAST(100 AS INT), 'key');