Solved

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

Posted on 2013-05-10
3
127 Views
Last Modified: 2016-03-16
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.
0
Comment
Question by:swsinri
  • 2
3 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 39156729
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
 
LVL 23

Expert Comment

by:nemws1
ID: 39156755
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
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39158028
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now