SQL資料太長，導致加解密出來資料會被截斷!
是Microsoft SQL Server的Bug，還是那裡出了問題呢?
前言
我們系統有使用SQL DECRYPTBYKEY/ENCRYPTBYKEY 來做加解密!
前陣子同事說，為何欄位資料比較長的就無法順利解密，會被截斷。
心想，不會吧! 那個方式產品從SQL 2005就用了。不過，這次是把「地址」拿來加解密，之前都只是把薪資加解密而已。
測試
於是我就寫了一個測試程式來測試，如下，
--1.CREATE SYMMETRIC KEY
CREATE SYMMETRIC KEY DB_KEY1 WITH ALGORITHM = RC2
ENCRYPTION BY PASSWORD = 'rainmaker'
GO
--2.Open Key and testing ENCRYPTBYKEY & DECRYPTBYKEY
OPEN SYMMETRIC KEY DB_KEY1 DECRYPTION BY PASSWORD = 'rainmaker';
DECLARE @KeyGUID AS UNIQUEIDENTIFIER
SELECT @KeyGUID = KEY_GUID('DB_KEY1');
--定義@MYDATA2為NVARCHAR(4000)
DECLARE @MYDATA2 NVARCHAR(4000) = N'012345678901234567890123456789AAAAAAAAAA'
--將資料加密再解密，看看跟字串會不會被截掉
SELECT CAST(DECRYPTBYKEY(ENCRYPTBYKEY(@KeyGUID, CAST(@MYDATA2 AS VARBINARY(8000)) ) ) AS NVARCHAR)
AS [DECRYPT_CAST_BIN_DATA];
--定義@MYDATA3為VARCHAR(4000)
DECLARE @MYDATA3 VARCHAR(4000) = '012345678901234567890123456789AAAAAAAAAA'
--將資料加密再解密，看看跟字串會不會被截掉
SELECT CAST(DECRYPTBYKEY(ENCRYPTBYKEY(@KeyGUID, CAST(@MYDATA3 AS VARBINARY(8000)) ) ) AS VARCHAR)
AS [DECRYPT_CAST_BIN_DATA];
--3.Close Key
CLOSE SYMMETRIC KEY DB_KEY1;
從上面的結果發現，不管是NVARCHAR OR VARCHAR只要超出30個字，就會被截掉，所以只能顯示30個字。
解決
想說是這是Microsoft SQL Server的BUG嗎?
後來問Microsoft才知道是因為CHAR, VARCHAR, NCHAR, NVARCHAR使用在CAST or CONVERT時沒指定長度的話，預設就是30個字。
另外，如果宣告時，沒有指定長度的話，是1個字哦!
所以如果您測試以下的SQL會發現，@A只會放一個字而已哦~~
DECLARE @A VARCHAR = '12335';
SELECT @A --RETURN '1'
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.
噹~~
後來改成VARCHAR(MAX), NVARCHAR(MAX)就解掉了問題! 如下，
--1.CREATE SYMMETRIC KEY
REATE SYMMETRIC KEY DB_KEY1 WITH ALGORITHM = RC2
NCRYPTION BY PASSWORD = 'rainmaker'
O
-2.Open Key and testing ENCRYPTBYKEY & DECRYPTBYKEY
PEN SYMMETRIC KEY DB_KEY1 DECRYPTION BY PASSWORD = 'rainmaker';
ECLARE @KeyGUID AS UNIQUEIDENTIFIER
ELECT @KeyGUID = KEY_GUID('DB_KEY1');
-定義@MYDATA2為NVARCHAR(4000)
ECLARE @MYDATA2 NVARCHAR(4000) = N'012345678901234567890123456789AAAAAAAAAA'
-將資料加密再解密，看看跟字串會不會被截掉
ELECT CAST(DECRYPTBYKEY(ENCRYPTBYKEY(@KeyGUID, CAST(@MYDATA2 AS VARBINARY(8000)) ) )
S NVARCHAR(MAX))
S [DECRYPT_CAST_BIN_DATA];
-定義@MYDATA3為VARCHAR(4000)
ECLARE @MYDATA3 VARCHAR(4000) = '012345678901234567890123456789AAAAAAAAAA'
-將資料加密再解密，看看跟字串會不會被截掉
ELECT CAST(DECRYPTBYKEY(ENCRYPTBYKEY(@KeyGUID, CAST(@MYDATA3 AS VARBINARY(8000)) ) )
S VARCHAR(MAX))
S [DECRYPT_CAST_BIN_DATA];
-3.Close Key
LOSE SYMMETRIC KEY DB_KEY1;
在此分享給大家!
經過這次的教訓，下次使用CAST OR CONVERT時，不敢偷懶不設定長度了~~~
Hi,
亂馬客Blog已移到了 「亂馬客 : Re:從零開始的軟體開發生活」
請大家繼續支持 ^_^
