参数化（始终加密）-内部存储过程 [英] Parameterization(Always Encrypted)- Inside stored proc

问题描述

我有一种情况，我需要在proc中使用用于始终加密列的文字（硬编码字符串），由于此操作失败并出现以下错误，

I have a scenario where i need to have literals(hard coded strings) inside proc used against "Always Encrypted" columns, Since this fails with the following error,

操作符类型冲突：varchar与nvarchar（20）不兼容
使用（encryption_type ='DETERMINISTIC'，
encryption_algorithm_name ='AEAD_AES_256_CBC_HMAC_SHA_256'，
column_encryption_key_name =' CEK_Auto4'，
column_encryption_key_database_name ='DBName'）

Operand type clash: varchar is incompatible with nvarchar(20) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto4', column_encryption_key_database_name = 'DBName')

我正在尝试对存储过程中始终加密的参数进行设置，类似于以下内容

I am trying to do Parameterization for Always Encrypted within the stored proc, similar to below

GO
    CREATE PROCEDURE InsertProc  
    @Var1 nVarchar(20)  
    As  
    BEGIN     
        DECLARE @Plaintext nvarchar(20)='testText' 

        INSERT INTO testClass(EncryptedCol1,EncryptedCol2,NonEncryptedCol)
        VALUES (@Plaintext,@Var1,default)
    END

我还启用了参数化功能，还启用了连接的列加密设置。创建过程时仍然出现以下错误，

I have also enabled parameterization and also enabled column encryption setting for connection. Still getting the following error when creating procedure,

过程sp_describe_parameter_encryption，第1行[批处理开始行
4]语句无法准备。
执行批处理时发生错误。错误消息是：内部错误。
sp_describe_parameter_encryption返回的结果集中缺少
参数'@ p3467a2cdc3d547a3be48f46dfc7e9580'的元数据在语句或
过程中-这里的proc代码->'丢失了。

Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 4] Statement(s) could not be prepared. An error occurred while executing batch. Error message is: Internal error. Metadata for parameter '@p3467a2cdc3d547a3be48f46dfc7e9580' in statement or procedure<--my proc code here--> ' is missing in resultset returned by sp_describe_parameter_encryption.

注意：如果我手动运行proc中的脚本，我仍然可以执行并插入。但是Proc的创建是有问题的

NOTE:I still could execute and insert if i manually run the script which is inside the proc.But Proc creation is issue

可以帮忙一些修复或提供替代解决方案的方法。

Could some one help to fix or provide alternative solution.

推荐答案

我找到了解决方法，可以使用硬编码

I found a work around for using hard coded strings in procedures.

1. 使用nVarchar列创建表（注意：加密的nvarchar与加密的varchar不兼容，也无法分配从较高的数据长度到较低的数据长度的加密值（例如，您无法将nVarchar（max）的加密变量/列分配给nVarchar（20）的加密列。因此，请选择较小的大小。）


2. 使用与以下相同的加密类型和密钥对列进行加密


3. 现在，您可以使用表中的值来插入，更新，比较等等。操作。您还可以在凝聚，isull等功能中使用。

示例：

使用加密列创建表

CREATE TABLE [dbo].[Encrypted_nVarchar_256](
    [SNo] [smallint] IDENTITY(-32768,1) NOT NULL,
    [EncryptValue] [nvarchar](256) COLLATE Latin1_General_BIN2 
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_key], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
        [Value_Description] [nvarchar](256) NULL,
     CONSTRAINT [Pk_Enc_nVar_256] PRIMARY KEY CLUSTERED 
    (
        [SNo] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

将文本插入要在过程中使用的值的加密列中。

Insert text into encrypted column the value that you would required to be used inside procedures.

DECLARE @EncryptValue  nvarchar(256) ='';
INSERT INTO Encrypted_nVarchar_256(EncryptValue,[Value_Description]) VALUES(@EncryptValue,'Empty string')
DECLARE @EncryptValue1  nvarchar(256) ='Some string you would like to hard code';
INSERT INTO Encrypted_nVarchar_256(EncryptValue,[Value_Description]) VALUES(@EncryptValue1,'Your description')
--more rows as you need
GO

现在您可以在存储过程中使用

Now you could consume in stored procedure

 CREATE PROCEDURE InsertProc  
    @Var1 nVarchar(20)  
    As  
    BEGIN    
        --Passing hard coded ''(Empty string) as insert value
        INSERT INTO testTable(EncryptedCol1,EncryptedCol2,NonEncryptedCol)
        SELECT TOP 1 EncryptValue,@Var1,"some string" FROM Encrypted_nVarchar_256 where Sno=-32768
        --Comparing some hard coded string
        SELECT * from  testTable
        where EncryptedCol1=(SELECT TOP 1 EncryptValue FROM Encrypted_nVarchar_256 
 where Sno=-32768)
         --Using in functions
         SELECT COALESCE(EncryptedCol1,(SELECT TOP 1 EncryptValue FROM Encrypted_nVarchar_256  where Sno=-32768)) as [new col name] from  testable

    END

Voila ！！！现在您只需稍作改动就可以做任何事情。

Voila!!! now you could do anything with just a little twist.

这篇关于参数化（始终加密）-内部存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！