之前发表关于关于SQL Server 2005内置加密方法的链接是http://blog.csdn.net/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或百度搜到的相关关键字只有几个