encrypt() and decrypt() functions

The encrypt() and decrypt() functions encrypt or decrypt a varchar or nvarchar value by using a supplied key.

Encryption is the process of transforming data to maintain its secrecy; the data can be read (unencrypted) only if the recipient has the required key. The Netezza Performance Server implementation uses symmetric encryption. This type of encryption is also known as private or secret key encryption, because the same secret key is used to encrypt and to decrypt data. This means that this secret key must be made available on any server that is decrypting previously encrypted data. You can choose which symmetric encryption algorithm the functions use to encrypt and decrypt the data: either Advanced Encryption Standard (AES) or RC4.

Private key encryption is more secure than public key encryption because all public key encryption schemes are susceptible to brute force key search attacks. However, private key encryption depends on maintaining the secrecy of the key. Therefore, periodically change the private key, and take steps to ensure that it cannot be discovered in use, in storage, or in distribution. For security recommendations that are specific to the Netezza Performance Server product, see the description of the key argument later in the topic.

The encrypt() and decrypt() functions perform field-level encryption and decryption, not database encryption and decryption.

Important: If you are using the encrypt() function to process input nvarchar values, you should transition your applications and query processes to use the encrypt_nvarchar() and decrypt_nvarchar() functions. The encrypt() function remains for compatibility with earlier versions and results, but tables and views that store encrypt('NVARCHAR') function values might encounter problems during loads and GENERATE STATISTICS operations.

Syntax

The encrypt() function has the following syntax:
varchar = encrypt(varchar text, varchar key [, int algorithm 
          [, varchar IV]]);
nvarchar = encrypt(nvarchar text, nvarchar key [, int algorithm 
          [, varchar IV]]);
The decrypt() function has the following syntax:
varchar = decrypt(varchar text, varchar key [, int algorithm 
          [, varchar IV]]);
nvarchar = decrypt(nvarchar text, nvarchar key [, int algorithm
          [, varchar IV]]);

The text value specifies the value to encrypt or decrypt.

The key value specifies the key to use to encrypt or decrypt the value. Take care to secure the key: otherwise, security is compromised. When designing your security system, keep in mind the architecture of the Netezza Performance Server system:
  • SQL functions are logged in the pg.log file on the Netezza Performance Server host, so executing an encrypt(secret_column, 'my_secret_key') function reveals your key to anyone who can read the pg.log file.
  • ODBC/JDBC conversations are easily captured with any number of diagnostic or hacking tools. If you transmit your key as part of the SQL, the key can be compromised during this process.
For these reasons, store the secret key in a table, and pass the key to the encrypt() and decrypt() functions through a table join. An example follows:
SELECT decrypt(a.value, b.key) FROM my_table a, my_keys b WHERE 
b.key_id = 1;
The algorithm value can be either RC4 or one of the versions of AES, as shown in the following list:
0
RC4. This is the default.
1
AES 128.
2
AES 192.
3
AES 256.

RC4, although the most widely used encryption algorithm (used, for example, by SSL and WEP), is not cryptographically secure and is vulnerable to attacks.

The Advanced Encryption Standard (AES) is the encryption standard that was adopted by the United States government and is required for all classified information. The three versions of AES differ only in the design and strength of the key lengths. Although all three key lengths are sufficient to protect classified information up to the SECRET level, TOP SECRET information requires the use of 192 or 256 key lengths.

The IV value specifies the initialization vector (IV) that is used to encrypt or decrypt the value. You can specify the IV only when using the AES algorithms. (The RC4 algorithm does not use the IV value.) If you do not specify an IV value, the encrypt() and decrypt() functions use a constant IV value.

Encryption with the output feedback (OFB) mode requires the IV and key to be 128 bits. As a best practice, specify 128-bit values for the key and IV (if used) so that the system uses your input values. If a value is less than or greater than 128 bits, the system uses an expansion or derivation mechanism to adjust the value as follows:

  • For keys or IVs that are smaller than 128 bits, the system duplicates the value until the key or IV reaches the correct length.
  • For keys or IVs that are larger than 128 bits, the system creates a 128-bit value by using a logical OR (XOR) of the first 128 bits of the value with the remaining bits of the value.
If you embed or call an encrypt() function within an encrypt() function, the result is a cleartext string, as shown in the following example:
select encrypt(encrypt('abcde', 'k1', 1), 'k1', 1);
  encrypt
 ---------
  abcde
 (1 row)

Returns

The functions return an encrypted or decrypted value. The SQL Extensions toolkit uses the OFB mode of AES for encryption and decryption, which makes a block cipher into a synchronous stream cipher. As a stream cipher, there is no padding requirement for OFB, and the length of the output ciphertext is the same as that of the input plaintext.

Example

select encrypt('123456',100,0);
 ENCRYPT
---------
 ÉôC¥Ñ
(1 row)

 select encrypt('Netezza Systems','NPS001534',1,'MY_IV is IBM001');
     ENCRYPT
-----------------
 �Ná•▒7Þ¢bhñæ
(1 row)

Key and IV generation

You can use the hash() and rawtohex() functions to generate keys and IVs. The hash() function returns a 128-bit, 160-bit, or 256-bit hash of the input data, depending on the algorithm input for the function. The following example shows how to create a 256-bit key for AES encryption. In the example, the MYKEY output is intentionally truncated by using ellipsis (...) characters so that the output can be displayed within the page boundary. 

create table impData (cname varchar(35),cid varchar(20));
CREATE TABLE

insert into impData values('Netezza Systems','CNZ0001SYS');
INSERT 0 1

create table securedImpData as select cid, rawtohex(hash(cid,2))
 as mykey, encrypt(cname, rawtohex(hash(cid,2)),2) as ENCRYPTED_CNAME from impData;
INSERT 0 1

select * from securedImpData;
    CID     |                    MYKEY                        | ENCRYPTED_CNAME
------------+-------------------------------------------------+-----------------
 CNZ0001SYS | 8D812C9E8DB362FCB20E...6A00B564175B92762B1A3520 | Ã4W§
      wÂÃð
ŸÃ
(1 row)

create table decryptedData as select CID, decrypt(ENCRYPTED_CNAME,
MYKEY,2) as DECRYPTED_CNAME from securedImpData;
INSERT 0 1

select * from decryptedData;
    CID     | DECRYPTED_CNAME
------------+-----------------
 CNZ0001SYS | Netezza Systems
(1 row)