fn_varbintohexstr seems to return different values when used in a stored procedure

I created a stored procedure to generate a SHA1 password hash:

alter procedure GetPasswordSp (
   @pUsername nvarchar(50), 
   @pPasswordClear nvarchar(15), 
   @pPassword char(50) output
   )
 AS
   
if @pusername is null
   RETURN
   
set @pPassword = SUBSTRING(master.dbo.fn_varbintohexstr(HASHBYTES('SHA1', ltrim(rtrim(@pPasswordClear)))),3,50) 

RETURN

Open in new window


Its simple enough... when I build a test it runs fine and the value that is returned is what I expect... when I run it as a stored procedure I get a different return.

I assumed that there was an extra character begin padded in my code, so I changed it from  a hash to simply display the hex results so I could see the extra chars

I test manually again:  username=username, pw = Buckwheat

RETURN VALUE: 0x4275636b7768656174

Again, this is what I expected...

I copy/paste the same code into the SP 

master.dbo.fn_varbintohexstr(CAST(ltrim(rtrim('@pPasswordClear')) AS varbinary))

Open in new window


and pass the same values

RETURN VALUE: 0x4200750063006b0077006800650061007400          

NOTICE THAT IF YOU TAKE OUT THE zero-zeros THE VALUE IS THE SAME....  

Any suggestions as to how to fix?

The hash key (SHA1) is going to be generated in SQL and also on a web page to validate passwords, if they don't match, well, you can imagine the problems.
swsinriAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
Should you really use nvarchar as input here (@pPasswordClear NVARCHAR(15))? Comparing it with a varchar input should definitely result in something different. If the code is padded with 00, the varchar result is converted into Unicode (nvarchar). If the result is a completely different hash, Unicode input is taken.
Note that 'something' is a varchar; N'something' is nvarchar.
0
 
nemws1Database AdministratorCommented:
Well, this part has to be incorrect:

master.dbo.fn_varbintohexstr(CAST(ltrim(rtrim('@pPasswordClear')) AS varbinary))

Open in new window

                                 

As you are not encoding the *value* of @pPasswordClear, but rather the string "@pPasswordClear".  That can't be right.  (try setting @pPasswordClear to different values - and note how they all *encode to the same thing).

(Edit: changed 'encrypt' to 'encode')
0
 
nemws1Database AdministratorCommented:
I'm not able to reproduce your results.  My code:

DECLARE @pPasswordClear VARCHAR(100) = 'Buckwheat';
DECLARE @pPassword VARCHAR(1000);

SELECT master.dbo.fn_varbintohexstr(CAST(ltrim(rtrim('@pPasswordClear')) AS VARBINARY))
SELECT master.dbo.fn_varbintohexstr(CAST(ltrim(rtrim(@pPasswordClear)) AS VARBINARY))
EXEC GetPasswordSP 'username', @pPasswordClear, @pPassword OUT;

SELECT @pPassword;

Open in new window


My results:
0x407050617373776f7264436c656172

0x4275636b7768656174

3fccee01b0d8b57dd132dd2d6953d5cbc80146bf

Open in new window


The first one is just garbage - since its not "Buckwheat" but rather the string "@pPasswordClear" that is encoded.  The 2nd is same as what you had.  The 3rd is what I get from your stored procedure:

ALTER PROCEDURE GetPasswordSp (
	@pUsername NVARCHAR(50)
	, @pPasswordClear NVARCHAR(15)
	, @pPassword VARCHAR(50) OUTPUT
	)
AS
BEGIN
	IF @pusername IS NULL
		RETURN

	SET @pPassword = SUBSTRING(master.dbo.fn_varbintohexstr(HASHBYTES('SHA1', ltrim(rtrim(@pPasswordClear)))), 3, 50)
END
GO

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.