Click here to Skip to main content
Rate this: bad
good
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 4:17am
Comments
Pablo Aliskevicius at 1-May-12 10:19am
   
Reason for my vote of 5
This can happen to anyone.

1 solution

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 at 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 at 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 at 3-May-12 5:02am
   
THIS IS NOT BEEN ACCEPTED.
Pablo Aliskevicius at 3-May-12 7:18am
   
Why not?
Rakesh Bhavsar at 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.

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 8,438
1 OriginalGriff 6,491
2 Peter Leow 3,567
3 Zoltán Zörgő 3,326
4 Richard MacCutchan 2,397


Advertise | Privacy | Mobile
Web04 | 2.8.150123.1 | Last Updated 12 Aug 2014
Copyright © CodeProject, 1999-2015
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