主页 / dba / 问题 / 167920
Accepted
Jason B.
Asked: 2017-03-23 10:33:54 +0800 CST

SQL AES_256 加密和解密

  • 772

我有一个奇怪的加密和解密问题。加密在 SQL 2008 R2 中运行良好，我们使用的是 TRIPLE_DES_3KEY。这在 SQL 2016 中已被弃用，因此我们正在测试 AES_256。我的原始字符串一直被切断，不知道它在做什么或如何解释。我确定我错过了一些东西，但不确定是什么。详情如下。谢谢！

我的对称密钥： 

CREATE SYMMETRIC KEY SK03  
WITH  
    ALGORITHM = AES_256  
    ENCRYPTION BY PASSWORD = 'ThisIsMyAES_256EncryptionTest';

我的表： 

CREATE TABLE [dbo].[_EncryptionTest](  
    [OriginalValue] [varchar](max) NULL,  
    [Encryptedvalue] [varbinary](8000) NULL  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

我的存储过程： 

CREATE PROCEDURE [dbo].[_usp_EncryptionTest]  
    @InputString varchar(MAX),  
    @InputString2 varchar(MAX)  

AS  
BEGIN  
    SET NOCOUNT ON;  

    OPEN SYMMETRIC KEY SK03 DECRYPTION BY PASSWORD='ThisIsMyAES_256EncryptionTest'  

    INSERT INTO _EncryptionTest
        (OriginalValue, Encryptedvalue)
    VALUES (
        @InputString
        ,ENCRYPTBYKEY(Key_GUID('SK03'), CONVERT(VARBINARY,@InputString2))
        )

    CLOSE SYMMETRIC KEY SK03;

END

我的测试 SQL： 

EXEC _usp_EncryptionTest 'Spring is Finally Here! Spring is Finally Here!','Spring is Finally Here! Spring is Finally Here!'

如下图所示，数据已更新到我的表中。

在此处输入图像描述

我的解密 SQL： 

OPEN SYMMETRIC KEY SK03 DECRYPTION BY PASSWORD='ThisIsMyAES_256EncryptionTest'  
SELECT   
    [OriginalValue]   
    ,CONVERT(varchar(MAX), DECRYPTBYKEY([EncryptedValue]))  
FROM  
    _EncryptionTest  
CLOSE SYMMETRIC KEY SK03;

下图应显示带有 decryoted 值截止的结果。 在此处输入图像描述

security sql-server-2016

2 个回答

  1. Best Answer

    尝试按照 https://technet.microsoft.com/en-us/library/ms188362(v=sql.110).aspx将varbinary转换语句中的长度定义为默认为 30。varbinary(max).

    您可以使用以下脚本复制它：

    DECLARE @STRING VARCHAR(100);
SELECT  @STRING = REPLICATE('X', 100);

SELECT  LEN(@STRING) AS String_Length,
        LEN(CONVERT(VARBINARY, @STRING)) AS Varbinary_Length ,
        LEN(CONVERT(VARBINARY(MAX), @STRING)) AS VarbinaryMax_Length;
    • 5

  2. 问题出在加密密码中。对于 AES 256 加密密码长度应为 32。使用下面的代码并检查。

    CREATE SYMMETRIC KEY SK03  
WITH  
    ALGORITHM = AES_256  
    ENCRYPTION BY PASSWORD = 'abcdefghijklmnopqrstuvwxyzasdefb'; 
go
CREATE TABLE [dbo].[_EncryptionTest](  
    [OriginalValue] [varchar](max) NULL,  
    [Encryptedvalue] [varbinary](max) NULL  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go
CREATE PROCEDURE [dbo].[_usp_EncryptionTest]  
    @InputString varchar(MAX),  
    @InputString2 varchar(MAX)  

AS  
BEGIN  
    SET NOCOUNT ON;  

    OPEN SYMMETRIC KEY SK03 DECRYPTION BY PASSWORD='abcdefghijklmnopqrstuvwxyzasdefb'  

    INSERT INTO _EncryptionTest
        (OriginalValue, Encryptedvalue)
    VALUES (
        @InputString
        ,ENCRYPTBYKEY(Key_GUID('SK03'), CONVERT(VARBINARY(max),@InputString2))
        )

    CLOSE SYMMETRIC KEY SK03;

END  
go
EXEC _usp_EncryptionTest 'Spring is Finally Here! Spring is Finally Here!','Spring is Finally Here! Spring is Finally Here!'
go
OPEN SYMMETRIC KEY SK03 DECRYPTION BY PASSWORD='abcdefghijklmnopqrstuvwxyzasdefb'  
SELECT   
    [OriginalValue] ,
    CONVERT(varchar(MAX), DECRYPTBYKEY([EncryptedValue]))
FROM  
    _EncryptionTest  
CLOSE SYMMETRIC KEY SK03;
    • 1

相关问题