Click here to Skip to main content
12,299,086 members (51,550 online)
Rate this:
 
Please Sign up or sign in to vote.
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 1-May-12 3:17am
Comments
Pablo Aliskevicius 1-May-12 10:19am
   
Reason for my vote of 5
This can happen to anyone.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  
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 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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

--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
  Permalink  
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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

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....
  Permalink  
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160525.2 | Last Updated 24 Sep 2015
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100