如何解决DECRYPTBYPASSPHRASE无法解密varchar类型列加密值的问题

嘿，我来帮你搞定这个解密失败的问题！咱们先聊聊为啥会出现这个情况，再给你正确的解法。

问题根源 #

EncryptByPassPhrase函数返回的是varbinary类型的加密数据，你在插入login_details时，用convert(nvarchar(100), EncryptByPassPhrase('kiran','XXX'),1)把它转换成了十六进制字符串（比如类似0x12AB34CD这样的文本格式），然后存在了varchar类型的password字段里。

但你之前的解密语句直接把varchar转成nvarchar传给DECRYPTBYPASSPHRASE，这个函数只认varbinary类型的加密数据，不认字符串格式的十六进制值，所以自然解不出正确结果。

正确的解密方法 #

你需要先把varchar字段里的十六进制字符串转回varbinary类型，再传入DECRYPTBYPASSPHRASE解密。具体来说就是用CONVERT(varbinary, password, 1)转换，这里的1是转换选项，对应“带0x前缀的十六进制字符串”格式。

正确的查询语句如下：

SELECT 
    CONVERT(varchar(20), DECRYPTBYPASSPHRASE('kiran', CONVERT(varbinary, password, 1))) AS decrypted_password,
    * 
FROM login_details

额外说明 #

• 为啥login_details1能正常解密？因为它直接把EncryptByPassPhrase返回的varbinary数据存在了varbinary类型的字段里，解密时类型完全匹配，所以没有问题。
• 建议：如果业务允许的话，尽量优先用varbinary类型存储加密数据，避免字符串和二进制来回转换带来的错误，也更节省存储。

内容的提问来源于stack exchange，提问作者Kiran nanaware