Microsoft SQL Server
Microsoft SQL Server

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: 2013-05-10

Who is Participating?

LVL 73

QlemoBatchelor, Developer and EE Topic AdvisorCommented: 2013-05-11

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.

LVL 23

nemws1Database AdministratorCommented: 2013-05-10

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')

LVL 23

nemws1Database AdministratorCommented: 2013-05-10

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