关于SQL Server 2005内置加密方法接受输入参数的最大长度

之前发表关于关于SQL Server 2005内置加密方法的链接是http://blog.youkuaiyun.com/huangya0/archive/2008/06/13/2543900.aspx , 但发现它的长度有限制,太长会不成功的加密,我发了email转给我的客户再转去问MS, 相关人员还没有给一个正确的答复.

下边我把我的测试代码贴下来:

--Create a table for test



CREATE TABLE [dbo].[SourceStringTable](

	[ID] [int] NOT NULL,

	[VarcharEngString] [varchar](max) NULL,

	[NVarcharEngString] [nvarchar](max) NULL,

	[NVarcharChnString] [nvarchar](max) NULL,

 CONSTRAINT [PK_SourceStringTable] PRIMARY KEY CLUSTERED 

(

	[ID] 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



-----------------------------------------------------------------------

delete from SourceStringTable

--接受加密的英文字符串(varchar)最大长度是117

--接受加密的英文字符串(nvarchar)最大长度是58

--接受加密的中文字符串(nvarchar)最大长度是58(116 bytes)

declare @VarcharEngString varchar(max)

declare @NVarcharEngString nvarchar(max)

declare @NVarcharChnString nvarchar(max)



--/*Below three string all can encrypt and decrypt correctly*/

--length: 117

set @VarcharEngString='Based on NCSA Mosaic. NCSA Mosaic(TM); was developed at the National Center for Supercomputing Applications at the Un'

--length: 58

set @NVarcharEngString='Based on NCSA Mosaic. NCSA Mosaic(TM); was developed at th'

--length: 58

set @NVarcharChnString='改进产品以满足用户不断增长的需求，所以不应将这些信息视为腾腾讯公司的承诺，另一方面，腾讯公司也不能保证在发布日期之后'

--/*******************/



insert into SourceStringTable(ID, VarcharEngString, NVarcharEngString, NVarcharChnString) 

values(1, @VarcharEngString, @NVarcharEngString, @NVarcharChnString)



--/*Below three string all can not encrypt and decrypt correctly*/

--length: 118

set @VarcharEngString='Based on NCSA Mosaic. NCSA Mosaic(TM); was developed at the National Center for Supercomputing Applications at the Uni'

--length: 59

set @NVarcharEngString='Based on NCSA Mosaic. NCSA Mosaic(TM); was developed at thi'

--length: 59

set @NVarcharChnString='改进产品以满足用户不断增长的需求，所以不应将这些信息视为腾腾讯公司的承诺，另一方面，腾讯公司也不能保证在发布日期之后提'

--/*******************/



insert into SourceStringTable(ID, VarcharEngString, NVarcharEngString, NVarcharChnString) 

values(2, @VarcharEngString, @NVarcharEngString, @NVarcharChnString)



select * from SourceStringTable



-----------------------------------------------------------------------



CREATE TABLE [dbo].[Customer](

	[CustomerID] [int] NULL,

	[Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,

	[City] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,

	[CreditCardType] [varbinary](max) NULL,

	[CreditCardNumber] [varbinary](max) NULL,

	[Notes] [varbinary](max) NULL,

	[Descr] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]



GO



--Create a DB master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'abcd1234';

GO



--Create a Certificate 

CREATE CERTIFICATE User2Certificate 

	AUTHORIZATION ODMSDB_User_Name  ---this parameter should change to your DB user name

    WITH SUBJECT = 'DB testing',

	START_DATE = '10/31/2007',

	EXPIRY_DATE = '10/31/2017';

GO	





--delete all data in this table

delete from Customer



-------------CustomerID=12. this row CAN decrypt correctly----------------------------------------------------

declare @VarcharEngString varchar(max)

declare @NVarcharEngString nvarchar(max)

declare @NVarcharChnString nvarchar(max)



select @VarcharEngString=VarcharEngString,

		@NVarcharEngString=NVarcharEngString,

		@NVarcharChnString=NVarcharChnString

	 from SourceStringTable where ID=1



--print @VarcharEngString

--print @NVarcharEngString

--print @NVarcharChnString



INSERT INTO Customer

VALUES (12, 'John Doe', 'Fairbanks',

EncryptByCert(Cert_ID('User2Certificate'), @VarcharEngString),

EncryptByCert(Cert_ID('User2Certificate'), @NVarcharEngString),

EncryptByCert(Cert_ID('User2Certificate'), @NVarcharChnString),

'Description for test')



select * from Customer where CustomerID=12



SELECT CustomerID, Name, City,

CONVERT(VARCHAR(max), DecryptByCert(Cert_ID('User2Certificate'), CreditCardType) ) as CreditCardType,

CONVERT(NVARCHAR(max), DecryptByCert(Cert_ID('User2Certificate'), CreditCardNumber)) as CreditCardNumber,

CONVERT(NVARCHAR(max), DecryptByCert(Cert_ID('User2Certificate'), Notes) ) as Notes,

Descr

FROM Customer WHERE CustomerID = 12



-------------CustomerID=13, this row CAN NOT decrypt correctly----------------------------------------------------

declare @VarcharEngString2 varchar(max)

declare @NVarcharEngString2 nvarchar(max)

declare @NVarcharChnString2 nvarchar(max)



select @VarcharEngString2=VarcharEngString,

		@NVarcharEngString2=NVarcharEngString,

		@NVarcharChnString2=NVarcharChnString

	 from SourceStringTable where ID=2



INSERT INTO Customer

VALUES (13, 'John Doe', 'Fairbanks',

EncryptByCert(Cert_ID('User2Certificate'), @VarcharEngString2),

EncryptByCert(Cert_ID('User2Certificate'), @NVarcharEngString2),

EncryptByCert(Cert_ID('User2Certificate'), @NVarcharChnString2),

'Description for test')



SELECT CustomerID, Name, City,

CONVERT(VARCHAR(max), DecryptByCert(Cert_ID('User2Certificate'), CreditCardType) ) as CreditCardType,

CONVERT(NVARCHAR(max), DecryptByCert(Cert_ID('User2Certificate'), CreditCardNumber)) as CreditCardNumber,

CONVERT(NVARCHAR(max), DecryptByCert(Cert_ID('User2Certificate'), Notes) ) as Notes,

Descr

FROM Customer WHERE CustomerID = 13



select * from Customer where CustomerID=13





--my question is: Each input parameter of the method EncryptByCert's maximal length when input value under the data type of varchar/nvarchar(english or chinese string)

客户转发回来咨询MS的内容如下:

I just confirmed that the max length of EncryptByCert(…,str) parameter str is 8000 bytes, which is the max length for a varchar/char/nvarchar/nchar data type. I understand that we can specify “max” such as “varchar(max)” so that the varchar data type can have length of 2^31-1 bytes, but in EncryptBycert() function the max length is 8000. I am sorry I did not find any public document that clearly state this.
  

  
    
  
By the way the return value of this function also has a  maximum size of 8,000 bytes.
  

  
    但跟我实际测试的结果不相符,真YM
  

  
   我在网上根本找不到与之相关的问题和知识,google或百度搜到的相关关键字只有几个