Solved

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

Posted on 2013-05-10
3
136 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 69

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

685 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