Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-05-10
3
Medium Priority
?
164 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 71

Accepted Solution

by:
Qlemo earned 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

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
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

721 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