EncryptByPassPhrase problem

Question

EncryptByPassPhrase problem

Shailesh Chaudhary

Old Hand

Points: 377
More actions
May 8, 2009 at 4:15 pm

#221022

Hi,
I am using EncryptByPassPhrase() function to encrypt the password and it is encrypting it correctly but the problem is, it is inserting some junk kind of characters into the field which is conflicting with my XML data. Can anybody tell me how to put the simple text or number using EncryptByPassPhrase() function and then decrypt it easily. My stored procedure is below: Thanks in advance
ALTER PROCEDURE [dbo].[RegisterUser]
@userName varchar(40),
@passwordHash varchar(50)
AS
BEGIN
Declare @encrystaff varchar(80);
DECLARE @PassphraseEnteredByUser nvarchar(128);
SET @PassphraseEnteredByUser = 'sometext';
-- here,we use asymmetric encryption to cipher the hashed password
SET @encrystaff=Convert(varchar(80),EncryptByPassPhrase( @PassphraseEnteredByUser,@passwordHash));
INSERT INTO Users VALUES(@userName,@encrystaff )
END

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

EncryptByPassPhrase() returns a varbinary() data type. Change @encrystaff and the subsequent column in the table to varbinary(128).

K. Brian Kelley
@kbriankelley

Shailesh Chaudhary Old Hand Points: 377 More actions · Answer 2

Thanks for your response.

Database is already in production and cannot make any change in column data types.

Thanks

shailesh

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

Actually, I'm sorry, it should be varbinary(256). nvarchar is 2 bytes per character. The varbinary is not guaranteed to be readable characters, meaning the conversion may store something that doesn't convert properly to the password. That's why the change is required. The EncryptByPassPhrase is going to give a stream of bytes out. So you need something to properly store that stream of bytes, and that's varbinary.

I understand what you're saying about a change to production, but unfortunately, you're doing encryption here. And with encryption, you're working with bits and bytes, not characters. Meaning your implementation probably won't work or won't be accurate as it is right now.

K. Brian Kelley
@kbriankelley