Encrypts a VARCHAR or BINARY value using a VARCHAR passphrase.
- See also:
ENCRYPT( <value_to_encrypt> , <passphrase> , [ [ <additional_authenticated_data> , ] <encryption_method> ] )
The VARCHAR or BINARY value to encrypt.
The passphrase to use to encrypt/decrypt the data. The passphrase is always a VARCHAR, regardless of whether the
value_to_encryptis VARCHAR or BINARY.
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
passphraseis 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).
This string specifies the method to use for encrypting/decrypting the data. This string contains subfields:<algorithm>-<mode> [ /pad: <padding> ]
algorithmis 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.
modespecifies 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:
Encrypt every block individually with the key. This mode is generally discouraged and is included only for compatibility with external implementations.
The encrypted block is XORed with the previous block.
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).
Output feedback. The ciphertext is XORed with the plaintext of a block.
Cipher feedback is a combination of OFB and CBC.
paddingspecifies 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.
modeis not specified, GCM is used.
paddingis not specified, PKCS is used.
The data type of the returned value is BINARY.
Although only a single value is returned, that value contains two or three concatenated fields:
The first field is an initialization vector (IV). The IV is generated randomly using a CTR-DRBG random number generator. Both encryption and decryption use the IV.
The second field is the ciphertext (encrypted value) of the
If the encryption mode is AEAD-enabled, then the returned value also contains a third field, which is the AEAD tag.
The IV and tag size depend on the encryption mode.
To decrypt data encrypted by
DECRYPT(). Do not use
To decrypt data encrypted by
DECRYPT_RAW(). Do not use
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.
The passphrase can be of arbitrary length, even 0 (the empty string). However, Snowflake strongly recommends using a passphrase that is at least 8 bytes.
Snowflake recommends that the passphrase follow general best practices for passwords, such as using a mix of uppercase letters, lowercase letters, numbers, and punctuation.
The passphrase is not used directly to encrypt/decrypt the input. Instead, the passphrase is used to derive an encryption/decryption key, which is always the same for the same passphrase. Snowflake uses the https://en.wikipedia.org/wiki/PBKDF2 key-derivation function with a Snowflake-internal seed to compute the encryption/decryption key from the given passphrase.
Because of this key derivation, the encrypt/decrypt function cannot be used to:
Decrypt data that was externally encrypted.
Encrypt data that will be externally decrypted.
To do either of these, use ENCRYPT_RAW or DECRYPT_RAW.
Because the initialization vector is always regenerated randomly, calling
ENCRYPT()with the same
passphrasedoes not return the same result every time. If you need to generate the same output for the same
passphrase, consider using ENCRYPT_RAW and specifying the initialization vector.
This example encrypts a VARCHAR with a simple passphrase.
SELECT encrypt('Secret!', 'SamplePassphrase');
The output is text that is not easy for humans to read.
The code below shows a simple example of encryption and decryption:
SET passphrase='poiuqewjlkfsd';SELECT TO_VARCHAR( DECRYPT( ENCRYPT('Patient tested positive for COVID-19', $passphrase), $passphrase), 'utf-8') AS dcrypt ; +--------------------------------------+ | DCRYPT | |--------------------------------------| | Patient tested positive for COVID-19 | +--------------------------------------+
This example uses a BINARY value for the
value_to_encrypt and for the authenticated data.
SELECT encrypt(to_binary(hex_encode('Secret!')), 'SamplePassphrase', to_binary(hex_encode('Authenticated Data')));
The output is:6E1361E297C22969345F978A45205E3E98EB872844E3A0F151713894C273FAEF50C365S
This example shows how to use an alternative mode (
CBC) as part of the specifier for the encryption method.
This encryption method also specifies a padding rule (
PKCS). In this example, the AAD parameter is NULL.
SELECT encrypt(to_binary(hex_encode('secret!')), 'sample_passphrase', NULL, 'aes-cbc/pad:pkcs') as encrypted_data;
This example shows how to use the AAD:
SELECT TO_VARCHAR( DECRYPT( ENCRYPT('penicillin', $passphrase, 'John Dough AAD', 'aes-gcm'), $passphrase, 'John Dough AAD', 'aes-gcm'), 'utf-8') AS medicine ; +------------+ | MEDICINE | |------------| | penicillin | +------------+
If you pass the wrong AAD, decryption fails:
SELECT DECRYPT( ENCRYPT('penicillin', $passphrase, 'John Dough AAD', 'aes-gcm'), $passphrase, 'wrong patient AAD', 'aes-gcm') AS medicine ;
100311 (22023): Decryption failed. Check encrypted data, key, AAD, or AEAD tag.