ENCRYPT() - Encrypt data

Functon group: cryptographic function

ENCRYPT() encrypts values of any data type using the AES algorithm and a key of 128 bits (16 bytes) in Electronic Codebook Mode (ECM).

---

ENCRYPT ( expression , key )

---

expression

Expression whose value is to be encrypted.
The value may be of any data type, but not CHARACTER VARYING with length >= 31998 and not NATIONAL CHARACTER VARYING (16000).
expression may not be a multiple value with dimension > 1.

key

Key with which the value of expression is to be encrypted.
Alphanumeric string with a length of 16 characters, i.e. of the data type
CHARACTER(16) or CHARACTER VARYING(n) where n >=16.
A NULL value of one of these data types is also permissible.

Result

If the value of expression or key is the NULL value, the result is the NULL value.

Otherwise:

The encrypted value of expression.

Data type: CHARACTER VARYING with a maximum length in accordance with the table on the next page.

Data type of expression	Data type of the result
CHAR(m)	VARCHAR(n) 1
VARCHAR(m) where m <= 31998	VARCHAR(n) 1
NCHAR(m)	VARCHAR(n) 2
NVARCHAR(m) where m <= 15999	VARCHAR(n) 2
SMALLINT, INTEGER	VARCHAR(16)
NUMERIC (up to 14 characters)	VARCHAR(16)
NUMERIC (15 to 30 characters)	VARCHAR(32)
NUMERIC (31 characters)	VARCHAR(48)
DECIMAL (up to 27 characters)	VARCHAR(16)
DECIMAL (28 to 31 characters)	VARCHAR(32)
FLOAT, REAL, DOUBLE PRECISION	VARCHAR(16)
DATE, TIME(3), TIMESTAMP(3)	VARCHAR(16)

Table 14: Data type of the result of ENCRYPT()

¹Where n is the lowest integral multiple of 16 which is>= m + 2
²Where n is the lowest integral multiple of 16 which is >= 2*m + 2

If expression has a data type whose values can have different lengths (i.e. (NATIONAL) CHARACTER VARYING), the encrypted values can also have different lengths. However, the length of the encrypted value is always a multiple of 16 characters, see the table above.If, for example, expression has the data type VARCHAR(20), the result ENCRYPT() will have the data type VARCHAR(32); strings with 0 to 14 characters are encrypted in strings with 16 characters, strings with 15 to 20 characters in strings with 32 characters. The precise length of the unencrypted value cannot be determined from the encrypted value without knowledge of the key (it is encrypted together with the value).

Examples

The values of the SERVICE_PRICE column are encrypted in the SREC_ENCR column; the unencrypted values of the SERVICE_PRICE column are converted to NULL: UPDATE service SET srec_encr=ENCRYPT(service_price,'0123456789ABCDEF'), service_price = NULL WHERE service_price IS NOT NULL The REP_OF_VALUE function also enables multiple values to be encrypted in a string (see also "DECRYPT() - Decrypt data"): ENCRYPT (REP_OF_VALUE(wages) || REP_OF_VALUE(bonus), :key)