Product Documentation

Appearance

All platforms

DECRYPT_KS ​

Given an encrypted character string, return the decrypted value using a key. See also ENCRYPT_KS.

DECRYPT_KS(encrypted_string, key [, algorithm [, ivec]]);

This function returns a VARCHAR value that is slightly smaller than the input expression. The formula depends on the algorithm specified:

  • If algorithm 1, 2, or 3 is explicitly specified as a constant in the function:
VARCHAR(FLOOR((input * 6) / 8))
  • Otherwise (algorithm = constant > 3, not specified, or not a constant, such as the result of an expression):
VARCHAR(CEIL(input / 4) * 3)

Parameters ​

encrypted_string

An encrypted character string (Base64-encoded), as produced by the ENCRYPT_KS function. This string represents the data that you want to decrypt on output.

key

The name of a key created with the CREATE KEY command.

algorithm

The specific encryption algorithm that you want to use (optional). Valid entries are 1 through 9. The default is 1. All of these algorithms use Output Feedback Mode (OFB). For more details, see Encryption and Decryption Algorithms.

The algorithm that you select determines the required size of the key value: 128, 192, or 256 bits. The size of the initialization vector (ivec) for all three algorithms is 128 bits.

When the input key or ivec is too short, the input data is expanded to produce the required length by appending the input provided. When the input key or the ivec is too long, the extra input is folded into the required input, starting from the beginning, using XOR logic.

ivec

An initialization vector for the algorithm (optional). You must specify a hexadecimal string or an expression that evaluates to a hexadecimal string. If you specify a vector, you must also specify an algorithm. See Encrypting Sensitive Data.

Examples ​

The following subquery encrypts the nickname column, then the outer query decrypts it. The query verifies that the decrypted value matches the original value. Because similar nickname values, such as Blues, are the same, the encrypted column in the result proves that the same string is encrypted the same way.

premdb=# select nickname, encrypted, 
decrypt_ks(encrypted,yb100key,3) as decrypted
from(select nickname, encrypt_ks(nickname,yb100key,3) as encrypted from team) t1
order by 1;
  nickname  |   encrypted    | decrypted  
------------+----------------+------------
 Addicks    | 3tvOiABww1     | Addicks
 Baggies    | 0Z9OYYhvw1     | Baggies
 Bantams    | 0ZPQn2hxw1     | Bantams
 Black Cats | 0NfPcgxeAD|NI3 | Black Cats
 Blades     | 0NfPXIBy       | Blades
 Blues      | 0NfSWAA        | Blues
 Blues      | 0NfSWAA        | Blues
...

The following example decrypts the values of an encrypted expression using key yb100key:

premdb=# select teamid, decrypt_ks(team_name,yb100key) as full_name, avg_att from encrypted_team_names limit 5;
 teamid |        full_name        | avg_att 
--------+-------------------------+---------
     1 | Arsenal:Gunners         |  59.944
     2 | Aston Villa:Villains    |  33.690
     3 | Barnsley:Tykes          |   0.000
     4 | Birmingham City:Blues   |   0.000
     5 | Blackburn Rovers:Rovers |   0.000
(5 rows)