encrypt_nvarchar() and decrypt_nvarchar() functions

The encrypt_nvarchar() and decrypt_nvarchar() functions encrypt nvarchar data and decrypt that encrypted data by using a supplied key.

For details about the encryption process, see encrypt and decrypt() functions. The Netezza Performance Server implementation uses symmetric encryption, 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.

The encrypt_nvarchar() and decrypt_nvarchar() functions perform field-level encryption and decryption, not database encryption and decryption.

Important: If you are using the encrypt() function to process 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_nvarchar() function has the following syntax:
varchar = encrypt_nvarchar(nvarchar text, nvarchar key [, int algorithm 
          [, varchar IV]]);
The decrypt_nvarchar() function has the following syntax:
nvarchar = decrypt_nvarchar(varchar 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_nvarchar(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 information about storing the secret key in a table, see encrypt() and decrypt() functions.
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_nvarchar() and decrypt_nvarchar() 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_nvarchar() function within an encrypt_nvarchar() function, the result is not a cleartext string, as shown in the following example:Sample nvarchar data

Returns

The function returns 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

An example of the encrypt_nvarchar() function follows:

Sample nvarchar data in encrypt_nvarchar() function call

An example of the decrypt_nvarchar() function follows:

Sample nvarchar data in decrypt_nvarchar() call