在MS SQL Server 2008中为AES加密创建存储过程 [英] Create a Stored Procedure for AES Encryption in MS SQL Server 2008

问题描述

我有一个SQL Server 2008表，其结构类似于以下内容:

I have an SQL Server 2008 table with a structure similar to the following:

ID int PRIMARY KEY IDENTITY(1,1)
Name nvarchar(100)
LongText ntext

我想要实现的目标很简单.在将数据插入此表之前，我想使用AES_192算法对LongText进行加密.我正在使用以下SP加密数据:

What I am trying to achieve is simple. Before inserting data inside this table, I want to encrypt the LongText using AES_192 algorithm. I am using the following SP to encrypt data:

create proc sp_Encrypt_LongText
@rawText ntext = null,
@encryptedText nvarchar(max) output
as
begin
OPEN SYMMETRIC KEY Encryption_Symmetric_Key
DECRYPTION BY CERTIFICATE Encryption_Certificate WITH PASSWORD = 'mypassword'
set @encryptedText = ENCRYPTBYKEY(KEY_GUID(N'Encryption_Symmetric_Key'), cast(@rawText as nvarchar(max)))
CLOSE SYMMETRIC KEY Encryption_Symmetric_Key    
end

为了解密，我创建了以下SP:

and for decryption, I have created the following SP:

alter proc sp_Decrypt_LongText
@encryptedText ntext = null,
@decryptedText varchar(max) output
as
begin
    OPEN SYMMETRIC KEY Encryption_Symmetric_Key
    DECRYPTION BY CERTIFICATE Encryption_Certificate WITH PASSWORD = 'mypassword'
    set @decryptedText = cast(DECRYPTBYKEY(cast(@encryptedText as nvarchar(max))) as varchar(max))
    CLOSE SYMMETRIC KEY Encryption_Symmetric_Key
end

当我使用exec命令时，这些程序似乎运行良好.到现在为止还挺好.问题是使用存储过程将数据插入到表中并从表中提取数据.每个插入和选择一个.到目前为止，我所拥有的如下:

The procedures seem to work fine when I use the exec command. So far, so good. The problem is that the data is inserted and fetched inside the table using stored procedures; one each for insert and select. What I have as of now is as follows:

用于插入:

create proc sp_InsertData
@Name nvarchar(100),
@LongText ntext = NULL
as
INSERT INTO TABLE tbl VALUES (@Name, @LongText)

用于获取

create proc sp_FindDataById
@Id int
as
SELECT ID, Name, LongText from tbl where ID=@Id

我的问题是，如何在这些SP中插入加密/解密过程以使其正常工作?我研究了几篇文章来实现这一目标，但是我一直遇到一个问题或另一个问题.主要是因为 ntext 数据类型.也许我可能在这里走错了路.任何帮助都将受到赞赏.

My question is, how do I plug the encryption/decryption procedures inside these SPs to make them work?. I have looked into several articles for achieving this, but I keep running into one issue or another; mostly because of the ntext datatype. Or maybe I might be going on the wrong path here. Any kind of help is appreciated.

PS:由于数据库管理员指定的某些原因，我无法将 LongText 的数据类型从 ntext 更改为 nvarchar 或 varchar .因此，所有的转换都将应用到该过程中.

PS: Due to some reasons specified by the DBAs, I can't change the data type of LongText from ntext to nvarchar or varchar. Hence, all the casting is applied in the procedures.

推荐答案

好的，所以我设法说服DBA将数据转移到具有varbinary(max)数据类型的新列中.然后，在将值加密之后，将其转移到新列中，然后删除较旧的列，并将新名称重命名为旧名称.做了一些工作，但是现在一切运行顺利.我设法创建了一个存储过程和两个函数以进一步模块化脚本.

Okay, so I managed to convince the DBAs to have the data transferred to a new column with varbinary(max) data type. Then I transferred the values into this new column after encrypting them, and then dropped the older column and renamed the new one to the old one's name. Took some work, but everything is running smoothly now. I managed to create a stored procedure and two functions to further modularize the scripts.

用于打开对称密钥

CREATE PROCEDURE sp_OpenEncryptionKeys  
AS  
BEGIN  
    SET NOCOUNT ON;  

    BEGIN TRY  
        OPEN SYMMETRIC KEY Encryption_Symmetric_Key  
        DECRYPTION BY CERTIFICATE Encryption_Certificate 
    END TRY  
    BEGIN CATCH  
        --catch
    END CATCH  
END

用于加密:

CREATE FUNCTION Encrypt
(  
    @ValueToEncrypt varchar(max)  
)  
RETURNS varbinary(max)  
AS  
BEGIN  
    -- Declare the return variable here  
    DECLARE @Result varbinary(max)  
    SET @Result = EncryptByKey(Key_GUID('My_Encryption_Symmetric_Key'), @ValueToEncrypt)  
    -- Return the result of the function  
    RETURN @Result  
END

用于解密:

CREATE FUNCTION Decrypt
(  
    @ValueToDecrypt varbinary(max)  
)  
RETURNS varchar(max)  
AS  
BEGIN  
    -- Declare the return variable here  
    DECLARE @Result varchar(max)  
    SET @Result = DecryptByKey(@ValueToDecrypt)  
    -- Return the result of the function  
    RETURN @Result  
END

用于插入

exec sp_OpenEncryptionKeys  
INSERT INTO tbl VALUES ('Name', Encrypt('some text here'))

用于获取

exec sp_OpenEncryptionKeys  
SELECT ID, Decrypt(LongText) from tbl

希望这对某人有帮助.

这篇关于在MS SQL Server 2008中为AES加密创建存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！