Sql-server – MSSQL Hashbytes produces different output, string vs column value

hashingsql server

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]

Best Answer

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.

Related Question