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).