|
We need to create SHA1 hashes that match the Microsoft (and probably the standard?) implementation. E.g I do this in MS SQL Server 2008: begin declare @myPwd nvarchar(128); declare @mySalt nvarchar(128); declare @hashThis nvarchar(128); declare @hashedPwd binary(20); set @myPwd = 'secret'; set @mySalt = 'random123'; set @hashThis = @myPwd+@mySalt; set @hashedPwd = HashBytes('SHA1', @hashThis); select Convert(nvarchar(128), @hashedPwd, 2); -- 7D0DF1998892DB320D334EB1F0AB090E6422DE34 end The same in C# produces the same result: class Program { static void Main(string[] args) { // our password + salt string hashThis = "secretrandom123"; // make it a byte array and hash byte[] data = System.Text.Encoding.Unicode.GetBytes(hashThis); SHA1 sha = new SHA1CryptoServiceProvider(); byte[] result = sha.ComputeHash(data); // convert hashed byte array into a hex string so we can display it string hashed = BitConverter.ToString(result).Replace("-", String.Empty); Console.WriteLine(hashed); // 7D0DF1998892DB320D334EB1F0AB090E6422DE34 } } Here comes SQL Anywhere 16: begin declare @myPwd nvarchar(128); declare @mySalt nvarchar(128); declare @hashThis nvarchar(128); declare @hashedPwd nvarchar(128); set @myPwd = 'secrect'; set @mySalt = 'random123'; set @hashThis = 'secretrandom123'; set @hashedPwd = Hash(@myPwd+@mySalt, 'SHA1'); select @hashedPwd // ce0c0bd5d60846c2bfb18478fceda1296f783edb end The hash code produced by SQL Anywhere is different. I'm sure I'm missing something here. Always thought that SQL Anywhere Hash() properly converts the input string to a byte array in the background. Anyway, whatever I try, I can't make Hash() to return the same hashcode as SQL Server HashBytes or the .NET SHA1CryptoServiceProvider(). Does anyone see my mistake? TIA, Michael |
|
The reason for this is the subtle differences in what you are hashing. The .NET System.Text.Encoding.Unicode.GetBytes call returns the characters of a string as a UTF-16 byte stream. When using the UTF-16 encoding each character is represented by 16-bits or two bytes. Because of this, the byte array 'data' in the C# program actually contains 30 bytes. It's also worth noting that the order of these bytes can very depending on the implementation (in .NET it's always little-endian) or the presence of a BOM character. UTF-16 is a Unicode encoding that is not backwards compatible with ASCII (which is traditionally 7 bits, but usually represented by one byte per character). UTF-8 on the other hand, is a variable width encoding that is completely backwards compatible with ASCII. That is to say, that the UTF-8 encoding of any traditional ASCII character is identical to it's original ASCII encoding. UTF-8, like UTF-16 is an encoding that can represent any Unicode character. In SQL Anywhere, the NVARCHAR data type contains characters encoded using UTF-8 (so normal ASCII characters are one byte in size). In the C# program, you can change the Unicode.GetBytes() call to UTF8.GetBytes() or ASCII.GetBytes() to get the results you are expecting. The fact that Unicode = UTF-16 in this case is arbitrary, UTF-8 is also a Unicode encoding. Your SQL Server code uses the NVARCHAR data type to store the characters. Unlike SQL Anywhere, SQL Server stores NVARCHAR data as UTF-16 and does not support UTF-8. If you store the string using the VARCHAR data type, it will be encoded in a fixed length representation determined by the database encoding. The default SQL Server character encoding is ISO 8859-1 (sometimes called Latin-1). This is an ASCII-compltible 8-bit encoding that adds an extra 128 characters to take advantage of the extra bit vs traditional 7-bit ASCII. If the characters you are hashing all exist in the ASCII character set, you should change the data type in SQL Sever to VARCHAR to resolve this issue. 1
"you should change the data type to VARCHAR" ...wise words with wide worthiness :)
(01 Dec '13, 15:29)
Breck Carter
|
|
If I fix the spelling error that Volker mentioned and then run your code, I get the value d3c1becf5f8a9c37de3b5826a3392fd61cc2e2ae. If I try any of several SHA-1 online calculators (here, here, here, or here), I get exactly the same result. I can't speak to why your other examples are not working but as far as I can tell, the SQL Anywhere implementation is working fine. So Microsoft is calculating different?
(27 Nov '13, 02:48)
Martin
|
Is that a spelling mistake that might explain the difference?
FWIW, in case you are doing new development and do need the hash for security purposes, the general recommendation would be to use SHA-2 instead of SHA-1 - cf. this summary.
For SQL Anywhere, you can use that with HASH(..., 'SHA256') or the according FIPS-based version.