Click on the banner to learn about and purchase my database training on Azure

Working with cryptographic functions (MD4, MD5, SHA1, SHA2_256, and SHA2_512) using the SQL Server HASHBYTES function

Views: 9.340 views
Reading Time: 2 minutes

Hello people,
All in peace ?

A little about coding
In this post I will demonstrate a little more about the SQL Server HASHBYTES function, which allows working with HASH-based cryptographic functions, where once encoded, it is not possible to get the original string again. If you want to learn more about encryption using the Base64 algorithm, which lets you encode and decode a string, go here.

The validation of the encoded string occurs through comparison, re-encoding the string to be tested and comparing the newly generated HASH with the original HASH. If the HASH's match, the tested string is confirmed as true.

This form of validation is considered more secure than cryptography, since the complexity of the encoding is much greater and makes it difficult for hackers to break the encoded string. Although it is a very safe method, collisions have already been proven, which are two or more strings that generate the same HASH, causing your security to be broken by a string that is different from the original. As we increase the amount of bits in our encryption, the more secure it becomes and demands more processing to break it (and more space to store HASH's)

Algorithm encryption level:

  • 128 bits (16 bytes): MD2, MD4, and MD5
  • 160 bits (20 bytes): SHA and SHA1
  • 256 bits (32 bytes): SHA2_256 (SQL Server 2012 onwards)
  • 512 bits (64 bytes): SHA2_512 (SQL Server 2012 onwards)

The HASHBYTES Function
Let's put into practice all that has been said above. Get to work! The HASHBYTES system function returns the hash of MD2, MD4, MD5, SHA1, or SHA2 (SQL Server 2012 onwards) from your SQL Server entry.

Example 1 - SHA1:

Example 2 - MD5:

Example 3 - SHA1 with data from a table:

To the next!