asked on

Confounding problem with EncryptByPassPhrase function in SQL?

I am absolutely stumped as to why this is happening.

If I use EncryptByPassPhrase() and feed it the value of a variable I'm passing to my stored proc, it only encrypts the first letter of the string and nothing else. I discover this by decrypting the resulting varbinary value. Why is it only encrypting the first letter?

HOWEVER if I hard code the string directly in the stored proc (see alternative in the attached code), it encrypts the entire string successfully.

Is this a bug, or am I missing something? I want to pass a string and encrypt the entire string. How can I do this successfully?

ALTER PROCEDURE [dbo].[myStoredProc]
    @Email nvarchar(256),
    @UserPassword nvarchar(256)
AS
BEGIN
 
-- do not change this passphrase because the encryption is symmetrical
DECLARE @passphrase nvarchar(16)
SET @passphrase = N'Passwords String';
 
DECLARE @PasswordEncode VARBINARY(256)
--******  THE FOLLOWING EXAMPLE ONLY ENCRYPTS THE FIRST LETTER OF THE @UserPassword string *****************
SET @PasswordEncode = EncryptByPassPhrase(@passphrase, @UserPassword);
----***** THIS EXAMPLE CORRECTLY ENCRYPTS THE ENTIRE STRING
-- SET @PasswordEncode = EncryptByPassPhrase(@passphrase, 'my big long string');
 
 
END

Open in new window

BrandonGalderisi

I would recommend NOT encrypting user passwords, rather, hashing them. There is no need to ever decrypt a password.

For user login validation, you simply hash the value they pass in and validate it. Ideally, your hash would be "salted" so that a dictionary attack cannot be run against your user table.

ALTER PROCEDURE [dbo].[myStoredProc]
    @Email nvarchar(256),
    @UserPassword nvarchar(256)
AS
BEGIN
 
DECLARE @Passwordhash BINARY(16)
,@hashString nvarchar(max)
set @HashString = lower(@Email) + @userpassword
--******  THE FOLLOWING EXAMPLE ONLY ENCRYPTS THE FIRST LETTER OF THE @UserPassword string *****************
SET @Passwordhash = hashbytes('SHA1',@HashString)
 
 
 
 
END

Open in new window

ASKER CERTIFIED SOLUTION

cacklebunny

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BrandonGalderisi

I think you are wrong about what you've found as well. As you can see from this example (and as document in http://technet.microsoft.com/en-us/library/ms190357.aspx), you CAN pass nvarchar into the function.

drop procedure up_TESTENC 
go
create procedure up_TESTENC 
 
    @Email nvarchar(256),
    @UserPassword nvarchar(256)
as
declare    @PasswordEncode VARBINARY(256)
 
 
 
DECLARE @passphrase nvarchar(16)
SET @passphrase = N'D795ECA4-285E-44CD-8078-CDE8DACCF20F';
 
--******  THE FOLLOWING EXAMPLE ONLY ENCRYPTS THE FIRST LETTER OF THE @UserPassword string *****************
SET @PasswordEncode = EncryptByPassPhrase(@passphrase, @UserPassword);
----***** THIS EXAMPLE CORRECTLY ENCRYPTS THE ENTIRE STRING
-- SET @PasswordEncode = EncryptByPassPhrase(@passphrase, 'my big long string');
 
SElect cast(@PasswordEncode as nvarchar(max))
SElect cast(decryptByPassPhrase(@passphrase, @PasswordEncode) as nvarchar(max))
 
go
exec up_TESTENC @Email=N'', @UserPassword = 'ASDF@!#%SDVAW%WR'

Open in new window