1

The following SQL

SELECT CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', 'test'), 2) as 'From String'

produces the following hash:

EE26B0DD4AF7E749AA1A8EE3C10AE9923F618980772E473F8819A5D4940E0DB27AC185F8A0E1D5F84F88BC887FD67B143732C304CC5FA9AD8E6F57F50028A8FF

If I then replace 'test' with the column value from [Key] instead like this:

SELECT CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', [Key]), 2) as 'From Column' FROM MyTable
WHERE [Key] = 'test'

The following hash is produced:

9F7D8627E02F97CC5A52DCB2BA96038FE12F2A34B0FAC50E041359AE13D5EDE8A8A50562DA58BA7916DA378E7343EF91E85EFBD6A0A70AB237ADA4C2274DF13D

Right now we have a couple of rows in our database that I would like to hash, so I want to run the following code:

UPDATE MyTable SET [Key] = CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', [Key]), 2)

But the problem is that the hash produced is not correct, it's only correct if I replace [Key] with the actual string value.

I found this question: Using HASHBYTES() yields different results for nvarchar and a variable and there they "solve it" by prefixing the string with N. But I want to do it the other way around, I want the output to be EE26B0DD4AF7E749AA1A8EE3C10AE9923F618980772E473F8819A5D4940E0DB27AC185F8A0E1D5F84F88BC887FD67B143732C304CC5FA9AD8E6F57F50028A8FF.

How can I achieve that?

My table looks like this:

CREATE TABLE [dbo].[MyTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Key] [nvarchar](128) NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
5
  • Turns out I can solve it by doing this (cast to varchar) SELECT CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', CAST([Key] as varchar(128))) ,2) as 'From Column' FROM MyTable WHERE [Key] = 'test' Is that a good solution though?
    – JOSEFtw
    Commented Mar 10, 2020 at 11:54
  • Answer your question and accept it, could help others in similar situation. Commented Mar 10, 2020 at 12:21
  • Yeah I will, just want to know before if there are any drawbacks, casting back and forth like that...
    – JOSEFtw
    Commented Mar 10, 2020 at 12:29
  • But I want to do it the other way around, I want the output to be--why? HASHBYTES will have a different result for VARCHAR compare to NVARCHAR. Your table is NVARCHAR and you want the result of VARCHAR. Does not make sense. Commented Mar 10, 2020 at 12:30
  • I hear you. Thing is, this should not have been a NVARCHAR from the beginning. But what we are doing now is generating the SHA512 hash in our C# code, and then we want to compare that with what we have in the database. So by having nvarchar, the hashes will not match. We will convert the column to varchar.
    – JOSEFtw
    Commented Mar 10, 2020 at 12:35

2 Answers 2

3

The reason your output is different between a literal string (i.e. writing 'test') or using a column value. In your case [key].

Is because [Key] column is a NVARCHAR column. Whereas 'test' is a VARCHAR string. Typing N'Test' makes it a NVARCHAR string.

If you want it to have the same behaviour, you would need to make your Key column a varchar, then you also don't need to convert it to NVARCHAR again.

CREATE TABLE [dbo].[MyTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Key] [varchar](128) NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Or convert it as such before it's hashed.

UPDATE MyTable SET [Key] = CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', CONVERT(VARCHAR(128), [Key])), 2)

At this point though you should really wonder why you're flip flopping between the chartypes. And once again converting the result.

1
  • Yeah we will just convert the columt to varchar, thanks! :)
    – JOSEFtw
    Commented Mar 10, 2020 at 12:37
1

MSSQL Hashbytes produces different output, string vs column value

I do not agree that MSSQL produces a different result for string vs column unless you are using different data types.

DROP TABLE IF EXISTS MyTable;
GO
CREATE TABLE [dbo].[MyTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Key] [varchar](128) NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
INSERT INTO MyTable VALUES( 'test');

The following code will produce the same result.

SELECT HASHBYTES ('SHA2_512', 'test');
GO
SELECT  HASHBYTES ('SHA2_512', [Key]) FROM MyTable
WHERE [Key] = 'test';
GO

You can reproduce the changing both string to NVARCHAR type.

As you mentioned in your comment, the solution is to change datatype of your key column.

Another piece of test code to prove that HASHBYTES produce a consistent result.

DECLARE @hashVarchar varchar(128) ='test';
DECLARE @hashNvarchar nvarchar(128) ='test';

SELECT HASHBYTES ('SHA2_512',@hashVarchar);
SELECT HASHBYTES ('SHA2_512',@hashNvarchar);
1
  • Thank you, I've now changed the column to varchar instead of nvarchar! :)
    – JOSEFtw
    Commented Mar 10, 2020 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.