如何在sql server中加密/解密字符串 [英] How to encrypt/decrypt string in sql server
本文介绍了如何在sql server中加密/解密字符串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何使用sql加密和解密任何字符串?
我尝试过以下选项,但无法获得相同的加密和解密值。
DECLARE @ HashThis nvarchar ( 4000 );
SELECT @ HashThis = CONVERT ( nvarchar ( 4000 ),' pass123');
SELECT HASHBYTES(' SHA1', @ HashThis );
GO
选择 EncryptByPassPhrase(' key',' abc')
选择 转换( varchar ( 100 ),DecryptByPassPhrase(' key',' 0x01000000E6A0E10AF0144E38670D9B8E92E6E22787F9CD27B467E253'))
解决方案
您正在向DecryptByPassPhrase发送一个字符串,但该函数需要一个varbinary参数。
声明 @encrypt varbinary ( 200 )
选择 @ encrypt = EncryptByPassPhrase(' key',' abc')
选择 @ encrypt
选择 转换( varchar ( 100 ),DecryptByPassPhrase(' key', @ encrypt ))
结果如下:
------------------- -------------------------------------------------- ------
0x010000009912C2B009EEAC5B7F2A9020D433D3EAFA026004ADD5AAB4
(1行受影响)
----------- -------------------------------------------------- --------------
abc
(1行受影响)
希望这会有所帮助,
Pablo。
- ENCRIPT
CREATE FUNCTION FNC_ENCRIPTION_PW(@str nvarchar(4000))
RETURNS varbinary(8000)
AS
BEGIN
DECLARE @encript varbinary(8000 )
SET @encript = ENCRYPTBYPASSPHRASE('KEY',@ str)
返回(@encript)
结束
GO
--DECRIPT
创建功能FNC_DECRIPTION_PW(@encryp varbinary(8000))
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @decript varbinary(4000)
SET @decript = DECRYPTBYPASSPHRASE('KEY',@ encryp)
RETURN( @decript)
END
GO
- 如何使用
DECLARE @decript nvarchar(4000)
DECLARE @encript varbinary(8000)
SET @encript = [ dbo]。[FNC_ENCRIPTION_PW]('密码')
SELECT @encript
SET @decript = [dbo]。[FNC_DECRIPTION_PW](@ encript)
SELECT @decript
如果帮助...
用于解密
-----------------------
DECLARE @ pwd varchar ( 100 ), @ OriginalPwd as varchar ( 100 ), @ OriginalString as Varchar ( 100 ), @ Count as int , @ iLoop as int , @ temp as int , @ rightChar as int
设置 @ pwd = @ Password
set @ Count = LEN( @ pwd )
set @ iLoop = 1
设置 @ rightChar = 1
set @ OriginalString = ' '
set @ OriginalPwd = ' '
set @ temp = 1
- 添加SET NOCOUNT ON以防止额外的结果集
- 干扰SELECT语句。
- SET NOCOUNT ON;
SELECT @ OriginalString = REVERSE( @ pwd )
WHILE @ temp< = @ Count< pre lang = SQL跨度>>
开始
SELECT @ OriginalPwd = @ OriginalPwd + 字符(ascii( RIGHT ( LEFT ( @ OriginalString , @ rightChar ), 1 )) - @ iLoop)
如果 @ iLoop = 4
开始
set @ iLoop = 0
end
set @ iLoop = @ iLoop + 1
set @ rightChar = @ rightChar + 1
set @ temp = @ temp + 1
结束
选择 password = @ OriginalPwd
PRINT ' 原始字符串是: - ' + @ OriginalPwd
我确定Y能够管理加密.. ..
How do I encrypt and decrypt any string using sql?
I have tried following option, but not able to get same values of encrypted and decrypted values.
DECLARE @HashThis nvarchar(4000);
SELECT @HashThis = CONVERT(nvarchar(4000),'pass123');
SELECT HASHBYTES('SHA1', @HashThis);
GO
select EncryptByPassPhrase('key', 'abc' )
select convert(varchar(100),DecryptByPassPhrase('key', '0x01000000E6A0E10AF0144E38670D9B8E92E6E22787F9CD27B467E253'))
解决方案
You're sending a string to DecryptByPassPhrase, but the function expects a varbinary parameter.
declare @encrypt varbinary(200) select @encrypt = EncryptByPassPhrase('key', 'abc' ) select @encrypt select convert(varchar(100),DecryptByPassPhrase('key', @encrypt ))
The results are:
--------------------------------------------------------------------------- 0x010000009912C2B009EEAC5B7F2A9020D433D3EAFA026004ADD5AAB4 (1 row(s) affected) --------------------------------------------------------------------------- abc (1 row(s) affected)
Hope this helps,
Pablo.
--ENCRIPT
CREATE FUNCTION FNC_ENCRIPTION_PW(@str nvarchar(4000))
RETURNS varbinary(8000)
AS
BEGIN
DECLARE @encript varbinary(8000)
SET @encript = ENCRYPTBYPASSPHRASE('KEY',@str)
RETURN (@encript)
END
GO
--DECRIPT
CREATE FUNCTION FNC_DECRIPTION_PW(@encryp varbinary(8000))
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @decript varbinary(4000)
SET @decript = DECRYPTBYPASSPHRASE('KEY',@encryp)
RETURN (@decript)
END
GO
--HOW USE
DECLARE @decript nvarchar(4000)
DECLARE @encript varbinary(8000)
SET @encript = [dbo].[FNC_ENCRIPTION_PW]('password')
SELECT @encript
SET @decript = [dbo].[FNC_DECRIPTION_PW](@encript)
SELECT @decript
If Help... For decrypt -----------------------
DECLARE @pwd varchar(100),@OriginalPwd as varchar(100),@OriginalString as Varchar(100),@Count as int,@iLoop as int,@temp as int,@rightChar as int Set @pwd =@Password set @Count =LEN (@pwd) set @iLoop = 1 Set @rightChar = 1 set @OriginalString = '' set @OriginalPwd='' set @temp = 1 -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. --SET NOCOUNT ON; SELECT @OriginalString= REVERSE(@pwd) WHILE @temp<=@Count <pre lang="SQL"> begin SELECT @OriginalPwd = @OriginalPwd + Char(ascii(RIGHT(LEFT(@OriginalString,@rightChar),1))-@iLoop) if @iLoop=4 begin set @iLoop=0 end set @iLoop=@iLoop+1 set @rightChar=@rightChar+1 set @temp=@temp+1 END select password=@OriginalPwd PRINT 'Original String Is :- ' +@OriginalPwd
And i M Sure Y Are able To Manage It For Encrypt....
这篇关于如何在sql server中加密/解密字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文