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.
Solved
fn_varbintohexstr seems to return different values when used in a stored procedure
Posted on 2013-05-10
I created a stored procedure to generate a SHA1 password hash:
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
and pass the same values
RETURN VALUE: 0x4200750063006b0077006800
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.
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
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))
and pass the same values
RETURN VALUE: 0x4200750063006b0077006800
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.
[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
Well, this part has to be incorrect:
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')
master.dbo.fn_varbintohexstr(CAST(ltrim(rtrim('@pPasswordClear')) AS varbinary))
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')
I'm not able to reproduce your results. My code:
My results:
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:
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;
My results:
0x407050617373776f7264436c656172
0x4275636b7768656174
3fccee01b0d8b57dd132dd2d6953d5cbc80146bf
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
Active today
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.
Note that 'something' is a varchar; N'something' is nvarchar.
Featured Post
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.
Suggested Courses
Course of the Month9 days, 5 hours left to enroll
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.