Categories:

Encryption Functions

DECRYPT_RAW

Decrypts a BINARY value using a BINARY key.

See also:

ENCRYPT_RAW

Syntax

DECRYPT_RAW( <value_to_decrypt> , <key> , <iv> ,
         [ [ [ <additional_authenticated_data> , ] <encryption_method> , ] <aead_tag> ]
       )

Arguments

Required:

value_to_decrypt

The binary value to decrypt.

key

The key to use to encrypt/decrypt the data. The key must be a BINARY value. The key can be any value as long as the length is correct. For example, for AES128, the key must be 128 bits (16 bytes), and for AES256, the key must be 256 bits (32 bytes).

The key used to encrypt the value must be used to decrypt the value.

iv

This parameter contains the Initialization Vector (IV) to use to encrypt and decrypt this piece of data. The IV must be a BINARY value of a specific length:

  • For GCM, this should be 96 bits (12 bytes).

  • For CCM, this should be 56 bits (7 bytes).

  • For ECB, this parameter is unneeded.

  • For all other supported encryption modes, this should be 128 bits (16 bytes).

This value is used to initialize the first encryption round. You should never use the same IV and key combination more than once, especially for encryption modes like GCM.

If this parameter is set to NULL, the implementation will choose a new pseudo-random IV during each call.

Optional:

additional_authenticated_data

Additional authenticated data (AAD) is additional data whose confidentiality and authenticity is assured during the decryption process. However, this AAD is not encrypted and is not included as a field in the returned value from the ENCRYPT or ENCRYPT_RAW function.

If AAD is passed to the encryption function (ENCRYPT or ENCRYPT_RAW), then the same AAD must be passed to the decryption function (DECRYPT or DECRYPT_RAW). If the AAD passed to the decryption function does not match the AAD passed to the encryption function, then decryption fails.

The difference between the AAD and the passphrase is that the passphrase is intended to be kept secret (otherwise, the encryption is essentially worthless) while the AAD can be left public. The AAD helps authenticate that a public piece of information and an encrypted value are associated with each other. The examples section in the ENCRYPT function includes an example showing the behavior when the AAD matches and the behavior when it doesn’t match.

For ENCRYPT_RAW and DECRYPT_RAW, the data type of the AAD should be BINARY. For ENCRYPT and DECRYPT, the data type of the AAD can be either VARCHAR or BINARY, and does not need to match the data type of the value that was encrypted.

AAD is supported only by AEAD-enabled encryption modes like GCM (default).

encryption_method

This string specifies the method to use for encrypting/decrypting the data. This string contains subfields:

<algorithm>-<mode> [ /pad: <padding> ]

The algorithm is currently limited to:

  • 'AES': When a passphrase is passed (e.g. to ENCRYPT), the function uses AES-256 encryption (256 bits). When a key is passed (e.g. to ENCRYPT_RAW), the function uses 128, 192, or 256-bit encryption, depending upon the key length.

The algorithm is case-insensitive.

The mode specifies which block cipher mode should be used to encrypt messages. The following table shows which modes are supported, and which of those modes support padding:

Mode

Padding

Description

'ECB'

Yes

Encrypt every block individually with the key. This mode is generally discouraged and is included only for compatibility with external implementations.

'CBC'

Yes

The encrypted block is XORed with the previous block.

'GCM'

No

Galois/Counter Mode is a high-performance encryption mode that is AEAD-enabled. AEAD additionally assures the authenticity and confidentiality of the encrypted data by generating an AEAD tag. Moreover, AEAD supports AAD (additional authenticated data).

'CTR'

No

Counter mode.

'OFB'

No

Output feedback. The ciphertext is XORed with the plaintext of a block.

'CFB'

No

Cipher feedback is a combination of OFB and CBC.

The mode is case-insensitive.

The padding specifies how to pad messages whose length is not a multiple of the block size. Padding is applicable only for ECB and CBC modes; padding is ignored for other modes. The possible values for padding are:

  • 'PKCS': Uses PKCS5 for block padding.

  • 'NONE': No padding. The user needs to take care of the padding when using ECB or CBC mode.

The padding is case-insensitive.

Default setting: 'AES-GCM'.

If the mode is not specified, GCM is used.

If the padding is not specified, PKCS is used.

aead_tag

This BINARY value is needed for AEAD-enabled decryption modes to check the authenticity and confidentiality of the encrypted data. Use the AEAD tag that was returned by the ENCRYPT_RAW function. An example below shows how to access and use this value.

Returns

The function returns the decrypted value. The data type of the returned value is BINARY.

Usage Notes

  • To decrypt data encrypted by ENCRYPT(), use DECRYPT(). Do not use DECRYPT_RAW().

  • To decrypt data encrypted by ENCRYPT_RAW(), use DECRYPT_RAW(). Do not use DECRYPT().

  • The function’s parameters are masked for security. Sensitive information such as the following is not visible in the query log and is not visible to Snowflake:

    • The string or binary value to encrypt or decrypt.

    • The passphrase or key.

  • The functions use a FIPS-compliant cryptographic library to effectively perform the encryption and decryption.

  • The passphrase or key used to decrypt a piece of data must be the same as the passphrase or key used to encrypt that data.

  • When extracting fields (ciphertext, initialization vector, or tag) from an encrypted binary value, use:

    ``as_binary(get(encrypted_value, '<field_name>'))``
    

    For example, use:

    ``as_binary(get(encrypted_value, 'ciphertext'))``
    

    Do not use encrypted_value:<field_name>::binary. The field-access operator : converts the extracted field value to a string; however, because the source is BINARY, that string is not always a valid UTF-8 string.

