2

I want to send a lot of data from c# to my database, together with a calculated checksum as the last value, which should be compared to the one that the SQL Server's stored procedure will compute from those values.

To achieve this, I have tried a lot of different ways like the SQL built-in Checksum method and different hashings, but either the C# and SQL compute a different value, or the hashing doesn't work with nvarchars and integers (fx. MD5 hashing) which is required for me.

Did anyone actually manage to do this, or know how to?

Reference to our failed MD5 attempt: SQL Server HASHBYTES conversion inconsistency?

here, the computed result is different than the c# one when it uses nvarchars

Hashing method:

Set @result = convert(nvarchar(32), hashbytes('MD5', @DataID + @Data1 + @Data2 + @Data3), 2)

Also, when you give the hashing method an integer as parameter (like DataID), it complains:

"Argument data type int is invalid for argument 2 of hashbytes function."

Community
  • 1
  • 1
J. Doe
  • 55
  • 6
  • 1
    Most likely, if text is involved, there is a difference with collation and encoding. In any case, you must post some code. Post some SQL code and some C# code that both calculates the checksum of the same things and then post the generated checksums. Until you do, at most you're going to get "Well, that's just too bad". – Lasse V. Karlsen Dec 01 '16 at 13:52
  • 1
    HASHBYTES() supports MD5 and works with nvarchar and any other type when converted to varbinary, C# can do the same, what is the problem you have encountered? – Alex K. Dec 01 '16 at 13:52
  • added reference to my friends post with the SQL code. When using Nvarchars it doesnt compute the same as the c# hashing does, and it doesn't accept integers. We will check both threads for answers regularly. – J. Doe Dec 01 '16 at 14:17
  • So where you at now? – J. Doe Dec 01 '16 at 15:41
  • Have you considered *not* using a hash and just performing a lookup with `where x=@data1 and y=@data2 and z=@data3` ? This is usually the best approach. – Alex K. Dec 01 '16 at 16:56
  • You cannot just append elements and hash them and expect a reliable output given that *H('AA' + 'B')* is the same as *H('A' + 'AB')* and MD5 should likely be avoided. – Alex K. Dec 01 '16 at 16:57

1 Answers1

1

Comments considered, here is how you can do it (/ is used as a guard char, only one is needed as the int is always 4 bytes):

declare @DataID int = 1234
declare @Data1 nvarchar(max) = N'foo æøåè bar'
declare @Data2 nvarchar(max) = N'qux quee'

declare @buffer varbinary(max)
 = cast(@DataID as varbinary(4)) + cast(@Data1 + N'/' as varbinary(max)) + cast(@Data2 as varbinary(max))

select @buffer, select hashbytes('MD5', @buffer)

For

0x000004D266006F006F002000E600F800E500E80061D86BDF20006200610072002F0071007500780020007100750065006500  
0x9DA035DB9D9C319BB636D5E89F4D0EC6

C#

int    DataID = 1234;
string Data1  = "foo æøåè bar";
string Data2  = "qux quee";

List<byte> buffer = new List<byte>(BitConverter.GetBytes(DataID));
buffer.Reverse(); // swap endianness for int
buffer.AddRange(Encoding.Unicode.GetBytes(Data1 + "/"));
buffer.AddRange(Encoding.Unicode.GetBytes(Data2));

using (MD5 md5 = MD5.Create())
{
    byte[] hashBytes = md5.ComputeHash(buffer.ToArray());

    //...
}

For

000004D266006F006F002000E600F800E500E80061D86BDF20006200610072002F0071007500780020007100750065006500
9DA035DB9D9C319BB636D5E89F4D0EC6
Alex K.
  • 171,639
  • 30
  • 264
  • 288