ENCRYPT_TDES Function

The ENCRYPT_TDES function returns a value that is the result of encrypting a character expression, or a BLOB or CLOB value, by applying the TDES (Triple Data Encryption Standard, which is sometimes also called DES3) algorithm to its first argument. This algorithm is slower than the DES algorithm that is used by the ENCRYPT_AES function, but is considered somewhat more secure. The disk space required as encryption overhead resembles that of ENCRYPT_AES, but is somewhat smaller because of the smaller block size of ENCRYPT_TDES. (See "Calculating storage requirements for encrypted data" *** for a discussion of how to estimate the size of encrypted character strings.)

Those differences in performance, tamper-resistance, and in the returned encrypted_data size that the previous paragraph lists are the practical differences between the ENCRYPT_TDES and ENCRYPT_AES functions, which otherwise follow the same rules, defaults, and restrictions that appear in the description of ENCRYPT_AES on the previous page in regard to the following features:

• The required first argument (the plain text data value to be encrypted)
• The explicit or default second argument (the password string that must also be an argument to DECRYPT_CHAR or DECRYPT_BINARY to decrypt the returned encrypted_data value). This must be specified unless a default session password has been set by the SET ENCRYPTION statement
• The optional third argument (the hint value) that might assist users who forget the password. If you subsequently cannot remember an explicit or default hint that was defined for password, you can use the returned value from ENCRYPT_TDES as the argument to GETHINT to retrieve the hint.

The following example calls ENCRYPT_TDES from the SET clause of an UPDATE statement. Here the session password is 'PERSEPHONE' and the hint string is "pomegranate", with column colU of table tabU the data argument. Because the WHERE clause condition of "1=1" is true for all rows of tabU, the effect of this statement is to replace every plain text colU value with encrypted strings returned by the algorithm that ENCRYPT_TDES implements:

EXEC SQL SET ENCRYPTION PASSWORD 'PERSEPHONE' WITH HINT 'pomegranate'
EXEC SQL UPDATE tabU SET colU = ENCRYPT_TDES (colU) WHERE 1=1;

This example assumes that the character data type of colU is of sufficient size to store the new encrypted values without truncation. (A more cautious example might execute an appropriate ALTER TABLE statement before the UPDATE.)

For additional information about using data encryption in column values of Dynamic Server databases, see Encryption and Decryption Functions, and SET ENCRYPTION PASSWORD.