使用EncryptByPassPhrase创建DECRYPTBYPASSPHRASE后无法正常工作 [英] DECRYPTBYPASSPHRASE is not working after creating them with EncryptByPassPhrase

问题描述

我有一张桌子:

CREATE TABLE TempHashedValues(
HashedValues varbinary(200)
)

现在，我正在使用插入加密值，以便以后可以使用:

Now, I am inserting encrypted values to it using, so that could be used later:

Insert into TempHashedValues values ( EncryptByPassPhrase('key', 'SecretEncoded' ))

现在，当我尝试使用相同的密钥解密它们时:

Now when I am trying to decrypt them using same key:

Select TOP 1 DECRYPTBYPASSPHRASE('key',HashedValues) from  TempHashedValues

我只是找回二进制值，而不是我加密的值！！

I am just getting the binary value back , not the value I encrypted !!

我想念什么?

推荐答案

如此处所述 http://sqlity.net/zh/2530/decryptbypassphrase/ ENCRYPTBYPASSPHRASE 以 VARBINARY(8000)数据类型返回加密的值.除了例如SQL_VARIANT之外，该数据类型不携带有关原始数据类型的任何信息.因此， DECRYPTBYPASSPHRASE 也将返回 VARBINARY(8000)值.您必须将其投放:

As stated here http://sqlity.net/en/2530/decryptbypassphrase/ ENCRYPTBYPASSPHRASE returns the encrypted value as a VARBINARY(8000) data type. That data type, other than for example SQL_VARIANT does not carry any information about the originating data type. Therefore, the DECRYPTBYPASSPHRASE also returns a VARBINARY(8000) value. You have to cast it :

Select TOP 1 (CAST(DECRYPTBYPASSPHRASE('key',HashedValues) AS VARCHAR(8000))) from  TempHashedValues

这篇关于使用EncryptByPassPhrase创建DECRYPTBYPASSPHRASE后无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！