.net sql-server tsql unicode encoding

TSQL md5 hash diferente a C#.NET md5



sql-server unicode (4)

Estaba teniendo el mismo problema, y ​​como comenta @srutzky, lo que podría estar sucediendo es que no precedí la consulta con una N mayúscula, y estaba obteniendo un ASCII extendido de 8 bits (VARCHAR / cadena no prefijada con mayúscula -N) en lugar de un Little Endian UTF-16 de 16 bits (NVARCHAR / cadena con el prefijo N mayúscula)

{Id, UserName, PasswordString, PasswordHashed}

Si lo haces:

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

Saldrá: E99A18C428CB38D5F260853678922E03

Pero si hace esto, tiene la misma contraseña (''abc123''):

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

Saldrá: 6E9B3A7620AAF77F362775150977EEB8

Lo que debería haber hecho es:

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

Eso genera el mismo resultado: 6E9B3A7620AAF77F362775150977EEB8

He generado un hash md5 de la siguiente manera:

DECLARE @varchar varchar(400) SET @varchar = ''è'' SELECT CONVERT(VARCHAR(2000), HASHBYTES( ''MD5'', @varchar ), 2)

Qué salidas:

785D512BE4316D578E6650613B45E934

Sin embargo, generar un hash MD5 usando:

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

genera:

0a35e149dbbb2d10d744bf675c7744b1

La codificación en el método C # .NET está establecida en UTF8 y supuse que varchar también era UTF8, ¿alguna idea de lo que estoy haciendo mal?

Si está tratando con datos NVARCHAR / NCHAR (que se almacenan como UTF-16 Little Endian ), entonces usaría la codificación Unicode , no BigEndianUnicode . En .NET, UTF-16 se llama Unicode mientras que otras codificaciones Unicode se denominan por sus nombres reales: UTF7, UTF8 y UTF32. Por lo tanto, Unicode en sí mismo es Little Endian en comparación con BigEndianUnicode . ACTUALIZACIÓN: Consulte la sección al final sobre UCS-2 y caracteres suplementarios.

En el lado de la base de datos:

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

En el lado .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

Sin embargo, esta pregunta se refiere a los datos VARCHAR / CHAR , que es ASCII, por lo que las cosas son un poco más complicadas.

En el lado de la base de datos:

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

Ya vemos el lado .NET arriba. De esos valores hash debería haber dos preguntas:

  • ¿Por qué ninguno de ellos coincide con el valor HASHBYTES ?
  • ¿Por qué el artículo "sqlteam.com" vinculado en la respuesta de @Eric J. muestra que tres de ellos ( ASCII , UTF7 y UTF8 ) coinciden con el valor HASHBYTES ?

Hay una respuesta que cubre ambas preguntas: Páginas de códigos. La prueba realizada en el artículo "sqlteam" utilizó caracteres ASCII "seguros" que están en el rango 0 - 127 (en términos del valor int / decimal) que no varían entre las páginas de códigos. Pero el rango 128-255, donde encontramos el carácter "è", es el conjunto extendido que varía según la página de códigos (lo cual tiene sentido ya que esta es la razón para tener páginas de códigos).

Ahora intenta:

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

Eso coincide con el valor hash ASCII (y nuevamente, debido a que el artículo / prueba "sqlteam" usó valores en el rango 0 - 127, no vieron ningún cambio al usar COLLATE ). Genial, ahora finalmente encontramos una manera de hacer coincidir los datos VARCHAR / CHAR . ¿Todo bien?

Bueno en realidad no. Echemos un vistazo a lo que realmente estábamos haciendo hash:

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];

Devoluciones:

TheChar TheASCIIvalue CharCP1255 TheASCIIvalueCP1255 è 232 ? 63

A ? ? Solo para verificar, ejecute:

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

Ah, entonces el código de la página 1255 no tiene el carácter è , ¿se traduce como el favorito de todos ? . Pero entonces, ¿por qué coincide con el valor hash MD5 en .NET cuando se usa la codificación ASCII? ¿Podría ser que en realidad no estábamos igualando el valor hash de è , sino que estábamos igualando el valor hash de ? :

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

Sip. El verdadero ASCII caracteres ASCII son solo los primeros 128 caracteres (valores 0 - 127). Y como acabamos de ver, el è es 232. Entonces, usar la codificación ASCII en .NET no es tan útil. Tampoco estaba usando COLLATE en el lado T-SQL.

¿Es posible obtener una mejor codificación en el lado .NET? Sí, utilizando Encoding.GetEncoding(Int32) , que permite especificar la Página de códigos. La página de códigos a usar se puede descubrir usando la siguiente consulta (use sys.columns cuando trabaje con una columna en lugar de un literal o variable):

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

La consulta anterior devuelve (para mí):

Latin1_General_100_CI_AS_SC 1252

Entonces, intentemos la página de códigos 1252:

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

Woo hoo! Tenemos una coincidencia para los datos de VARCHAR que utilizan nuestra clasificación predeterminada de SQL Server :). Por supuesto, si los datos provienen de una base de datos o un conjunto de campos con una intercalación diferente, entonces GetEncoding(1252) podría no funcionar y tendrá que encontrar la página de códigos coincidente real utilizando la consulta que se muestra arriba (se usa una página de códigos en muchas intercalaciones, por lo que una intercalación diferente no implica necesariamente una página de códigos diferente).

Para ver cuáles son los valores posibles de la página de códigos y a qué cultura / localidad pertenecen, consulte la lista de páginas de códigos here (la lista se encuentra en la sección "Comentarios").

Información adicional relacionada con lo que realmente se almacena en los campos NVARCHAR / NCHAR :

Se puede almacenar cualquier carácter UTF-16 (2 o 4 bytes), aunque el comportamiento predeterminado de las funciones integradas supone que todos los caracteres son UCS-2 (2 bytes cada uno), que es un subconjunto de UTF-16. A partir de SQL Server 2012, es posible acceder a un conjunto de intercalaciones de Windows que admiten los caracteres de 4 bytes conocidos como caracteres complementarios. El uso de una de estas intercalaciones de Windows que terminan en _SC , ya sea especificada para una columna o directamente en una consulta, permitirá que las funciones integradas manejen adecuadamente los caracteres de 4 bytes.

-- The database''s 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];

Devoluciones:

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

Como puede ver, ni DATALENGTH ni HASHBYTES se ven afectados. Para obtener más información, consulte la página de MSDN sobre Compatibilidad y compatibilidad con Unicode (específicamente la sección "Caracteres complementarios").

los hashbytes del servidor sql siempre funcionan como System.Text.Encoding.Unicode en caracteres unicode como el árabe persa, ... si usa Utf8.Unicode o Ascii.Unicode Verá la diferencia y si usa Utf8.Unicode el resultado de retorno de sql servidor y c # serán iguales