Hey guys,
All at peace?

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

Validation of the encoded string is done 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 safer than cryptography, since the complexity of coding is much greater and makes hacker attacks difficult to break the encoded string. Although it is a very secure method, the existence of collisions has already been proven, which are two or more strings that generate the same HASH, causing its security to be broken by a string that is different from the original. As we increase the number of bits in our encryption, the more secure it becomes and requires more processing to break it (and more space to store the 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 everything said above into practice. Get to work! The HASHBYTES system function returns the MD2, MD4, MD5, SHA1, or SHA2 (SQL Server 2012 onwards) hash of your SQL Server input.

Example 1 – SHA1:

-- Retorno: 0x18CAA0AA39B10AE2AB9CFF8DAEC38619F64CF38D
SELECT HASHBYTES('SHA1', 'Dirceu Resende')

Example 2 – MD5:

-- Retorno: 0x48699133C5F20DB0CAB52CC203CFFED1
SELECT HASHBYTES('MD5', 'Dirceu Resende')

Example 3 – SHA1 with data from a table:

IF(OBJECT_ID('tempdb..#Teste') IS NOT NULL) DROP TABLE #Teste
CREATE TABLE #Teste (Ds_Texto nvarchar(50))
INSERT #Teste VALUES ('Dirceu Resende'), ('SQL Server');

SELECT HASHBYTES('SHA1', Ds_Texto) FROM #Teste

/*
-- Retorno:
0xA76690CCD5AF74518E9C6218B4CBC6BA313C86E6
0xD983D8A96DAA8A7368438848B6D988FC4AA6659B
*/

Until next time!