Functon group: cryptographic function
DECRYPT() decrypts strings in accordance with the AES algorithm and using a key of 128 bits (16 bytes) in Electronic Codebook Mode (ECM) to the corresponding value of a specified data type.
DECRYPT ( expression , key , data_type )
expression
Specifies the value which is to be decrypted.
The value must be of the alphanumeric data type CHARACTER or CHARACTER VARYING.
expression may not be a multiple value with dimension > 1.
The length of expression must be an integral multiple of 16 and greater than 0. A NULL value is also permitted.
key
Key with which the value of expression is to be decrypted.
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.
To obtain a correct result, the key must be the same as that which was used for encryption with ENCRYPT().
data_type
Data type of the decrypted value (without dimension specification). The data types permitted depend on the (maximum) length of the data type of expression, see the table on the next page.
Result
If the value of expression or key is the NULL value, the result is the NULL value.
Otherwise:
For the decrypted value of expression in the specified data type, see the table on the next page. For possible errors, see "Error cases".
Data type: the specified data_type
Data type of expression | data_type and data type of the result |
CHAR(m), VARCHAR(>= m) 1 | CHAR(n) if n <= 256 2 |
CHAR(m), VARCHAR(>= m) 1 | VARCHAR(n) 2 |
CHAR(m), VARCHAR(>= m) 1 | NCHAR(n) 3 |
CHAR(m), VARCHAR(>= m) 1 | NVARCHAR(n) 3 |
CHAR(16), VARCHAR(>= 16) | SMALLINT, INTEGER |
CHAR(16), VARCHAR(>= 16) | NUMERIC (up to 14 characters) |
CHAR(32), VARCHAR(>= 32) | NUMERIC (15 to 30 characters) |
CHAR(48), VARCHAR(>= 48) | NUMERIC (31 characters) |
CHAR(16), VARCHAR(>= 16) | DECIMAL (up to 27 characters) |
CHAR(32), VARCHAR(>= 32) | DECIMAL (28 to 31 characters) |
CHAR(16), VARCHAR(>= 16) | FLOAT, REAL, DOUBLE PRECISION |
CHAR(16), VARCHAR(>= 16) | DATE, TIME(3), TIMESTAMP(3) |
Table 13: Permitted combinations in the case of DECRYPT()
1m must be>= 16 and an integral multiple of 16
2Length n must be >= 1 and between (m - 17) and (m -2) (inclusive)
3Length n must be >= 1 and between (m/2 - 1) and (m/2 - 8) (inclusive)
Examples
Decryption in a SELECT expression:
SELECT DECRYPT(sprice_encr,'0123456789ABCDEF',NUMERIC(5,0))
AS test_decr FROM service
The VALUE_OF_REP function also enables individual values of a jointly encoded string to be decrypted (see also "ENCRYPT() - Encrypt data"):
VALUE_OF_REP (SUBSTRING (DECRYPT (wagesandbonus, :key, CHAR(12)) FROM 7 FOR 6), NUMERIC(6)) AS bonus
Error cases
The following errors can occur when the DECRYPT function is executed:
The length of the encrypted string is 0 or not an integral multiple of 16.
The key key is a string with a length which is not 16 or it is not the key that was used for encryption.
The decrypted value does not match the data type specified in the result (when, for example, a SMALLINT value is encrypted, but INTEGER was specified as the result type in the DECRYPT function (or vice versa)).
However, when the DECRYPT function is executed no check is made to see whether the decrypted result is assigned precisely the same data type as the encrypted value. Only the internal presentation of values is encrypted and decrypted, but no additional information.
Thus, for example, in SESAM/SQL the values of the data types INTEGER, CHARACTER(4), NUMERIC(4,0), DECIMAL(7,2) and REAL which are not equal to NULL all have an internal presentation with precisely 4 bytes. Consequently a value of the data type INTEGER can be encrypted and decrypted to a value of the type CHAR(4) or REAL. The DECRYPT function does not return an error even if decryption is to the type NUMERIC(4,0). Depending on the decrypted value, however, an error can occur in a subsequent arithmetic operation.