Example showing how to determine the size of an encrypted column

The size of the column must be large enough to store the encrypted data.

The following example shows how the size of a Credit Card column is calculated:

DATA SIZE  16 bytes
    ENCRYPTED DATA SIZE = (DATA SIZE + blocksize8) / blocksize8 * 
blocksize8 = 24 bytes (integer operation)
    OR ENCRYPTED DATA SIZE = (DATA SIZE - DATA SIZE% blocksize8 + 
blocksize8 ) = 24 bytes
    (For ENCRYPT_TDES, round up to (N + 1) * 8 bytes, for example 
13 bytes round up to 16 bytes, 16 bytes to 24 bytes)
    HEADER SIZE = 11 bytes (for Base64 encoding)
    IV SIZE = 8 bytes (fixed size)
    HINT SIZE = 32 bytes (maximum size)
    ENCRYPTED HINT SIZE = 40 bytes (maximum size)

BASE64 SIZE = ((INPUT DATA SIZE + 2) / 3) * 4    
(integer operation)
    OR BASE64 SIZE = ((INPUT DATA SIZE + 2) - 
(INPUT DATA SIZE + 2) % 3) / 3 * 4

    TOTAL SIZE = HEADER SIZE 
    + BASE64(IV SIZE + ENCRYPTED DATA SIZE + ENCRYPTED HINT) 
                         = 11 + BASE64(8 + 24 + 40) 
                = 11 + (72 + 2) / 3 * 4
                = 11 + 96 = 107

In the previous example, Initialization Vector (IV) is a pseudo-random series of bytes that is used to initiate encryption when using some cipher modes. IV size is the number of random series of bytes; for HCL OneDB™, this is 8 bytes.

If the hint is not stored in the column, the total size in the previous example is 55 bytes.

Another way to determine the encrypted column size is to calculate as follows:

SELECT LENGTH(ENCRYPT_TDES
("1234567890123456",

         "password", "long...;.hint"))

FROM "onedb".systables WHERE tabid = 1

Without the hint, you can calculate as follows:

SELECT LENGTH(ENCRYPT_TDES("1234567890123456", 
"password", ""))

FROM "onedb".systables 
WHERE tabid = 1

Important: If the column size is smaller than the returned data size from ENCRYPT and DECRYPT functions, the encrypted data is truncated when it is inserted and it is not possible to decrypt the data (because the header indicates that the length must be longer than the data received).