AES_DECRYPT Function [String]

Decrypts the string using the supplied key, and returns, by default, a VARBINARY or LONG BINARY, or the original plaintext type.

Syntax

AES_DECRYPT( string-expression, key [, data-type ] )

Parameters

string-expression – the string to be decrypted. You can also pass binary values to this function. This parameter is case sensitive, even in case-insensitive databases.

key – the encryption key required to decrypt the string-expression. To obtain the original value that was encrypted, the key must be the same encryption key that was used to encrypt the string-expression. This parameter is case-sensitive, even in case-insensitive databases.

Warning! Protect your key; store a copy of your key in a safe location. If you lose your key, the encrypted data becomes completely inaccessible and unrecoverable.

data-type – this optional parameter specifies the data type of the decrypted string-expression and must be the same data type as the original plaintext.

If you do not use a CAST statement while inserting data using the AES_ENCRYPT function, you can view the same data using the AES_DECRYPT function by passing VARCHAR as the data-type. If you do not pass data-type to AES_DECRYPT, VARBINARY data type is returned.

Usage

You can use the AES_DECRYPT function to decrypt a string-expression that was encrypted with the AES_ENCRYPT function. This function returns a VARBINARY or LONG VARBINARY value with the same number of bytes as the input string, if no data type is specified. Otherwise, the specified data type is returned.

To successfully decrypt a string-expression, you must use the same encryption key that was used to encrypt the data. An incorrect encryption key returns an error.

Example

Decrypt the password of a user from the user_info table.

SELECT AES_DECRYPT(user_pwd, '8U3dkA', CHAR(100))
FROM user_info;

Standards and Compatibility

SQL – vendor extension to ISO/ANSI SQL grammar.
Sybase – not supported by Adaptive Server Enterprise.