DECRYPT_BIN and DECRYPT_CHAR scalar functions

The schema is SYSIBM.

The DECRYPT_BIN and DECRYPT_CHAR functions both return a value that is the result of decrypting encrypted-data. The password used for decryption is either the password-string-expression value or the encryption password value that was assigned by the SET ENCRYPTION PASSWORD statement. To maintain the best level of security on your system, it is recommended that you do not pass the encryption password explicitly with the DECRYPT_BIN and DECRYPT_CHAR functions in your query; instead, use the SET ENCRYPTION PASSWORD statement to set the password, and use a host variable or dynamic parameter markers when you use the SET ENCRYPTION PASSWORD statement, rather than a literal string.

The DECRYPT_BIN and DECRYPT_CHAR functions can only decrypt values that are encrypted using the ENCRYPT function (SQLSTATE 428FE).

encrypted-data

An expression that returns a CHAR FOR BIT DATA or VARCHAR FOR BIT DATA value as a complete, encrypted data string. The data string must have been encrypted using the ENCRYPT function.

password-string-expression

An expression that returns a CHAR or VARCHAR value with at least 6 bytes and no more than 127 bytes (SQLSTATE 428FC). This expression must be the same password used to encrypt the data (SQLSTATE 428FD). If the value of the password argument is null or not provided, the data will be decrypted using the encryption password value that was assigned for the session by the SET ENCRYPTION PASSWORD statement (SQLSTATE 51039).

The result of the DECRYPT_BIN function is VARCHAR FOR BIT DATA. The result of the DECRYPT_CHAR function is VARCHAR. If encrypted-data included a hint, the hint is not returned by the function. The length attribute of the result is the length of the data type of encrypted-data minus 8 bytes. The actual length of the value 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.

If the first argument can be null, the result can be null. If the first argument is null, the result is the null value.

If the data is decrypted on a different system, which uses a code page that is different from the code page in which the data was encrypted, expansion might occur when converting the decrypted value to the database code page. In such situations, the encrypted-data value should be cast to a VARCHAR string with a larger number of bytes.

Examples

The following example demonstrates the use of the DECRYPT_CHAR function by showing code fragments from an embedded SQL application.

EXEC SQL BEGIN DECLARE SECTION;
      char hostVarCreateTableStmt[100];
      char hostVarSetEncPassStmt[200]; 
      char hostVarPassword[128];
      char hostVarInsertStmt1[200];
      char hostVarInsertStmt2[200];
      char hostVarSelectStmt1[200];
      char hostVarSelectStmt2[200];
EXEC SQL END DECLARE SECTION; 

/* prepare the statement */ 
strcpy(hostVarCreateTableStmt, "CREATE TABLE EMP (SSN VARCHAR(24) FOR BIT DATA)"); 
EXEC SQL PREPARE hostVarCreateTableStmt FROM :hostVarCreateTableStmt;

/* execute the statement */ 
EXEC SQL EXECUTE hostVarCreateTableStmt; 

Use the SET ENCRYPTION PASSWORD statement to set an encryption password for the session:

/* prepare the statement with a parameter marker */ 
strcpy(hostVarSetEncPassStmt, "SET ENCRYPTION PASSWORD = ?"); 
EXEC SQL PREPARE hostVarSetEncPassStmt FROM :hostVarSetEncPassStmt;

/* execute the statement for hostVarPassword = 'Pac1f1c' */ 
strcpy(hostVarPassword, "Pac1f1c"); 
EXEC SQL EXECUTE hostVarSetEncPassStmt USING :hostVarPassword; 

/* prepare the statement */ 
strcpy(hostVarInsertStmt1, "INSERT INTO EMP(SSN) VALUES ENCRYPT('289-46-8832')"); 
EXEC SQL PREPARE hostVarInsertStmt1 FROM :hostVarInsertStmt1;

/* execute the statement */ 
EXEC SQL EXECUTE hostVarInsertStmt1; 

/* prepare the statement */ 
strcpy(hostVarSelectStmt1, "SELECT DECRYPT_CHAR(SSN) FROM EMP"); 
EXEC SQL PREPARE hostVarSelectStmt1 FROM :hostVarSelectStmt1;

/* execute the statement */ 
EXEC SQL EXECUTE hostVarSelectStmt1; 

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

Pass the encryption password explicitly:

/* prepare the statement */ 
strcpy(hostVarInsertStmt2, "INSERT INTO EMP (SSN) VALUES ENCRYPT('289-46-8832',?)"); 
EXEC SQL PREPARE hostVarInsertStmt2 FROM :hostVarInsertStmt2;

/* execute the statement for hostVarPassword = 'Pac1f1c' */ 
strcpy(hostVarPassword, "Pac1f1c"); 
EXEC SQL EXECUTE hostVarInsertStmt2 USING :hostVarPassword; 

/* prepare the statement */ 
strcpy(hostVarSelectStmt2, "SELECT DECRYPT_CHAR(SSN,?) FROM EMP"); 
EXEC SQL PREPARE hostVarSelectStmt2 FROM :hostVarSelectStmt2;

/* execute the statement for hostVarPassword = 'Pac1f1c' */ 
strcpy(hostVarPassword, "Pac1f1c"); 
EXEC SQL EXECUTE hostVarSelectStmt2 USING :hostVarPassword; 

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