[SQL]CAST/CONVERT成CHAR, VARCHAR, NCHAR, NVARCHAR要注意的地方!

  • 13054
  • 0
  • SQL
  • 2012-03-22

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;

DecryptByKey

從上面的結果發現,不管是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;

image

在此分享給大家! 

經過這次的教訓,下次使用CAST OR CONVERT時,不敢偷懶不設定長度了~~~

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活

請大家繼續支持 ^_^