ENCRYPT function [String]

ENCRYPT( string-expression, key 
[, algorithm [ format ] ] 
)

algorithm : 
'AES' 
| 'AES256' 
| 'AES_FIPS' 
| 'AES256_FIPS'
[ format ]

format:
( FORMAT=RAW [;padding ] ) [ initialization-vector ] ) 

padding:
PADDING=PKCS5 
| ZEROES 
| NONE ]

string-expression The data to be encrypted. Binary values can also be passed to this function. This parameter is case sensitive, even in case-insensitive databases.
key The encryption key used to encrypt the string-expression. This same key must be used to decrypt the value to obtain the original value. This parameter is case sensitive, even in case-insensitive databases.
As with most passwords, it is best to choose a key value that cannot be easily guessed. It is recommended that you choose a value for your key that is at least 16 characters long, contains a mix of uppercase and lowercase, and includes numbers, letters and special characters. You require this key each time you want to decrypt the data.

Caution

For strongly encrypted columns, store a copy of the key in a safe location. If you lose the encryption key, there is no way to access the data—even with the assistance of Technical Support. The column must be discarded and you must create a new column.
algorithm This optional parameter specifies the algorithm to use when encrypting string-expression. The algorithm used for strong encryption is Rijndael: a block encryption algorithm chosen as the new Advanced Encryption Standard (AES) for block ciphers by the National Institute of Standards and Technology (NIST).
You can specify one of the FIPS-certified algorithms for algorithm on any platform that supports FIPS-certified encryption.

Note

RSA and FIPS-certified encryption are not available on all platforms. For information about which platforms support which encryption method, see http://www.sybase.com/detail?id=1061806.

If algorithm is not specified, AES is used by default. If the database server was started using the -fips server option, AES_FIPS is used as the default instead.
FORMAT=RAW This optional parameter encrypts the data into raw format. The initialization vector parameter is required.
padding This optional parameter specifies the type of padding to use. If padding is not specified, PKCS5 is used by default.
- PKCS5 The data is padded using the PKCS#5 algorithm. The outputted (encrypted) data is 1-16 bytes longer than the input data.
- ZEROES The data is padded with zeros (0). The outputted (encrypted) data is 0-15 bytes longer than the input data. When this output is decrypted, it is also padded with zeros.
- NONE The data is not padded. The input data must be a multiple of the cipher block length (16-bytes).
initialization-vector This initialization vector parameter is required when FORMAT=RAW is specified. The string cannot be longer than 16 bytes. Any value less than 16 bytes is padded with 0 bytes. This string cannot be set to NULL.

LONG BINARY

The LONG BINARY value returned by this function is at most 31 bytes longer than the input string-expression. The value returned by this function is not human-readable. You can use the DECRYPT function to decrypt a string-expression that was encrypted with the ENCRYPT function. To successfully decrypt a string-expression, you must use the same encryption key and algorithm that were used to encrypt the data. If you specify an incorrect encryption key, an error is generated. A lost key results in inaccessible data, from which there is no recovery.

If you are storing encrypted values in a table, the column should be BINARY or LONG BINARY so that character set conversion is not performed on the data.

When FORMAT=RAW is specified, the data is encrypted using raw encryption. You must specify the encryption key, initialization vector, and optionally the padding format. These same values must be specified when decrypting the data. The decryption can be performed outside of the database server; although you can also use the DECRYPT function.

Raw encryption is not recommended when the data is to be encrypted and decrypted only within the database server because you must specify the initialization vector and the padding, and the encryption key cannot be verified during decryption.

SQL/2008 Vendor extension.

The following trigger encrypts the user_pwd column of the user_info table. This column contains users' passwords, and the trigger fires whenever a password value is changed.

CREATE TRIGGER encrypt_updated_pwd
BEFORE UPDATE OF user_pwd
ON user_info
REFERENCING NEW AS new_pwd
FOR EACH ROW
BEGIN
    SET new_pwd.user_pwd=ENCRYPT( new_pwd.user_pwd, '8U3dkA' );
END;

The following SELECT statement uses raw encryption to encrypt the binary_data column of the SensitiveData table. The encrypted data is padded with zeroes:

SELECT ENCRYPT( binary_data, 'TheEncryptionKey',
'AES(format=raw;padding=zeroes)', 'ThisIsTheIV'),
LENGTH(binary_data)
FROM SensitiveData;

ENCRYPT function [String]

Syntax

Parameters

Caution

Note

Returns

Remarks

See also

Standards and compatibility

Example