SQL Server的EncryptByKey(..)函数的@cleartext(第二个)参数的实际数据类型是什么? [英] What is the actual data type of the @cleartext (2nd) param of SQL Server's EncryptByKey(..) function?

问题描述

下面我要问的是:此SQL函数的@cleartext参数的实际数据类型是什么? >> ENCRYPTBYKEY(..)- http://msdn.microsoft.com/en-us/library/ms174361. aspx

Pointedly what I'm asking below is: What is the actual data type of the @cleartext parameter of this SQL function? >> ENCRYPTBYKEY (..) - http://msdn.microsoft.com/en-us/library/ms174361.aspx

(如果您在此行下阅读，您可以遵循历史和推理.我认为这比第一次出现时要棘手.)

(If you read below this line you can follow the history and reasoning. I think it's trickier than it first appears.)

SQL Server文档声明了@cleartext(2nd)参数" rel ="nofollow noreferrer"> EncryptByKey(..)函数可以接受许多不同的类型:

The SQL Server documentation states the @cleartext (2nd) parameter of the EncryptByKey(..) function can accept a number of various types:

EncryptByKey(@key_GUID， @cleartext [，@add_authenticator，@authenticator])

EncryptByKey (@key_GUID , @cleartext [, @add_authenticator, @authenticator] )

@cleartext
是类型的变量 nvarchar，char，varchar，binary， 包含数据的varbinary或nchar 用密钥加密.

@cleartext
Is a variable of type nvarchar, char, varchar, binary, varbinary, or nchar that contains data that is to be encrypted with the key.

^ ^ ^ ^ ^ ^ ^ ^ ^ ^-----但是其实际声明的数据类型是什么? ...

如果我要创建一个自定义函数(与上面给出的EncryptByKey示例完全分开)我要给哪种实际数据类型一个自定义参数，以便它将以相同的方式接受所有相同的类型?

If I were to create a custom function (totally separate from the EncryptByKey example given above) what actual data type do I give a custom parameter so it will accept all those same types in the same manner?

我实际上是在自定义包装SQL EncryptByKey函数 UDF函数和我想重新创建相同的 传递给的参数类型 它.这就是我要按类型创建完全相同的参数的原因.

Edit 1: I'm actually wrapping the SQL EncryptByKey function in a custom UDF function and I want to recreate the same parameter types to pass through to it. This is the reasoning behind my want to create exact same params by type.

如果尝试使用sql_variant，则会导致错误

Edit 2: If I try using sql_variant it results in the error

MSG 8116，第16级，状态1，程序 EncryptWrapper，第17行参数数据 类型sql_variant对以下内容无效 EncryptByKey函数的参数2.

Msg 8116, Level 16, State 1, Procedure EncryptWrapper, Line 17 Argument data type sql_variant is invalid for argument 2 of EncryptByKey function.

修改3:

这是我的自定义包装函数-以及直接的问题.直接将其传递给EncryptByKey的@cleartext数据类型应该是什么?

Here's my custom wrapper function - and the direct problem. What should the data type of @cleartext be for direct pass through to EncryptByKey?

ALTER FUNCTION [dbo].[EncryptWrapper]
(
 @key_GUID uniqueidentifier,
 @cleartext -- ???????????  <<< WHAT TYPE ????????????????
 @add_authenticator int = 0,
 @authenticator sysname = NULL
)
RETURNS varbinary(8000)
AS
BEGIN

    -- //Calling a SQL Server builtin function. 
    -- //Second param @cleartext is the problem. What data type should it be?
 Return EncryptByKey(@key_GUID, @cleartext, @add_authenticator, @authenticator)

END

注意:我不必使用CAST或CONVERT-我只需要为@cleartext参数使用正确的数据类型.

Note: I shouldn't have to use CAST or CONVERT - I only need to use the proper data type for my @cleartext param.

编辑4 :发现EncryptByKey(..)@cleartext参数是不是以下类型:

Edit 4: Discovered the EncryptByKey(..) @cleartext parameter is not the following types:

• sql_variant-通过时引发错误
• varbinary-过于严格-不允许传递EncryptByKey(..)接受的文本类型
• sysname，nvarchar，varchar-奇怪的行为-倾向于仅采用自变量文本或某些内容的第一个字符

• sql_variant- raises error when passed
• varbinary- too restrictive- doesn't allow passing of the text types otherwise accepted by EncryptByKey(..)
• sysname, nvarchar, varchar- weird behaviour- tends to take only the first character of the argument text or something

推荐答案

ENCRYPTBYKEY()几乎可以肯定不是用普通的T-SQL编写的.不需要遵循T-SQL数据键入规则.

ENCRYPTBYKEY() almost certainly isn't written in vanilla T-SQL. It doesn't need to follow T-SQL data typing rules.

也就是说，如果要为其编写包装，请按照KM的建议，对@cleartext参数使用SQL_VARIANT.

That said, if you want to write a wrapper for it, use SQL_VARIANT for the @cleartext parameter, just as KM suggested.

如果ENCRYPTBYKEY()对@cleartext的最大长度不敏感，则可以将所有CHAR/VARCHARs更改为VARCHAR(8000)，将所有NCHAR/NVARCHARs更改为NVACHAR(4000).

If ENCRYPTBYKEY() is not sensitive to the max length of @cleartext, you could munge all CHAR/VARCHARs to VARCHAR(8000), and all NCHAR/NVARCHARs to NVACHAR(4000).

否则，您可能是SOL:任何尊重最大长度的数据类型转换(例如CHAR(10)与CHAR(20))都将需要动态SQL，因此您必须将其编写为存储过程，而不是将其编写为存储过程.比功能.那时，它实际上不再是包装器了.

Otherwise you may be SOL: any data type conversion that respects maximum length--eg, CHAR(10) vs CHAR(20)--will require dynamic SQL, so you would have to write it as a stored procedure, rather than a function. At that point, it's not really a wrapper anymore.

这篇关于SQL Server的EncryptByKey(..)函数的@cleartext(第二个)参数的实际数据类型是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！