SQL bigint hash to match c# int64 hash

Question

I am trying to create a universal hashing alogrithim that hashes a string as a 64 bit int.

I am able to hash the strings correctly: sql:

select  
    convert
    (
        varchar(64),
        HASHBYTES
        (
            'SHA1',
            'google.com'
        ),
        2
    )

returns BAEA954B95731C68AE6E45BD1E252EB4560CDC45

C#

    System.Security.Cryptography.SHA1 c = System.Security.Cryptography.SHA1.Create();
    System.Text.StringBuilder sb = new StringBuilder();
    byte[] b = c.ComputeHash(Encoding.UTF8.GetBytes("google.com"));
    for (int i = 0; i < b.Length;i++ )
    {
        byte by = b[i];
        sb.Append(by.ToString("x2").ToUpper());
    }

    return sb.ToString();

retruns BAEA954B95731C68AE6E45BD1E252EB4560CDC45

However when I convert to a bigint/long the values do not match: sql:

select  
    convert
    (
        bigint,
        HASHBYTES
        (
            'SHA1',
            'google.com'
        )
    )

returns 2172193747348806725

c#:

    System.Security.Cryptography.SHA1 c = System.Security.Cryptography.SHA1.Create();
    byte[] b = c.ComputeHash(Encoding.UTF8.GetBytes("google.com"));
    return BitConverter.ToInt64(b, 0);

returns 7501998164347841210

Any ideas on how to get these numbers to match?

See here: http://stackoverflow.com/questions/8467072/sql-server-varbinary-bigint-with-bitconverter-toint64-values-are-different for a possible solution. — Matthew Watson, Mar 22 '13 at 21:27
Instead of generating your own hashes on objects you should just use [`GetHashCode`](http://msdn.microsoft.com/en-us/library/system.object.gethashcode.aspx), its much more effective than reinventing the wheel, strings with the same characters will produce the same `"HashCode"`. — classicjonesynz, Mar 22 '13 at 21:37
@Killrawr: GetHashCode should only be used to balance a hash table. We have no evidence that the original poster is attempting to balance a hash table; it looks rather like they are attempting a crypto strength hash. It is very, very important to **never use GetHashCode** for a crypto hash. It has *none* of the properties that you need to make a secure hash. Again, if you are calling GetHashCode and you are not right now trying to balance a hash table, you're doing something wrong. — Eric Lippert, Mar 22 '13 at 21:46
@EricLippert oh I thought I'd recommend it, if the hashes are being used for anything else such as with the `Equals` Method in order to create a efficient `Contract` between objects. — classicjonesynz, Mar 22 '13 at 21:56

DasKrümelmonster · Accepted Answer · 2013-03-22T21:47:53.010

8

Your SQL bigint takes the last 8 bytes while the c# implementation takes the first 8 bytes (and reverses them because its running on little endian).

Take the proper Range of the array in C# and reverse it. Then you should be fine.

Did some coding:

System.Security.Cryptography.SHA1 c = System.Security.Cryptography.SHA1.Create();
byte[] b = c.ComputeHash(Encoding.UTF8.GetBytes("google.com"));
long value = BitConverter.ToInt64(b, 12);
value = IPAddress.HostToNetworkOrder(value);

Debug.WriteLine(value);
// writes 2172193747348806725

edited Mar 22 '13 at 21:47

answered Mar 22 '13 at 21:25

DasKrümelmonster

5,816
1
24
45

And you can use `var reversed = BitConverter.GetBytes(IPAddress.HostToNetworkOrder(longValue))` to swap the bytes. – Matthew Watson Mar 22 '13 at 21:31
1

@DasKrumelmonster: If you use `BitConverter.GetBytes(IPAddress.HostToNetworkOrder(longValue))` rather than Linq, it will work regardless of the client's byte order because HostToNetworkOrder() accounts for it. – Matthew Watson Mar 22 '13 at 21:41
Good idea. It operates on a long value though, the endian conversion comes after the bitconverter. Also, I could eliminate linq by utilizing the startIndex parameter. – DasKrümelmonster Mar 22 '13 at 21:50
if you would rather make sql match `select convert ( bigint, convert ( varbinary(8), reverse ( convert ( varbinary(8), HASHBYTES ( 'SHA1', 'google.com' ) ) ) ) )` returns 7501998164347841210 – Eulalie367 Mar 23 '13 at 06:07
static long ComputeSha256HashbigInt(string rawData) { // Create a SHA256 using (SHA256 sha256Hash = SHA256.Create()) { // ComputeHash - returns byte array byte[] bytes = sha256Hash.ComputeHash(Encoding.UTF8.GetBytes(rawData)); if (BitConverter.IsLittleEndian) Array.Reverse(bytes); long value = BitConverter.ToInt64(bytes, 0); return value; } } – Vikrant Aug 12 '21 at 05:33

SQL bigint hash to match c# int64 hash

1 Answers1

Linked