Non Reversible Encryption with SQL Server: HASHBYTES function

Not all cipher texts are required to be converted back to plain texts. Good example is "passwords". All we need with non reversible encryption is, store them in encrypted format and perform comparison when required. What is the best way of implementing this with SQL Server?

SQL Server provides number of functions that can be used for encrypting plain texts using different mechanisms. Most of the functions allow you to encrypt and then decrypt them back to plain text. Examples for these functions are "ENCRYPTBYKEY" and "ENCRYPTBYCERT". If decryption is not required or the requirment is non reversible encryption, then the best function to be used is "HASHBYTES".

HASHBYTES returns the hash of given clear text based on the algorithm used. Algorithms supported are: MD2, MD4, and MD5 (128 bits (16 bytes)); SHA and SHA1 (160 bits (20 bytes)); SHA2_256 (256 bits (32 bytes)), and SHA2_512 (512 bits (64 bytes)). SHA2_256 and SHA2_512 available only with SQL Server 2012 and above.

Though we have been given many algorithms for this, most of them are susceptible for several attacks and no longer considered as secured cryptography algorithm. Some of them are known to "collisions" that generate same output for different inputs. If you are using a version before 2012, best is SHA1 even though it has been marked for "collisions". If the version of SQL Server is 2012 or above, best is either SHA2_256 or SHA2_512.

Here is a sample code that shows the usage of HASHBYTES;

This code creates a table and inserts two records.

-- Creating table
IF OBJECT_ID('dbo.UserCredential', 'U') IS NOT NULL
 DROP TABLE dbo.UserCredential
GO
CREATE TABLE dbo.UserCredential
(
 UserId int identity(1,1) PRIMARY KEY
 , UserName varchar(20) NOT NULL
 , Password binary(64) NOT NULL
)
GO

-- Inserting records
INSERT INTO dbo.UserCredential
 (UserName, Password)
VALUES
 ('Dinesh', HASHBYTES('SHA2_512', 'Pa$$w0rd'))
 , ('Yeshan', HASHBYTES('SHA2_512', 'P@$$w0rD'))

-- Checking records inserted
SELECT * FROM dbo.UserCredential;

Since the cipher text cannot be reverted back with HASHBYTES, here is the way of doing the comparison.

-- Validating user
IF EXISTS (SELECT * FROM dbo.UserCredential
   WHERE UserName = 'Yeshan'
    AND Password = HASHBYTES('SHA2_512', 'P@$$w0rD'))
 Print 'User authenticated'
ELSE
 Print 'Invalid user!'