SQL Query return different result for parameter vs hardcoded value passed to HashBytes Function

Question

I need to pass values through a prameter to a HashBytes Function. When I hardcode the values, and when I use a parameter, the results are different:

I've tried to play with VarChar vs nVarChar
I've tried to play with CHAR(13) and/or CHAR(10)
I've copied the value of FileContents to NotePad and WordPad, and it seems correct in both.

--Read the values from the database into a parameter
DECLARE @FileContents  NVARCHAR(MAX)
DECLARE @TableContents  table (TXT NVARCHAR(250))
insert into @TableContents (TXT) select TXT_0 from ZFICFIL
select * from @TableContents   -- Table contents

set @FileContents = ''
SELECT @FileContents = @FileContents + char(13) + TXT FROM @TableContents
-- Drop the first CR/LF
select @FileContents = right(@FileContents, len(@FileContents)-1)
select @FileContents

DECLARE @HashThis nvarchar(MAX);  
SET @HashThis = CONVERT(nvarchar(MAX),'ZAPAY190800101000001,07/02/2019 14:46:24 PM
2,300,');  
SELECT HASHBYTES('SHA1', @HashThis)
SELECT HASHBYTES('SHA1', @FileContents)

The table contents gives this:

ZAPAY190800101000001,07/02/2019 14:46:24 PM
2,300,

@FileContents gives this:

ZAPAY190800101000001,07/02/2019 14:46:24 PM
2,300,

This first HashBytes, give this result (hard-coded):

0xB1C23ED2F33F723D7C4FF23D4415B983F08E0C6C

This second HashBytes, give this result (parameter):

0x6A8C526D7BCD7BEA5789B53D526B8D5C77173EA2

If I don't read from the table , but 'hard-code' it as follows, then it works:

set @FileContents = 'ZAPAY190800101000001,07/02/2019 14:46:24 PM'
set @FileContents = @FileContents + CHAR(13) + CHAR(10) + '2,300,'

I've tried to put the values in a file and read from a file, but that didn't work either.

set @FileContents = 'SELECT convert(varchar(MAX), *) FROM OPENROWSET(BULK ''C:\Temp\NB00002.TXT'',SINGLE_CLOB) x'

What am I missing?

MYSQL does not have nvarchar or + as a concat shortcut - are you sure this is a mysql question? — P.Salmon, Aug 07 '19 at 08:24

score 0 · Answer 1 · answered Aug 07 '19 at 12:42

According to Microsoft documentation and the function HashBytes :

HASHBYTES ( '<algorithm>', { @input | 'input' } )  
<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512

The single quotes are mandatory for the algorithm type and SHA1 output is 160 bits / 20 bytes. The input can be varchar,nvarchar, or varbinary. The output is returned as varbinary(Max 8000). The max input is limited to 8000 bytes for SQL 2014(12.x) and earlier.

Example:

DECLARE @HashThis nvarchar(32);  
SET @HashThis = CONVERT(nvarchar(32),'dslfdkjLK85kldhnv$n000#knf');  
SELECT HASHBYTES('SHA2_256', @HashThis);

*SQL 2016 and up have deprecated SHS and SHA1. Consider using checksum or binary_Checksum; Also for SQL 2014 and earlier the max input is 8000 bytes.

SQL Query return different result for parameter vs hardcoded value passed to HashBytes Function

1 Answers1