DECRYPT function [String]

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

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

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

padding:
PADDING=PKCS5 
| ZEROES 
| NONE ]

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 value 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.

Caution

For strongly encrypted databases, 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 database must be discarded and you must create a new database.
algorithm This optional parameter specifies the algorithm originally used to encrypt the string-expression.

FORMAT=RAW This optional parameter specifies that the data to be decrypted is in raw format. The initialization-vector parameter is required.
padding Specify the type of padding that was used to encrypt the data. If padding is not specified, PKCS5 is used by default.
The supported padding formats are:
- PKCS5 The data is padded using the PKCS#5 algorithm. The decrypted data contains padding.
- ZEROES The data is padded with zeros (0). The decrypted data is padded with zeros.
- NONE The data is not padded. The decrypted data contains no padding.
initialization-vector Specify the initialization vector that was used to encrypt the data. This parameter is required.

LONG BINARY

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, unless the data is in raw format. When FORMAT=RAW, the length of the returned value depends on the padding format.

To successfully decrypt a string-expression, you must use the same encryption key that was used to encrypt the data. When FORMAT=RAW, you must also use the same initialization-vector and padding format that was used to encrypt the data. Data in raw format can be decrypted outside of the database server.

If you specify an incorrect encryption key, an error is generated unless FORMAT=RAW is specified. When you specify FORMAT=RAW and an incorrect encryption key or an incorrect initialization vector, the decryption fails silently.

Caution

For strongly encrypted data, 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.

Note

Not all platforms support FIPS-certified encryption. For a list of supported platforms, see [external link] http://www.sybase.com/detail?id=1061806.

SQL/2008 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, which is unreadable.

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

The following example decrypts data that was encrypted using the raw format. The data was encrypted with encryption key TheEncryptionKey and the initialization vector ThisIsTheIV.

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