TSQL md5 отличается от С#.NET md5

Я создал хеш md5, как показано ниже:

DECLARE @varchar varchar(400) 

SET @varchar = 'è'

SELECT CONVERT(VARCHAR(2000), HASHBYTES( 'MD5', @varchar ), 2)

Какие выходы:

785D512BE4316D578E6650613B45E934

Однако генерирование хэша MD5 с использованием:

System.Text.Encoding.UTF8.GetBytes("è")

генерирует:

0a35e149dbbb2d10d744bf675c7744b1

Кодировка в методе С#.NET установлена ​​в UTF8, и я предположил, что varchar также был UTF8, любые идеи о том, что я делаю неправильно?

Ответы

Ответ 1

Если вы имеете дело с NVARCHAR/NCHAR данными (которые хранятся как UTF-16 Little Endian), вы должны использовать Unicode кодировку, а не BigEndianUnicode. В .NET UTF-16 называется Unicode, в то время как другие кодировки Unicode относятся к их фактическим именам: UTF7, UTF8 и UTF32. Следовательно, Unicode само по себе является Little Endian в отличие от BigEndianUnicode. ОБНОВЛЕНИЕ: Пожалуйста, смотрите раздел в конце о UCS-2 и дополнительных символах.

На стороне базы данных:

SELECT HASHBYTES('MD5', N'è') AS [HashBytesNVARCHAR]
-- FAC02CD988801F0495D35611223782CF

На стороне .NET:

System.Text.Encoding.ASCII.GetBytes("è")
// D1457B72C3FB323A2671125AEF3EAB5D

System.Text.Encoding.UTF7.GetBytes("è")
// F63A0999FE759C5054613DDE20346193

System.Text.Encoding.UTF8.GetBytes("è")
// 0A35E149DBBB2D10D744BF675C7744B1

System.Text.Encoding.UTF32.GetBytes("è")
// 86D29922AC56CF022B639187828137F8

System.Text.Encoding.BigEndianUnicode.GetBytes("è")
// 407256AC97E4C5AEBCA825DEB3D2E89C

System.Text.Encoding.Unicode.GetBytes("è")  // this one matches HASHBYTES('MD5', N'è')
// FAC02CD988801F0495D35611223782CF

Однако этот вопрос относится к данным VARCHAR/CHAR, который является ASCII, и поэтому вещи немного сложнее.

На стороне базы данных:

SELECT HASHBYTES('MD5', 'è') AS [HashBytesVARCHAR]
-- 785D512BE4316D578E6650613B45E934

Мы уже видим сторону .NET выше. Из этих хешированных значений должны быть два вопроса:

  • Почему ни один из них не соответствует значению HASHBYTES?
  • Почему статья "sqlteam.com", связанная с ответом @Eric J., показывает, что три из них (ASCII, UTF7 и UTF8) соответствуют значению HASHBYTES?

Существует один ответ, который охватывает оба вопроса: Кодовые страницы. Тест, выполненный в статье "sqlteam", использовал "безопасные" символы ASCII, которые находятся в диапазоне 0 - 127 (в терминах значения int/decimal), которые не меняются между страницами кода. Но диапазон 128 - 255, где мы находим символ "è", - это расширенный набор, который изменяется по кодовой странице (что имеет смысл, поскольку это является причиной наличия кодовых страниц).

Теперь попробуйте:

SELECT HASHBYTES('MD5', 'è' COLLATE SQL_Latin1_General_CP1255_CI_AS) AS [HashBytes]
-- D1457B72C3FB323A2671125AEF3EAB5D

Это соответствует хешируемому значению ASCII (и, опять же, поскольку статья/тест "sqlteam" использовала значения в диапазоне 0 - 127, при использовании COLLATE) изменений не наблюдалось. Отлично, теперь мы наконец нашли способ сопоставления данных VARCHAR/CHAR. Все хорошо?

Ну, не совсем. Давайте посмотрим, что мы на самом деле хешируем:

SELECT 'è' AS [TheChar],
       ASCII('è') AS [TheASCIIvalue],
       'è' COLLATE SQL_Latin1_General_CP1255_CI_AS AS [CharCP1255],
       ASCII('è' COLLATE SQL_Latin1_General_CP1255_CI_AS) AS [TheASCIIvalueCP1255];

Возврат:

TheChar TheASCIIvalue   CharCP1255  TheASCIIvalueCP1255
è       232             ?           63

A ?? Просто чтобы проверить, запустите:

SELECT CHAR(63) AS [WhatIs63?];
-- ?

А, поэтому код Page 1255 не имеет символа è, поэтому он переводится как все любимые ?. Но тогда почему это соответствовало хешированию MD5 в .NET при использовании ASCII-кодирования? Может быть, мы фактически не соответствовали хешируемому значению è, а вместо этого соответствовали хешируемому значению ?:

SELECT HASHBYTES('MD5', '?') AS [HashBytesVARCHAR]
-- 0xD1457B72C3FB323A2671125AEF3EAB5D

Угу. Истинное ASCII набор символов - это только первые 128 символов (значения 0 - 127). И, как мы только что видели, è составляет 232. Таким образом, использование ASCII кодирования в .NET не так полезно. Также не использовалось COLLATE на стороне T-SQL.

Можно ли получить лучшую кодировку на стороне .NET? Да, используя Encoding.GetEncoding(Int32), который позволяет указать страницу кода. Страница кода для использования может быть обнаружена с использованием следующего запроса (используйте sys.columns при работе с столбцом вместо литерала или переменной):

