Product Documentation

Appearance

ENCRYPT (deprecated) ​

Given an input character string, return an encrypted value. See also DECRYPT (deprecated).

ENCRYPT(input_expression, key [, algorithm [, ivec]])

This function returns a Base64-encoded VARCHAR value that is slightly larger 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 * 8 + 5) / 6))
  • Otherwise (algorithm = constant > 3, not specified, or not a constant, such as the result of an expression):
VARCHAR(CEIL((512 + input) / 3) * 4)

Parameters ​

input_expression

A VARCHAR expression, such as a character column in a table, a substring of a column, or a concatenation of character strings from multiple columns. This expression represents the sensitive data that you want to protect and encrypt on output.

key

A hexadecimal string or an expression that evaluates to a hexadecimal string. For example, you can specify a character string inside an MD5 function. This key is like a password, providing a level of security for your use of the function and a guarantee of consistent encryption. To produce consistent results when encrypting and decrypting the same input expression, users must provide the same key value. See the description of the algorithm parameter for information about the required length of the key. See also Encrypting Sensitive Data.

algorithm

The specific encryption algorithm that you want to use. Valid entries are 1, 2, and 3. The default is 1. All of these algorithms use Output Feedback Mode (OFB).

Function parameterAlgorithm
1Initialize AES with a 128-bit key
2Initialize AES with a 192-bit key
3Initialize AES with a 256-bit key

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. Changing the vector but using the same key has the effect of re-scrambling the output for a given input expression. See Encrypting Sensitive Data. See the description of the algorithm parameter for information about the required length of the ivec value.

Examples ​

Encrypt the nickname column from the team table, using the default algorithm:

premdb=# select nickname, encrypt(nickname, md5('Testing 1-2-3')) 
from team order by teamid limit 5;
 nickname |   encrypt   
----------+-------------
 Gunners  | NchTADqJV2
 Villains | 6MAT8T4L2Y5
 Tykes    | 4MxS1L5
 Blues    | I2QR1L5
 Rovers   | 2EgR1HbJ
(5 rows)

Run the same query, using the same key, but change the algorithm:

premdb=# select nickname, encrypt(nickname, md5('Testing 1-2-3'), 3) from team order by teamid limit 5;
 nickname |   encrypt   
----------+-------------
 Gunners  | Z14y6h|cS3
 Villains | onby4xFW1B6
 Tykes    | mnKzDpE
 Blues    | cbrwDpE
 Rovers   | sf5wDtkc
(5 rows)

Add an ivec value:

premdb=# select nickname, encrypt(nickname, md5('Testing 1-2-3'), 3, md5('IVEC Test')) from team order by teamid limit 5;
 nickname |   encrypt   
----------+-------------
 Gunners  | l91kL6ChL0
 Villains | {vWkNMyj8K5
 Tykes    | yvHlUUD
 Blues    | gjmeUUD
 Rovers   | wX0eUQTh
(5 rows)

Encrypt the result of a CONCAT function. Change all of the parameters.

premdb=# select concat(name,', ',nickname), encrypt(concat(name,', ',nickname), md5('Testing concat'), 2, md5('IVEC concat')) 
from team order by teamid limit 5;
         concat          |             encrypt              
--------------------------+----------------------------------
 Arsenal, Gunners         | jgbZQ1oAIRgYY1HF9QoAc0
 Aston Villa, Villains    | jkLYG1YQeFkn9HmV7N1Cvy12vTgV
 Barnsley, Tykes          | kcqZHrZ9RFlX54ZACQYA
 Birmingham City, Blues   | k6qZIT29PB{o8L44EU38vDrAynBQB1
 Blackburn Rovers, Rovers | kI4dSL2ABZFn5S3FHQoAcyLI2PwUTXAU
(5 rows)

Encrypt the nickname column by using a key that is selected from another table:

premdb=> select encrypt(nickname,
(select key from userkeys where whoami=current_user)) 
from team order by teamid;
      encrypt       
---------------------
 CyftgWky|C2f10
 V8zzguku5qYmwD8
 T8jpjKkwi0
 BuyvqKkwi0
 R0VtqKkviC2
...

Parent topic:String Functions