针对Nvarchar问题的Sql server非对称密钥解密

Question

我在sql中面临数据加密和解密的问题，下面是我尝试过的场景。

·创建表(@TABLE)，列类型为NVARCHAR(MAX)。(试图加密FirstName、LastName、MiddleName、Country、TIN)

·创建的存储过程用于执行加密，◦存储过程接受输入参数NVARCHAR(MAX)，对值进行加密，将值转换为NVARCHAR(MAX)并返回值。

CREATE PROCEDURE DNB_ENCRYPT    
(   
    @Value NVARCHAR(MAX),
    @EncryptedValue NVARCHAR(MAX) OUTPUT    
)    
AS
BEGIN

    IF NOT EXISTS (SELECT * FROM sys.asymmetric_keys WHERE name = 'ASymKeyPwd')
        CREATE ASYMMETRIC KEY ASymKeyPwd WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = N'18SomeHiddenPassword!96';

    IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = 'SymKey')
        CREATE SYMMETRIC KEY SymKey WITH ALGORITHM = AES_256 ENCRYPTION BY ASYMMETRIC KEY AsymKeyPwd;

    OPEN SYMMETRIC KEY SymKey DECRYPTION BY ASYMMETRIC KEY AsymKeyPwd WITH PASSWORD = N'18SomeHiddenPassword!96'    

    --INSERT INTO TESTTABLE(FIRSTNAME)VALUES(ENCRYPTBYKEY(KEY_GUID('SymKey'), @Value))

    SET @EncryptedValue = CONVERT(NVARCHAR(MAX), EncryptByKey(Key_GUID('SymKey'), @Value))
    CLOSE SYMMETRIC KEY SymKey;
END

·在@TABLE之上创建的视图，查看解密并返回表中的所有值。

CONVERT(VARCHAR(MAX), DECRYPTBYKEYAUTOASYMKEY(ASYMKEY_ID('AsymKeyPwd'), N'18SomeHiddenPassword!96',  PRIMARYTIN))

问题在于解密，一些值被正确地解密，对于很少的值，我看到的是空值。

乔-蠀簴䝌誉ꢁ罫檉-乔

安东尼-蠀簴䝌誉ꢁ罫檉-安东尼

123121312-蠀簴䝌誉ꢁ罫檉-空

IN-蠀簴䝌誉ꢁ罫檉-IN

US-蠀簴䝌誉ꢁ罫檉-空

如果表列类型为var二进制，则整个过程运行良好，但对于nvarchar(max)，很少有值不按预期解密。我无法追踪这个问题。有谁能帮我解决这个问题吗？

谢谢，克里斯

Answer 1

您有语法错误，在解密部分。

( CONVERT(VARCHAR(MAX)，DECRYPTBYKEYAUTOASYMKEY(ASYMKEY_ID('AsymKeyPwd')，N‘18 18SomeHiddenPassword！96’，PRIMARYTIN)

VARCHAR(MAX) NVARCHAR(MAX)，应该是，因为它在所有其他地方都是NVARCHAR(MAX)类型。