Calculating storage requirements for encrypted data

The LENGTH function provides a convenient way to calculate the storage requirements of encrypted data directly:

EXECUTE FUNCTION LENGTH(ENCRYPT_TDES("1234567890123456", "simple password"));
This returns 55.
EXECUTE FUNCTION LENGTH(ENCRYPT_TDES("1234567890123456", "simple password", 
"12345678901234567890123456789012"));
This returns 107.
EXECUTE FUNCTION LENGTH(ENCRYPT_AES("1234567890123456", "simple password"));
This returns 67.
EXECUTE FUNCTION LENGTH(ENCRYPT_AES("1234567890123456", "simple password", 
"12345678901234567890123456789012"));
This returns 119.
The required storage size for encrypted data is sensitive to three factors:
  • N, the number of bytes in the plain text
  • whether or not a hint is provided
  • which encryption function you use (ENCRYPT_TDES or ENCRYPT_TDES)
The following formulae describe the four possible cases, and are not simplified:
  • Encryption by ENCRYPT_TDES( ) with no hint:
    Encrypted size = (4 x ((8 x((N + 8)/8) + 10)/3) + 11)
  • Encryption by ENCRYPT_AES( ) with no hint:
    Encrypted size = (4 x ((16 x((N + 16)/16) + 10)/3) + 11)
  • Encryption by ENCRYPT_TDES( ) with a hint:
    Encrypted size = (4 x ((8 x((N + 8)/8) + 50)/3) + 11)
  • Encryption by ENCRYPT_AES( ) with a hint:
    Encrypted size = (4 x ((16 x((N + 16)/16) + 50)/3) + 11)
The integer division ( / ) returns an integer quotient and discards any remainder.
Based on these formulae, the following table shows the encrypted size (in bytes) for selected ranges of values of N:
N ENCRYPT_TDES No Hint ENCRYPT_AES No Hint ENCRYPT_TDES With Hint ENCRYPT_AES With Hint
1 to 7 35 43 87 99
8 to 15 43 43 99 99
16 to 23 55 67 107 119
24 to 31 67 67 119 119
32 to 39 75 87 131 139
40 to 47 87 87 139 139
100 163 171 215 227
200 299 299 355 355
500 695 707 747 759

If the column size is smaller than the data size returned by encryption functions, the encrypted value is truncated when it is inserted. In this case, it will not be possible to decrypt the data, because the header will indicate that the length should be longer than the data value that the column contains.

These formulae and the values returned by the LENGTH function, however, indicate that the table schema in the next example can store the encrypted form of 16-digit credit card numbers (with a hint).


Copyright© 2019 HCL Technologies Limited