Click here to Skip to main content
14,767,159 members
Please Sign up or sign in to vote.
1.70/5 (3 votes)
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'))
Posted
Updated 19-Oct-17 22:54pm
Comments
Pablo Aliskevicius 1-May-12 10:19am
   
Reason for my vote of 5
This can happen to anyone.
Member 11216458 11-Apr-17 3:06am
   
how to convert hashpassword into string format using sql

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.
   
Comments
Mukund Thakker 2-May-12 0:42am
   
declare @encrypt varbinary(200)
select @encrypt = EncryptByPassPhrase('key', 'abc' )
select @encrypt

If you encrypt with following script it gives different output every time. if I apply the same it gives NULL result
Pablo Aliskevicius 2-May-12 1:46am
   
Different output every time is expected. Some encryption algorithms introduce random noise in the encrypted string; this makes them harder to break. The whole issue of encryption, with concepts like 'evidence' and 'enthropy' (which have, in the context of encryption, different meanings than their usual ones) has filled dozens of books.
Mukund Thakker 3-May-12 5:02am
   
THIS IS NOT BEEN ACCEPTED.
Pablo Aliskevicius 3-May-12 7:18am
   
Why not?
Rakesh N Bhavsar 6-Jul-12 4:51am
   
I am agree with Mukund Thakker.
"When we run both functions in a single query it works fine. But when we run it using separate sql scripts it will not work."

Scenario is as follows:
I have created two different stored procedures each one for EncryptByPassPhrase('key', 'abc' ) and another for convert(varchar(100),DecryptByPassPhrase('key', @encrypt))
I am storing procedure 1 result into database.
Procedure 2 will retrived stored encrypted varbinary value and do decryption and it is returning 'NULL' value.
Frankly I din't get the exact use of sql 'DecryptByPassPhrase()' function.
--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
   
Comments
Richard Deeming 24-Sep-15 9:21am
   
This is a solved question from over three years ago. Your answer adds nothing to the accepted solution.
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....
   
Comments
Richard Deeming 24-Sep-15 9:21am
   
This is a solved question from over three years ago. Your answer adds nothing to the accepted solution.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900