SQL des加密算法

[charmzj]

SQL DES加密算法如何控制加密后的字符串长度，目前这个加密后的字符串太长；

还有这个加密方法如何修改成一个加密存储过程和一个解密存储过程？

加密SQL如下：

CREATE SYMMETRIC KEY key1 WITH ALGORITHM=DES
ENCRYPTION BY PASSWORD='abc$123'
GO

OPEN SYMMETRIC KEY key1
DECRYPTION BY PASSWORD='abc$123'

DECLARE @str NVARCHAR(100),@Encrypted VARBINARY(MAX)
SET @str=N'要加密的字串'
SELECT @Encrypted=ENCRYPTBYKEY(KEY_GUID('key1'),@str)
SELEcT '加密'=@Encrypted ,'解密'=CAST(DECRYPTBYKEY(@Encrypted) AS NVARCHAR(100))
CLOSE SYMMETRIC KEY key1
GO
DROP SYMMETRIC KEY key1
GO

[KevinLiu]

引用 4 楼 charmzj 的回复:

@hdhai9451，select sys.fn_VarBinToHexStr(hashbytes('SHA1', '中国上海')); 我怎么解密，我是必须要解密的。而且是URl中的参数。

上面的两句是加密后面的两句是解密的，跟你说的做法有区别。

[KevinLiu]

这个是加密算法的工作原理：http://msdn.microsoft.com/en-us/library/ms345262.aspx Clarification regarding DES algorithms: •DESX was incorrectly named. Symmetric keys created with ALGORITHM = DESX actually use the TRIPLE DES cipher with a 192-bit key. The DESX algorithm is not provided. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. •Symmetric keys created with ALGORITHM = TRIPLE_DES_3KEY use TRIPLE DES with a 192-bit key. •Symmetric keys created with ALGORITHM = TRIPLE_DES use TRIPLE DES with a 128-bit key. 关于存储过程的代码可以参考这个； CREATE PROC decryptaslogin1 WITH EXECUTE AS ‘login1′ AS OPEN SYMMETRIC KEY sensitive_data DECRYPTION BY CERTIFICATE SSNCert; SELECT Employee, CONVERT(VARCHAR, DECRYPTBYKEY(ssn)) FROM employeessn; CLOSE ALL SYMMETRIC KEYS; GO

[Andy__Huang]

使用下面加密码方式，你看是否合适你的要求？

select sys.fn_VarBinToHexStr(hashbytes('MD5', '123456'));
select sys.fn_VarBinToHexStr(hashbytes('SHA1', '中国上海'));

select hashbytes('SHA1', '123456');
select hashbytes('MD5', '123456');

[KevinLiu]

或者像楼上说的 使用其他的加密算法。

[charmzj]

@hdhai9451，select sys.fn_VarBinToHexStr(hashbytes('SHA1', '中国上海')); 我怎么解密，我是必须要解密的。而且是URl中的参数。

[LongRui888]

des算法所产生的加密字符串，是固定长度的，好像不能限制长度的把