DECRYPT_BINARY, DECRYPT_BIT, DECRYPT_CHAR, and DECRYPT_DB

The decryption functions return a value that is the result of decrypting encrypted data. The decryption functions can decrypt only values that are encrypted by using the ENCRYPT_TDES function.

>>-+-DECRYPT_BINARY-+-(encrypted-data-+---------------------------------------------+-)-><
   +-DECRYPT_BIT----+                 '-,-+-password-string-+-+-------------------+-'     
   +-DECRYPT_CHAR---+                     '-DEFAULT---------' '-,--ccsid-constant-'       
   '-DECRYPT_DB-----'                                                                     

The schema is SYSIBM.

The password used for decryption is either the password-string value or the ENCRYPTION PASSWORD value, which is assigned by the SET ENCRYPTION PASSWORD statement.

encrypted-data
An expression that returns a complete, encrypted data value of a CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY data type. The data string must have been encrypted using the ENCRYPT_TDES function. The length attribute must be greater than or equal to 0 (zero) and less than or equal to 32672.
password-string
An expression that returns a CHAR or VARCHAR value with at least 6 bytes and no more than 127 bytes. This expression must be the same password that was used to encrypt the data or decryption will result in a different value than was originally encrypted. For enhanced security, password-string should be specified using a host variable rather than a string constant. If the value of the password argument is null or not provided, the data will be decrypted using the ENCRYPTION PASSWORD value, which must have been assigned by the SET ENCRYPTION PASSWORD statement.

For a static SQL statement, it is recommended that the password be specified with a host variable rather than with a string constant.

DEFAULT
The data is decrypted using the ENCRYPTION PASSWORD value, which must have been assigned by the SET ENCRYPTION PASSWORD statement.
ccsid-constant
A integer constant that specifies the CCSID in which the data should be returned by the decryption function. If DECRYPT_BIT or DECRYPT_BINARY is specified, ccsid-constant must not be specified. The default is
  • The ENCODING bind option of the plan or package or the APPLICATION ENCODING SCHEMA option of the CREATE PROCEDURE or ALTER PROCEDURE statement for native SQL procedures that contain the static SQL statements
  • The value of the APPLICATION ENCODING special register for dynamic SQL statements
The data type of the result of the function is determined by the function that is specified and the data type of the first argument, as shown in the following table. If the cast from the actual type of the encrypted data to the result of the function is not supported, a warning or error is returned.
Table 1. Result of the decryption function
Function Type of first argument Actual type of encrypted data Result
DECRYPT_BINARY FOR BIT DATA1, BINARY, VARBINARY Any string (except for LOBs) VARBINARY
DECRYPT_BIT FOR BIT DATA, BINARY, VARBINARY CHAR, VARCHAR VARCHAR FOR BIT DATA
DECRYPT_BIT FOR BIT DATA, BINARY, VARBINARY GRAPHIC, VARGRAPHIC (UTF16) Warning or error

If a warning is returned, the result is VARCHAR FOR BIT DATA
DECRYPT_BIT FOR BIT DATA, BINARY, VARBINARY GRAPHIC, VARGRAPHIC (not UTF16) Warning or error

If a warning is returned, the result is VARCHAR FOR BIT DATA
DECRYPT_BIT FOR BIT DATA, BINARY, VARBINARY BINARY, VARBINARY Warning or error

If a warning is returned, the result is VARCHAR FOR BIT DATA
DECRYPT_CHAR FOR BIT DATA, BINARY, VARBINARY CHAR, VARCHAR VARCHAR(3)
DECRYPT_CHAR FOR BIT DATA, BINARY, VARBINARY GRAPHIC, VARGRAPHIC (UTF16) VARCHAR(3)
DECRYPT_CHAR FOR BIT DATA, BINARY, VARBINARY GRAPHIC, VARGRAPHIC (not UTF16) Warning or error

If a warning is returned, the result is VARCHAR(3)
DECRYPT_CHAR FOR BIT DATA, BINARY, VARBINARY BINARY, VARBINARY Warning or error

If a warning is returned, the result is VARCHAR(3)
DECRYPT_DB FOR BIT DATA, BINARY, VARBINARY CHAR, VARCHAR, GRAPHIC, VARGRAPHIC VARGRAPHIC
DECRYPT_DB FOR BIT DATA, BINARY, VARBINARY BINARY, VARBINARY Warning or error

If a warning is returned, the result is VARGRAPHIC
Note: 1 FOR BIT DATA means CHAR or VARCHAR FOR BIT DATA

If encrypted-data included a hint, the hint is not returned by the function. The length attribute of the result is the length attribute of encrypted-data minus 8 bytes. The actual length of the value that is returned by the function will match the length of the original string that was encrypted. If encrypted-data includes bytes beyond the encrypted string, these bytes are not returned by the function.

Administration of encrypted data: The decryption functions can only decrypt data that was encrypted using the Triple DES encryption algorithm. Therefore, columns with encrypted data can only be used after replication if they were encrypted using the Triple DES encryption algorithm.

If the data is decrypted using a different CCSID than the originally encrypted value, it is possible that expansion might occur when converting the decrypted value to this CCSID. In such situations, the encrypted-data value must first be cast to a VARCHAR string with a larger number of bytes before performing the decryption functions.

The result can be null; if the first argument is null, the result is the null value.

For additional information about using the decryption functions, see ENCRYPT_TDES and GETHINT.

Password protection: To prevent inadvertent access to the encryption password, do not specify password-string as a string constant in the source statement. Instead, use the ENCRYPTION PASSWORD special register or specify the password using a host variable.

Example 1: Set the ENCRYPTION PASSWORD value to 'Ben123' and use it as the password to insert a decrypted social security number into the table. Decrypt the value of the added social security number, using the ENCRYPTION PASSWORD value. 
   SET ENCRYPTION PASSWORD ='Ben123';
   INSERT INTO EMP(SSN) VALUES ENCRYPT_TDES('289-46-8832');
   SELECT DECRYPT_CHAR(SSN) FROM EMP;

This example returns the value '289-46-8832'.

Example 2: Decrypt the social security number that is inserted into the table. Instead of using the ENCRYPTION PASSWORD value, explicitly specify 'Ben123' as the encryption password. 
   SELECT DECRYPT_CHAR(SSN,'Ben123') FROM EMP;

This example returns the value '289-46-8832'.

Example 3: Insert a decrypted social security number into the table, explicitly specifying 'Ben123' as the password. Decrypt the data and have it converted to CCSID 1208. 
   SET ENCRYPTION PASSWORD ='Ben123';
   INSERT INTO EMP(SSN) VALUES ENCRYPT_TDES('289-46-8832');
   SELECT DECRYPT_CHAR(SSN) FROM EMP;
When a CCSID is specified, it might be necessary to explicitly cast the data to a longer value to ensure that there is room for expansion when the data is decrypted. The following example illustrates the technique: 
   SELECT DECRYPT_CHAR(CAST(SSN AS VARCHAR(57)),
                        'Ben123',1208) 
                  FROM EMP;

In the first case, where the data is not cast to a longer value, the result is a VARCHAR(11) value. In the second case, to allow for expansion, SSN is cast as VARCHAR(57) (11 * 3 + 24). Casting the data to a longer value allows for three times expansion in the normal VARCHAR(11) result. Three times expansion is often associated with a worst case of ASCII or EBCDIC to Unicode UTF-8 conversion. In both cases in this example, the result is the VARCHAR(11) value '289-46-8832'.