DB2 10.5 for Linux, UNIX, and Windows

ENCRYPT scalar function

The ENCRYPT function returns a value that is the result of encrypting data-string-expression.


>>-ENCRYPT------------------------------------------------------>

>--(--data-string-expression--+--------------------------------------------------------------+--)-><
                              '-,--password-string-expression--+---------------------------+-'      
                                                               '-,--hint-string-expression-'

The schema is SYSIBM.

The password used for encryption 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 ENCRYPT function 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.

In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed.

data-string-expression: An expression that returns a CHAR or a VARCHAR value that is to be encrypted. The length attribute for the data type of data-string-expression is limited to 32663 without a hint-string-expression argument, and 32631 when the hint-string-expression argument is specified (SQLSTATE 42815).
password-string-expression: An expression that returns a CHAR or a VARCHAR value with at least 6 bytes and no more than 127 bytes (SQLSTATE 428FC). The value represents the password used to encrypt data-string-expression. If the value of the password argument is null or not provided, the data is encrypted using the encryption password value that was assigned for the session by the SET ENCRYPTION PASSWORD statement (SQLSTATE 51039).
hint-string-expression: An expression that returns a CHAR or a VARCHAR value with at most 32 bytes that will help data owners remember passwords (for example, 'Ocean' as a hint to remember 'Pacific'). If a hint value is given, the hint is embedded into the result and can be retrieved using the GETHINT function. If this argument is null or not provided, no hint will be embedded in the result.

The result data type of the function is VARCHAR FOR BIT DATA.

When the optional hint parameter is specified, the length attribute of the result is equal to the length attribute of the unencrypted data + 8 bytes + the number of bytes until the next 8-byte boundary + 32 bytes for the length of the hint.
When the optional hint parameter is not specified, the length attribute of the result is equal to the length attribute of the unencrypted data + 8 bytes + the number of bytes until the next 8-byte boundary.

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

Note that the encrypted result is longer than the data-string-expression value. Therefore, when assigning encrypted values, ensure that the target is declared with sufficient size to contain the entire encrypted value.

Notes

Encryption algorithm: The internal encryption algorithm is RC2 block cipher with padding; the 128-bit secret key is derived from the password using an MD5 message digest.
Encryption passwords and data: Password management is the user's responsibility. Once the data is encrypted, only the password that was used when encrypting it can be used to decrypt it (SQLSTATE 428FD).
The encrypted result might contain null terminator and other unprintable characters. Any assignment or cast to a length that is shorter than the suggested data length might result in failed decryption in the future, and lost data. Blanks are valid encrypted data values that might be truncated when stored in a column that is too short.
Administration of encrypted data: Encrypted data can only be decrypted on servers that support the decryption functions corresponding to the ENCRYPT function. Therefore, replication of columns with encrypted data should only be done to servers that support the DECRYPT_BIN or the DECRYPT_CHAR function.

Avoid encrypted data in predicates: The ENCRYPT function does not always produce the same encrypted data when given the same input. Do not use encrypted data in search conditions or comparison operations. For example, do not use encrypted data in a predicate.

Examples

Example 1: The following example demonstrates the use of the ENCRYPT 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 hostVarInsertStmt3[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;

Example 2: 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;

Example 3: 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;

Example 4: Define a password hint:

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

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