存储过程对数据进行加密 [英] Stored procedure to encrypt data

问题描述

我正在使用创建的存储过程:

I'm using a store procedure I created:

USE [database_name]
GO
OPEN SYMMETRIC KEY Password_Key
   DECRYPTION BY CERTIFICATE PasswordCertificate;
GO
CREATE PROCEDURE dbo.AddPassword
    @ID int,
    @Password varchar(50)
WITH ENCRYPTION
AS
INSERT INTO .EncryptionTest (ID,Password,Password_Encrypted)
VALUES (@ID,@Password,EncryptByKey(Key_GUID('Password_Key')
    , @Password, 1, HashBytes('SHA1', CONVERT(varbinary,@ID))))
GO

执行时:

USE [database_name]
GO
EXEC dbo.AddPassword @ID = 1, @Password = 'Test';
GO

它添加一行，其中 ID = 1， Password ='Test'， Password_Encrypted = NULL.为什么最后一个给出的是null而不是对字符串'Test'的加密?

it adds a row where ID = 1, Password = 'Test', Password_Encrypted = NULL. Why is the last one giving null instead of an encryption of the string 'Test'?

推荐答案

我怀疑您正在从其他范围执行该过程，或者自创建该过程以来，您的连接已断开.当然，您不能期望用户每次执行存储过程时都会打开对称密钥，因此它应该在过程内部，而不是朝着创建过程的一步.

I suspect you are executing the procedure from a different scope, or your connection has been severed since creating the procedure. Of course you can't expect that users will open the symmetric key every time they execute the stored procedure, so that should probably be inside the procedure instead of as a step toward creating the procedure.

ALTER PROCEDURE dbo.AddPassword
  @ID int,
  @Password varchar(50)
WITH ENCRYPTION
AS
BEGIN
  SET NOCOUNT ON;

  OPEN SYMMETRIC KEY Password_Key
   DECRYPTION BY CERTIFICATE PasswordCertificate;

  -- for debugging 101:    
  SELECT EncryptByKey(Key_GUID('Password_Key')
    , @Password, 1, HashBytes('SHA1', CONVERT(varbinary(128),@ID)));

  INSERT INTO dbo.EncryptionTest(ID,Password,Password_Encrypted)
  VALUES (@ID,@Password,EncryptByKey(Key_GUID('Password_Key')
    , @Password, 1, HashBytes('SHA1', CONVERT(varbinary(128),@ID))));
END
GO

这篇关于存储过程对数据进行加密的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！