SELECT sd.[collation_name],
       COLLATIONPROPERTY(sd.[collation_name], 'CodePage') AS [CodePage]
FROM   sys.databases sd
WHERE  sd.[name] = DB_NAME(); -- replace function with N'{db_name}' if not running in the DB

Запрос выше возвращает (для меня):

Latin1_General_100_CI_AS_SC    1252

Итак, попробуйте код Страница 1252:

System.Text.Encoding.GetEncoding(1252).GetBytes("è") // Matches HASHBYTES('MD5', 'è')
// 785D512BE4316D578E6650613B45E934

Уо! У нас есть соответствие для данных VARCHAR, которые используют нашу сортировку SQL Server по умолчанию:). Конечно, если данные поступают из базы данных или поля, заданного в другую сортировку, то GetEncoding(1252) может не работать, и вам нужно будет найти фактическую совпадающую страницу кода, используя запрос, показанный выше (кодовая страница используется во многих Сопоставления, поэтому другая сортировка не обязательно подразумевает другую страницу кода).

Чтобы узнать, каковы возможные значения кодовой страницы и к какой культуре/языку они относятся, см. список кодовых страниц здесь ( список находится в разделе "Примечания" ).

Дополнительная информация, связанная с тем, что на самом деле хранится в полях NVARCHAR/NCHAR:

Любой символ UTF-16 (2 или 4 байта) может быть сохранен, хотя поведение по умолчанию встроенных функций предполагает, что все символами являются UCS-2 (по 2 байта), который является подмножеством UTF-16. Начиная с SQL Server 2012, можно получить доступ к набору сопоставлений Windows, которые поддерживают 4 байтовых символа, известных как дополнительные символы. Использование одной из этих оконных коллайсов, заканчивающихся на _SC, заданных для столбца или непосредственно в запросе, позволит встроенным функциям правильно обрабатывать 4 байтовых символа.

-- The database collation is set to: SQL_Latin1_General_CP1_CI_AS
SELECT  N'𨝫' AS [SupplementaryCharacter],
        LEN(N'𨝫') AS [LEN],
        DATALENGTH(N'𨝫') AS [DATALENGTH],
        UNICODE(N'𨝫') AS [UNICODE],
        LEFT(N'𨝫', 1) AS [LEFT],
        HASHBYTES('MD5', N'𨝫') AS [HASHBYTES];

SELECT  N'𨝫' AS [SupplementaryCharacter],
        LEN(N'𨝫' COLLATE Latin1_General_100_CI_AS_SC) AS [LEN],
        DATALENGTH(N'𨝫' COLLATE Latin1_General_100_CI_AS_SC) AS [DATALENGTH],
        UNICODE(N'𨝫' COLLATE Latin1_General_100_CI_AS_SC) AS [UNICODE],
        LEFT(N'𨝫' COLLATE Latin1_General_100_CI_AS_SC, 1) AS [LEFT],
        HASHBYTES('MD5', N'𨝫' COLLATE Latin1_General_100_CI_AS_SC) AS [HASHBYTES];

Возврат:

SupplementaryChar   LEN   DATALENGTH   UNICODE   LEFT   HASHBYTES
𨝫                  2     4             55393    �     0x7A04F43DA81E3150F539C6B99F4B8FA9
𨝫                  1     4            165739    𨝫     0x7A04F43DA81E3150F539C6B99F4B8FA9

Как вы можете видеть, не влияют ни DATALENGTH, ни HASHBYTES. Для получения дополнительной информации см. Страницу MSDN для Поддержка сортировки и юникода (в частности раздел "Дополнительные символы" ).

Ответ 2

SQL Server использует UCS-2 вместо UTF-8 для кодирования символьных данных.

Если вы используете поле NVarChar, будет работать следующее:

System.Text.Encoding.Unicode.GetBytes("è"); // Updated per @srutzky comments

Дополнительные сведения о хешировании SQL и С# см. в

http://weblogs.sqlteam.com/mladenp/archive/2009/04/28/Comparing-SQL-Server-HASHBYTES-function-and-.Net-hashing.aspx

Ответ 3

У меня была одна и та же проблема, и, как и комментарии @srutzky, может произойти то, что я не выполнил запрос с помощью capital-N, и я получал 8-разрядный расширенный ASCII (VARCHAR/string not с префиксом capital-N) вместо 16-разрядного UTF-16 Little Endian (NVARCHAR/строка с префиксом capital-N)

{Id, UserName, PasswordString, PasswordHashed}

Если вы выполните:

SELECT TOP 1 CONVERT(char(32),HashBytes('MD5', 'abc123'),2) FROM [Users]

Он выведет: E99A18C428CB38D5F260853678922E03

Но если вы сделаете это, имея тот же пароль ('abc123'):

SELECT CONVERT(char(32),HashBytes('MD5', [PasswordString]),2) FROM [Users]

Он выведет: 6E9B3A7620AAF77F362775150977EEB8

Что я должен был сделать:

SELECT CONVERT(char(32),HashBytes('MD5', N'abc123'),2) FROM [Users]

Это выводит тот же результат: 6E9B3A7620AAF77F362775150977EEB8

Ответ 4

sql server hashbytes всегда работает как System.Text.Encoding.Unicode на символы юникода, такие как арабский персидский,... если вы используете Utf8.Unicode или Ascii.Unicode Вы увидите разницу и если вы используете Utf8.Unicode, результат возврата sql-сервера и С# будет таким же