1

I've seen a lot of examples matching the keywords. However those are often for other languages and/or the conversion on values from select, which I haven't been able to adapt to my case.

I'm converting a hashed string in C# to Base64 (previously it was stored as HEX). As the fiddle shows, the HEX'ed result is precisely what the following SQL produces.

declare @Pass as varchar(max) = 'abc'
declare @HexPass as varchar(max) = convert(varchar(max), hashbytes('SHA2_512', @Pass), 2)
select @Pass as Plain, @HexPass as HEX

However, when replacing the old Convert.ToHexString(hash) by the new Convert.ToBase64String(hash) changes the output and I haven't figured out a way to introduce a corresponding conversion in SQL.

string text = "abc";
string expectedResult = "3a81oZNherrMQXNJriBBMRLm+k6JqX6iCp7u5ktV05ohkpkqJ0/BqDa6PCOj/uu9RU1EI2Q86A4qmslPpUyknw==";

byte[] data = Encoding.UTF8.GetBytes(text);
byte[] hash = SHA512.Create().ComputeHash(data);
string actualResult = Convert.ToBase64String(hash);
Console.WriteLine(expectedResult == actualResult ? "equal": "not equal");

Researching it, I stumbled in several instances on a solution relying on xs:base64Binary (like here, here, definitely here etc.).

However, I fail to figure out the syntax of how to follow that applying @HexPass above. I've tried a "gazillion" things, concatenating strings in that schema expression, replacing parts of it etc.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Konrad Viltersten
  • 36,151
  • 76
  • 250
  • 438
  • The output of `hashbytes()` is a `varbinary` value. You need to base64 encode that `varbinary` value if you want the correct result. By converting the `varbinary` value to a hex string and then (presumably) trying to base64 encode that you will get an incorrect result. – AlwaysLearning Aug 06 '23 at 08:42
  • Side note: you can do `SHA512.HashData(data);` directly – Charlieface Aug 06 '23 at 13:32

1 Answers1

3

Your C# code is using Encoding.UTF8.GetBytes.

You can use

declare @Pass as nvarchar(max) = N'abc' 

select 
cast('' as xml).value(
    'xs:base64Binary(sql:column("v.col"))', 'varchar(max)'
) as Base64Encoding
from (values(hashbytes('SHA2_512', CAST(@Pass COLLATE Latin1_General_100_CI_AI_SC_UTF8  AS VARCHAR(MAX))))) v(col)

This returns

3a81oZNherrMQXNJriBBMRLm+k6JqX6iCp7u5ktV05ohkpkqJ0/BqDa6PCOj/uu9RU1EI2Q86A4qmslPpUyknw==

The Latin1_General_100_CI_AI_SC_UTF8 doesn't make any difference for abc but can do for other strings

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Just to confirm - this is the simplest way to do it? I'm asking so because in my (probably ignorant) mind, such a basic conversion would have a single statement achieving that (like in C#), while it seems that in SQL, convoluted and quite clunky. Am I blissfully unaware of a complexity behind the conversion? (I see that it e.g. relies on correct collation, which may differ, to begin with...) – Konrad Viltersten Aug 06 '23 at 17:36
  • @KonradViltersten - the only way of doing it in TSQL is through XML methods. There is no other function for it. The dupe does have some other syntax that is maybe a bit simpler `SELECT hashbytes('SHA2_512', CAST(@Pass COLLATE Latin1_General_100_CI_AI_SC_UTF8 AS VARCHAR(MAX))) FOR XML PATH(''), BINARY BASE64` – Martin Smith Aug 06 '23 at 19:23
  • And the collation isn't anything to do with the base64 part, this is to get the correct value to base 64 encode in the first place. `hashbytes` depends on the binary representation of the string. This depends on the text encoding used. In .NET you have selected `Encoding.UTF8` to do the conversion so you need to use a collation/datatype combination that will give you the same encoding in SQL – Martin Smith Aug 07 '23 at 07:06