Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

DECRYPT function [String]

Decrypts the string using the supplied key and returns a LONG BINARY value.

DECRYPT( string-expression, key
[, algorithm ]
)

string-expression    The string to be decrypted. Binary values can also be passed to this function. This parameter is case sensitive, even in case-insensitive databases.

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

algorithm    This optional parameter specifies the algorithm used to decrypt the string-expression. The string-expression must be decrypted using the same algorithm with which it was encrypted. The algorithm used to implement SQL Anywhere 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).

On any platform that supports FIPS, you can also specify a separate FIPS-approved AES algorithm for strong encryption using the AES_FIPS type. When the database server is started with the -fips option, you can run databases encrypted with AES or AES_FIPS strong encryption, but not databases encrypted with simple encryption. Unencrypted databases can also be started on the server when -fips is specified.

You can use the DECRYPT function to decrypt a string-expression that was encrypted with the ENCRYPT function. This function returns a LONG BINARY value with the same number of bytes as the input string.

To successfully decrypt a string-expression, you must use the same encryption key that was used to encrypt the data. If you specify an incorrect encryption key, an error is generated. A lost key will result in inaccessible data, from which there is no recovery.

• ENCRYPT function [String]
• Encrypting portions of a database
• String functions

• SQL/2003    Vendor extension.

The following example decrypts a user's password from the user_info table. The CAST function is used to convert the password back to a CHAR data type because the DECRYPT function converts values to the LONG BINARY data type.

SELECT CAST( DECRYPT( user_pwd, '8U3dkA' ) AS CHAR(100) ) FROM user_info;