TSQL md5 hash different to C# .NET md5

Question

I've generated an md5 hash as below: ``` DECLARE @varchar varchar(400) SET @varchar = 'è' SELECT CONVERT(VARCHAR(2000), HASHBYTES( 'MD5', @varchar ), 2) ``` Which outputs: ``` 785D512BE4316D578E6650613B45E934 ``` However generating an MD5 hash using: ``` System.Text.Encoding.UTF8.GetBytes("è") ``` generates: ``` 0a35e149dbbb2d10d744bf675c7744b1 ``` The encoding in the C# .NET method is set to UTF8 and I had assumed that varchar was also UTF8, any ideas on what I'm doing wrong?

Accepted Answer

If you are dealing with `NVARCHAR` / `NCHAR` data (which is stored as UTF-16 Little Endian), then you would use the `Unicode` encoding, not `BigEndianUnicode`. In .NET, UTF-16 is called `Unicode` while other Unicode encodings are referred to by their actual names: UTF7, UTF8, and UTF32. Hence, `Unicode` by itself is `Little Endian` as opposed to `BigEndianUnicode`. UPDATE: Please see the section at the end regarding UCS-2 and Supplementary Characters.

On the database side:

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

On the .NET side:

```
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
```

However, this question pertains to `VARCHAR` / `CHAR` data, which is ASCII, and so things are a bit more complicated.

On the database side:

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

We already see the .NET side above. From those hashed values there should be two questions:

- Why don't any of them match the `HASHBYTES` value?

- Why does the "sqlteam.com" article linked in @Eric J.'s answer show that three of them (`ASCII`, `UTF7`, and `UTF8`) all match the `HASHBYTES` value?

There is one answer that covers both questions: Code Pages. The test done in the "sqlteam" article used "safe" ASCII characters that are in the 0 - 127 range (in terms of the int / decimal value) that do not vary between Code Pages. But the 128 - 255 range -- where we find the "è" character -- is the Extended set that does vary by Code Page (which makes sense as this is the reason for having Code Pages).

Now try:

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

That matches the `ASCII` hashed value (and again, because the "sqlteam" article / test used values in the 0 - 127 range, they did not see any changes when using `COLLATE`). Great, now we finally found a way to match `VARCHAR` / `CHAR` data. All good?

Well, not really. Let's take a look-see at what we were actually hashing:

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

Returns:

```
TheChar TheASCIIvalue   CharCP1255  TheASCIIvalueCP1255
è       232             ?           63
```

A `?` ? Just to verify, run:

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

Ah, so Code Page 1255 doesn't have the `è` character, so it gets translated as everyone's favorite `?`. But then why did that match the MD5 hashed value in .NET when using the ASCII encoding? Could it be that we weren't actually matching the hashed value of `è`, but instead were matching the hashed value of `?`:

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

Yup. The true ASCII character set is just the first 128 characters (values 0 - 127). And as we just saw, the `è` is 232. So, using the `ASCII` encoding in .NET is not that helpful. Nor was using `COLLATE` on the T-SQL side.

Is it possible to get a better encoding on the .NET side? Yes, by using Encoding.GetEncoding(Int32), which allows for specifying the Code Page. The Code Page to use can be discovered using the following query (use `sys.columns` when working with a column instead of a literal or 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
```

The query above returns (for me):

```
Latin1_General_100_CI_AS_SC    1252
```

So, let's try Code Page 1252:

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

Woo hoo! We have a match for `VARCHAR` data that uses our default SQL Server collation :). Of course, if the data is coming from a database or field set to a different collation, then `GetEncoding(1252)` might not work and you will have to find the actual matching Code Page using the query shown above (a Code Page is used across many Collations, so a different Collation does not necessarily imply a different Code Page).

To see what the possible Code Page values are, and what culture / locale they pertain to, please see the list of Code Pages here (list is in the "Remarks" section).

Additional info related to what is actually stored in `N…

TSQL md5 hash different to C# .NET md5

Solution

Problem

Related problems