ENCRYPT_TDES
The ENCRYPT_TDES function returns a value that is the result of encrypting the first argument by using the Triple DES encryption algorithm. The function can also set the password that is used for encryption.
The encryption password can also be set by using the ENCRYPTION PASSWORD value, which is assigned by using the SET ENCRYPTION PASSWORD statement.
>>-ENCRYPT_TDES(data-string-+----------------------------------------+-)->< '-,--password-string--+----------------+-' '-,--hint-string-'
The schema is SYSIBM.
- data-string
- An expression that returns the string value to be
encrypted. The string expression must return a built-in string data
type that is not a LOB. The length attribute must be greater than
or equal to 0 (zero). The length attribute is limited to 32640 if hint-string is
specified and 32672 if hint-string is not
specified.
The
argument can also be a numeric data type. The numeric argument is
implicitly cast to a VARCHAR data type.
- password-string
- An expression that returns a CHAR or VARCHAR value
with at least 6 bytes and no more than 127 bytes. The
argument can also be a numeric data type. The numeric argument is
implicitly cast to a VARCHAR data type.
The value represents the password that is used to encrypt data-string. If the value of the password argument is null or not specified, the data is encrypted using the ENCRYPTION PASSWORD value, which must have been assigned by the SET ENCRYPTION PASSWORD statement.
- hint-string
- An expression that returns a CHAR or VARCHAR value up to 32 bytes
that is to help data owners remember passwords (for example, 'Ocean'
as a hint to remember 'Pacific'). The
argument can also be a numeric data type. The numeric argument is
implicitly cast to a VARCHAR data type.
If a hint value is specified, the hint is embedded into the result and can be retrieved using the GETHINT function. If this argument is null or not specified and no hint was specified when the ENCRYPTION PASSWORD was set, no hint is embedded in the result. If password-string is not specified, the hint can be specified using the SET ENCRYPTION PASSWORD statement.
| Data type of the first argument | Data type of the result |
|---|---|
| BINARY, VARBINARY | VARBINARY |
| CHAR, VARCHAR, GRAPHIC, VARGRAPHIC | VARCHAR FOR BIT DATA |
The encoding scheme of the result is the same as the encoding scheme of data-string. If the result is character data, the result is bit data.
The length attribute of the result is different depending of whether hint-string is specified:
- If hint-string is specified, the length attribute of the result is the length attribute of the non-encrypted data + 24 bytes + number of bytes to the next 8 byte boundary + 32 bytes for the hint.
- If hint-string is not specified, the length attribute of the result is the length attribute of the non-encrypted data + 24 bytes + the number of bytes to the next 8 byte boundary.
The result can be null; if the first argument is null, the result is the null value.
The encrypted result is longer than the data-string value. Therefore, when assigning encrypted values, ensure that the target is declared with a length that can contain the entire encrypted value.
When encrypting data, be aware of the following points:
- Password protection: To prevent inadvertent access to the encryption password, do not specify password-string as a string constant in the source for a program, procedure, or function. Instead, use the SET ENCRYPTION PASSWORD statement or a variable.
- Encryption algorithm: The internal encryption algorithm used is Triple DES cipher block chaining (CBC) with padding. The 128-bit secret key is derived from the password using an MD5 hash.
- Encryption passwords and data: It is your responsibility to perform password management. After data is encrypted, only the password that is used to encrypt it can be used to decrypt it. If a different password is used to decrypt the data than was used to encrypt the data, the results of decryption will not match the original string. No error or warning is returned. CHAR variables might be padded with blanks if they are used to set password values. The encrypted result might contain null terminator and other non-printable characters.
- Table column definitions: When defining columns and types
to contain encrypted data, always calculate the length attribute as
follows:
- For encrypted data with an embedded hint, the column length should be the length attribute of the non-encrypted data + 24 bytes + number of bytes to the next 8 byte boundary + 32 bytes for the hint.
- For encrypted data without an embedded hint, the column length should be the length attribute of the non-encrypted data + 24 bytes + number of bytes to the next 8 byte boundary.
Here are some sample column length calculations, which assume that a hint is not embedded:Maximum length of non-encrypted data 6 bytes 24 bytes for encryption key 24 bytes Number of bytes to the next 8 byte boundary 2 bytes --------- Encrypted data column length 32 bytes Maximum length of non-encrypted data 32 bytes 24 bytes for encryption key 24 bytes Number of bytes to the next 8 byte boundary 0 bytes --------- Encrypted data column length 56 bytes - Administration of encrypted data: Encrypted data can be decrypted only on servers that support the decryption of data that was encrypted using the Triple DES encryption algorithm. Hence, replication of columns with encrypted data should only be done to servers that support the decryption functions and the same encryption algorithms.
ENCRYPT can be specified as a synonym for ENCRYPT_TDES. DB2® supports this keyword to provide compatibility with other products in the DB2 family.
SET ENCRYPTION PASSWORD ='Ben123';
INSERT INTO EMP(SSN) VALUES ENCRYPT_TDES ('289-46-8832');INSERT INTO EMP(SSN) VALUES ENCRYPT_TDES ('289-46-8832','Ben123');INSERT INTO EMP(SSN) VALUES ENCRYPT_TDES ('289-46-8832','Pacific','Ocean');