Convert Hashbytes to Varchar

Convert HashBytes to VarChar

I have found the solution else where:

SELECT SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', 'HelloWorld')), 3, 32)

How to convert from Hashbytes function's SHA2_256 encryption datatype to varchar(256) in SQL Server 2012

I think this is what you are looking for...

select convert(varchar(256),HASHBYTES('SHA2_256','asd123123'),2)

See the Binary Styles Section (since Hashbytes returns varbinary)

Thus....

update members set
    passwd = convert(varchar(256),HASHBYTES('SHA2_256','asd123123'),2),
    pwchangedat = GETDATE()
    where userid = 'abcd1234'

HASHBYTES in SQL

The 2 sets the style ... Take a peek at https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

Just for fun, you can compare the results

Select With1 = CONVERT(VARCHAR(32), HashBytes('MD5', 'MyPassword'), 1)  -- with 0X
      ,With2 = CONVERT(VARCHAR(32), HashBytes('MD5', 'MyPassword'), 2)  -- without 0x
      ,SansN = CONVERT(VARCHAR(32), HashBytes('MD5', 'MyPassword'))     -- binary (default)

Results

With1                               With2                               SansN
0x48503DFD58720BD5FF35C102065A52    48503DFD58720BD5FF35C102065A52D7    HP=ýXrÕÿ5ÁZR×

Note: since varchar(32), With1 is truncated (missing D7)

hashbytes equivalent in mysql when working with none english text

Based on MySQL returning D8B3D984D8A7D985 from SELECT HEX(CAST('سلام' as binary)) it appears that MySQL is using UTF-8 character encoding.

On SQL Server 2019 you can use the LATIN1_GENERAL_100_CI_AS_SC_UTF8 collation (it's not supported on SQL Server 2017) such as the following:

create table #Test (
  UTF16 nvarchar(max),
  UTF8 varchar(max)  COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8
)
insert #Test values (N'سلام', N'سلام');

select UTF16 from #Test;
select CAST(UTF16 as varbinary) as [UTF16-Bytes] from #Test;
select UTF8 from #Test;
select CAST(UTF8 as varbinary) as [UTF8-Bytes] from #Test;

Which returns:

UTF16
سلام

UTF16-Bytes
0x3306440627064506

UTF8
سلام

UTF8-Bytes
0xD8B3D984D8A7D985

And then with hashbytes():

select hashbytes('MD5', cast(UTF16 as varbinary)) as [UTF16-Hash] from #Test;
select hashbytes('MD5', cast(UTF8 as varbinary)) as [UTF8-Hash] from #Test;

Which returns:

UTF16-Hash
0x0381CA5081FBC68B2F55F2F2C21399D7

UTF8-Hash
0x78903C575B0DDA53C4A7644A2DD36D0E

Hope this helps!

Almost the same Hash value

This should do it:

DECLARE @HashThis varchar(32);  
SET @HashThis = CONVERT(varchar(32),'Hello World!');  
SELECT CONVERT(char(64), HASHBYTES('SHA2_256', @HashThis), 2)

But if you send down a binary to the database you should be able to compare that to the result of HASHBYTES without any convert. The same if you bring it up to you C# app as byte[].

SQL Server HASHBYTES conversion inconsistency?

My team member asked a similar question and accepted the answer that solved it.
Comparing a C# generated Checksum with a SQL Server one

Declare variable for HashBytes

The issue is that you are using nvarchar to declare your secret. but it should be varchar and it would solve the problem.

So lets test it:

DECLARE @HashThis varchar(32);  
SET @HashThis = CONVERT(varchar(32),'secret');
DECLARE @HASHBYTES VARBINARY(128) = hashbytes('sha2_256', @HashThis)
SELECT cast(N'' as xml).value('xs:base64Binary(sql:variable("@HASHBYTES"))', 'varchar(128)');

Will return what you originally expected:

K7gNU3sdo+OL0wNhqoVWhr3g6s1xYv72ol/pe/Unols=

Btw, you do not need to CONVERT, you can just pass secret as varchar. some thing like:

DECLARE @HashThis varchar(32);
SET @HashThis = 'secret';

---

Related Topics