DECRYPT_BIT, DECRYPT_BINARY, DECRYPT_CHAR and DECRYPT_DB
The DECRYPT_BIT, DECRYPT_BINARY, DECRYPT_CHAR, and DECRYPT_DB functions return a value that is the result of decrypting encrypted data. The password used for decryption is either the password-string value or the ENCRYPTION PASSWORD value assigned by the SET ENCRYPTION PASSWORD statement.
The decryption functions can only decrypt values that are encrypted using the ENCRYPT_AES, ENCRYPT_RC2, or ENCRYPT_TDES function.
- encrypted-data
- An expression that must be a string expression that returns a complete, encrypted data value of a CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, VARBINARY, or BLOB built-in data type. The data string must have been encrypted using the ENCRYPT_AES, ENCRYPT_RC2, or ENCRYPT_TDES function.
- password-string
- An expression that returns a character string value with at least 6 bytes and no more than 127 bytes. The expression must not be a CLOB. This expression must be the same password used to encrypt the data or decryption will result in a different value than was originally encrypted. 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 set using the SET ENCRYPTION PASSWORD statement.
- DEFAULT
- The data will be decrypted using the ENCRYPTION PASSWORD value, which must have been set using the SET ENCRYPTION PASSWORD statement.
- integer
- An integer constant that specifies the CCSID of the result. If
DECRYPT_BIT or DECRYPT_BINARY is specified, the third argument must
not be specified.
If DECRYPT_CHAR is specified, integer must be a valid SBCS CCSID or mixed data CCSID. It cannot be 65535 (bit data). If the third argument is an SBCS CCSID, then the result is SBCS data. If the third argument is a mixed CCSID, then the result is mixed data. If the third argument is not specified then the CCSID of the result is the default CCSID of the current server.
If DECRYPT_DB is specified, integer must be a valid DBCS CCSID. If the third argument is not specified then the CCSID of the result is the DBCS CCSID associated with the default CCSID of the current server.
The data type of the result is determined by the function specified and the data type of the first argument as shown in the following table. If a cast from the actual type of the encrypted data to the function's result is not supported a warning or error is returned.
Function | Data Type of First Argument | Actual Data Type of Encrypted Data | Result |
---|---|---|---|
DECRYPT_BIT | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Character string | VARCHAR FOR BIT DATA |
DECRYPT_BIT | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Graphic string | Error or Warning ** |
DECRYPT_BIT | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Binary string | Error or Warning ** |
DECRYPT_BIT | BLOB | Any string | Error |
DECRYPT_BINARY | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Any string | VARBINARY |
DECRYPT_BINARY | BLOB | Any string | BLOB |
DECRYPT_CHAR | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Character string | VARCHAR |
DECRYPT_CHAR | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Unicode graphic string | VARCHAR |
DECRYPT_CHAR | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Non-Unicode graphic string | Error or Warning ** |
DECRYPT_CHAR | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Binary string | Error or Warning ** |
DECRYPT_CHAR | BLOB | Character string | CLOB |
DECRYPT_CHAR | BLOB | Unicode graphic string | CLOB |
DECRYPT_CHAR | BLOB | Non-Unicode graphic string | Error or Warning ** |
DECRYPT_CHAR | BLOB | Binary string | Error or Warning ** |
DECRYPT_DB | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | UTF-8 character string or graphic string | VARGRAPHIC |
DECRYPT_DB | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Non-UTF-8 character string | Error or Warning ** |
DECRYPT_DB | CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, BINARY, or VARBINARY | Binary string | Error or Warning ** |
DECRYPT_DB | BLOB | UTF-8 character string or graphic string | DBCLOB |
DECRYPT_DB | BLOB | Non-UTF-8 character string | Error or Warning ** |
DECRYPT_DB | BLOB | Binary string | Error or Warning ** |
Note:
** If the decryption function is in the select list of an outer subselect, a data mapping warning is returned. Otherwise an error is returned. For more information about data mapping warnings, see Assignments and comparisons. |
If the encrypted-data included a hint, the hint is not returned by the function. The length attribute of the result is the length attribute of the data type of encrypted-data minus 8 bytes. The actual length of the result is the length of the original string that was encrypted. If the encrypted-data includes bytes beyond the encrypted string, these bytes are not returned by the function.
If the argument can be null, the result can be null; if the argument is null, the result is the null value.
If the data is decrypted using a different CCSID than the originally encrypted value, expansion may occur when converting the decrypted value to this CCSID. In such situations, the encrypted-data should be cast to a varying-length string with a larger number of bytes.
Note
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 ENCRYPTION PASSWORD special register or a host variable.
When connected to a remote relational database, the specified password itself is sent "in the clear". That is, the password itself is not encrypted. To protect the password in these cases, consider using a communications encryption mechanism such as IPSEC (or SSL if connecting between IBM® i products).
Syntax alternatives: For compatibility with previous versions of Db2®, DECRYPT_BIN can be specified in place of DECRYPT_BIT.
Examples
- Assume that table EMP1 has a social security column called SSN.
This example uses the ENCRYPTION PASSWORD value to hold the encryption
password.
The DECRYPT_CHAR function returns the original value '289-46-8832'.SET ENCRYPTION PASSWORD = :pw INSERT INTO EMP1 (SSN) VALUES ENCRYPT_RC2( '289-46-8832' ) SELECT DECRYPT_CHAR( SSN) FROM EMP1
- This example explicitly passes the encryption password which
has been set in variable pw.
The DECRYPT_CHAR function returns the original value '289-46-8832'.INSERT INTO EMP1 (SSN) VALUES ENCRYPT_TDES( '289-46-8832', :pw) SELECT DECRYPT_CHAR( SSN, :pw) FROM EMP1