Examples

This example shows encryption and decryption.

For readability, set the BINARY_OUTPUT_FORMAT to HEX:

ALTER SESSION SET BINARY_OUTPUT_FORMAT='hex';

Create a table and load it.

CREATE OR REPLACE TABLE binary_table (
    encryption_key BINARY,   -- DO NOT STORE REAL ENCRYPTION KEYS THIS WAY!
    initialization_vector BINARY(12), -- DO NOT STORE REAL IV'S THIS WAY!!
    varchar_column VARCHAR,
    binary_column BINARY,
    encrypted_binary_column VARIANT,
    aad_column BINARY);

INSERT INTO binary_table (varchar_column,
                          encryption_key,
                          initialization_vector,
                          aad_column)
    SELECT 'Bonjour',
            SHA2('NotSecretEnough', 256),
            SUBSTR(TO_BINARY(HEX_ENCODE('AlsoNotSecretEnough'), 'hex'), 0, 12),
            TO_BINARY(HEX_ENCODE('additional data'))
    ;

UPDATE binary_table SET binary_column = TO_BINARY(HEX_ENCODE(varchar_column), 'HEX');

Encrypt:

UPDATE binary_table SET encrypted_binary_column =
    ENCRYPT_RAW(binary_column, 
        encryption_key, 
        initialization_vector, 
        aad_column,
        'AES-GCM');
+------------------------+-------------------------------------+
| number of rows updated | number of multi-joined rows updated |
|------------------------+-------------------------------------|
|                      1 |                                   0 |
+------------------------+-------------------------------------+

This shows the corresponding call to the DECRYPT_RAW() function. The initialization vector (IV) is taken from the encrypted value; you do not need to store the initialization vector separately. Similarly, the AEAD tag is also read from the encrypted value.

SELECT 'Bonjour' as original_value,
       binary_column,
       hex_decode_string(to_varchar(binary_column)) as decoded,
       encrypted_binary_column,
       decrypt_raw(as_binary(get(encrypted_binary_column, 'ciphertext')),
                  encryption_key,
                  as_binary(get(encrypted_binary_column, 'iv')),
                  aad_column,
                  'AES-GCM',
                  as_binary(get(encrypted_binary_column, 'tag')))
           as decrypted,
       hex_decode_string(to_varchar(decrypt_raw(as_binary(get(encrypted_binary_column, 'ciphertext')),
                  encryption_key,
                  as_binary(get(encrypted_binary_column, 'iv')),
                  aad_column,
                  'AES-GCM',
                  as_binary(get(encrypted_binary_column, 'tag')))
                  ))
           as decrypted_and_decoded
    FROM binary_table;
+----------------+----------------+---------+---------------------------------------------+----------------+-----------------------+
| ORIGINAL_VALUE | BINARY_COLUMN  | DECODED | ENCRYPTED_BINARY_COLUMN                     | DECRYPTED      | DECRYPTED_AND_DECODED |
|----------------+----------------+---------+---------------------------------------------+----------------+-----------------------|
| Bonjour        | 426F6E6A6F7572 | Bonjour | {                                           | 426F6E6A6F7572 | Bonjour               |
|                |                |         |   "ciphertext": "CA2F4A383F6F55",           |                |                       |
|                |                |         |   "iv": "416C736F4E6F745365637265",         |                |                       |
|                |                |         |   "tag": "91F28FBC6A2FE9B213D1C44B8D75D147" |                |                       |
|                |                |         | }                                           |                |                       |
+----------------+----------------+---------+---------------------------------------------+----------------+-----------------------+

The previous example duplicated a long call to DECRYPT_RAW(). You can use a WITH clause to reduce the duplication:

WITH
    decrypted_but_not_decoded as (
        decrypt_raw(as_binary(get(encrypted_binary_column, 'ciphertext')),
                      encryption_key,
                      as_binary(get(encrypted_binary_column, 'iv')),
                      aad_column,
                      'AES-GCM',
                      as_binary(get(encrypted_binary_column, 'tag')))
    )
SELECT 'Bonjour' as original_value,
       binary_column,
       hex_decode_string(to_varchar(binary_column)) as decoded,
       encrypted_binary_column,
       decrypted_but_not_decoded,
       hex_decode_string(to_varchar(decrypted_but_not_decoded))
           as decrypted_and_decoded
    FROM binary_table;
+----------------+----------------+---------+---------------------------------------------+---------------------------+-----------------------+
| ORIGINAL_VALUE | BINARY_COLUMN  | DECODED | ENCRYPTED_BINARY_COLUMN                     | DECRYPTED_BUT_NOT_DECODED | DECRYPTED_AND_DECODED |
|----------------+----------------+---------+---------------------------------------------+---------------------------+-----------------------|
| Bonjour        | 426F6E6A6F7572 | Bonjour | {                                           | 426F6E6A6F7572            | Bonjour               |
|                |                |         |   "ciphertext": "CA2F4A383F6F55",           |                           |                       |
|                |                |         |   "iv": "416C736F4E6F745365637265",         |                           |                       |
|                |                |         |   "tag": "91F28FBC6A2FE9B213D1C44B8D75D147" |                           |                       |
|                |                |         | }                                           |                           |                       |
+----------------+----------------+---------+---------------------------------------------+---------------------------+-----------------------+