SQL Server for MD5 encryption

Official definition functions:

Hashbytes ( '<algorithm>', { @input | 'input' } )

<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1

Parameter explanation:

Algorithm: Identifies the hashing algorithm used to perform a hash operation on the input. This is a required parameter with no default value. You need to use single quotation marks.

@input: The data type is varchar, nvarchar, or varbinary

'input' : string data

return value:

varbinary (maximum 8000 bytes)

Note: The allowable input value is limited to 8,000 bytes. The output conforms to the algorithm standard: MD2, MD4, and MD5 are 128 bits (i.e. 16 bytes); SHA and SHA1 are 160 bits (20 bytes)

One: Example MD5 encryption:

A complete notation:

PRINT LOWER (sys.fn_varbintohexstr (hashbytes (' MD5 ', ' This string needs to be encrypted ' )) >> 8926206f9ebb0bee4bbb0724fccb4410

Gradually split below

1. First use the Hashbytes function to see the resulting results

PRINT hashbytes (' MD5 ', ' This string needs to be encrypted ') >> 0x8926206f9ebb0bee4bbb0724fccb4410

Because the value returned by the function is a binary varbinary data , not a string cannot be used directly, so it is converted to a string

2. Using Sys.fn_varbintohexstr (@pbinin)

@pbinin: varbinary (max) data type

return: nvarchar data type

PRINT sys.fn_varbintohexstr (hashbytes (' MD5 ', ' This string needs to be encrypted ')) >> 0x8926206f9ebb0bee4bbb0724fccb4410

3. How to get 32-bit MD5 value, seemingly the largest can only to 32 bits, using the right function to intercept

PRINT Right (Sys.fn_varbintohexstr (' Hashbytes (' MD5 ', ' This string needs to be encrypted '))>> 8926206f9ebb0bee4bbb0724fccb4410

function definition

)

Parameters:

Character_expression: Can be a character or binary data expression, constant, variable, column, any data type that can be implicitly converted to varchar or nvarchar (text,ntext requires a display conversion)

Integer_expression: A positive integer that will return the number of characters

Return value: character_expression returns varchar for non-Unicode and returns nvarchar if Unicode

4. If you need uniform capitalization, use lower (character_expression ), Upper (character_expression ) to convert

Two: about the character encoding problem of MD5 encryption

A couple of comparisons first.

1. Character "È"

sqlserver:c05fc85230c60ed8fecb3670e77e603d

Result: Corresponds to C # gb2312

2. Character "This is kanji"

sqlserver:e8c9ab48b603f81b87f4ed4f1005bb6e

Result: Corresponds to C # gb2312

3. Character "123456"

sqlserver:e10adc3949ba59abbe56e057f20f883e

Results: Corresponds to C # gb2312,utf-8, Utf-7, ASCII

4. Character "Pxs123"

Sqlserver:684d95499489435c9351efcdd68b25f2

Results: Corresponds to C # gb2312,utf-8, Utf-7, ASCII

4. Character "[email protected]#."]

sqlserver:fc05d7826726e773e993bdca88f6b181

Result: With C # gb2312,utf-8, ASCII corresponds

5. Character English symbol "[email protected]#.&*) |_;"

sqlserver:4c782d772102482d09696d11bde1a02a

Result: With C # gb2312,utf-8, ASCII corresponds

6. Character character English mixed symbol "! ）*、“

sqlserver:1a891b7b5b3285925e2783694c2a367c

Result: Corresponds to C # gb2312

6. Character character English mixed symbol "Chinese character Hanzi"

Sqlserver:49212c24bdb2fa254880119a332f86d6

Result: Corresponds to C # gb2312

8. Character "プログラミン"

Sqlserver:045b556a9cf5f0fe92ec7a7c9b3f66ca

Result: Corresponds to C # gb2312

In contrast:

SQL Server MD5 encryption function, the character of non-English characters will be encoded in accordance with the gb2312 method to calculate the encryption value, then C#,JS and other calculations MD5 encryption value is required to gb2312 encoding, in order to produce the same result.

If you are English characters, you can use Utf-8 to edit them.

^ If there is any mistake, please correct me! ^

SQL Server for MD5 encryption