Encrypting password in sql server

Despite that fact that everyone knows storing passwords in a plain text is evil, I am convinced that there are many large organisations that store customer passwords in plain text.

Recently a large company was attacked by a group of hackers and they used sql injection to get customer password which they then used to login to a portal and do bad things..

In this post I will show you a simple way to store passwords securely in your database.

There is an important difference between encrypting passwords and encrypting other information such as customer salary or credit card information. In the case of passwords, in most cases the DBA or any user does not need to know the customers password, so all you need is a way to encrypt the password (aka one way hash).  But credit card numbers need to be seen in the plain form by the Finance team and employee salary by the Account team so you need to be able to encrypt and decrypt this information.

If you are encrypting the password data using EncryptByPassPhrase or EncryptByKey and a hacker/ or an “non-authorised internal user” got hold of your key or passphrase “which usually will be found on the server“ then your passwords can be decrypted using DecryptByPassPhrase or DecryptByKey.  

In order to do that, I have written a function called password () which tales a string as a parameter and returns a string of the hash value. The function is inspired by the php function password() .. php rocks ;-)

Create a function

CREATE FUNCTION [dbo].[password]

(

          @stringToHash VARCHAR(128) 

)

RETURNS VARCHAR(128)

AS

/*********************************************************************************

    Name:       dba_parseString_udf

    Author:     Y Bamarni

    Purpose:    A function to hash a string

    Date        Initials    Description

    ----------------------------------------------------------------------------

    2011-09-06           Initial Release

*********************************************************************************

Usage:           

    SELECT dbo.password ('mypassword') as encryptedString

*********************************************************************************/

BEGIN

      DECLARE @hash VARCHAR(10),

                  @EncryptedValue   VARCHAR(128)

                 

      SET @hash='SHA1'

      SELECT @EncryptedValue=CONVERT(VARCHAR(128), HASHBYTES(@hash, @stringToHash),2)  -- the convert is to remove the begining two characters

      RETURN @EncryptedValue;

END

GO

Now in order to test this, we will create a test table called Test

create table Test (id int identity, username varchar(50), pass varchar(128))

To insert a new record, use the new password function to convert the password string to an encrypted string

insert into Test (username, Pass) values ( 'Yasir1',  dbo.password('mypassword'))

insert into Test (username, Pass) values ( 'Yasir2',  dbo.password('mypassword2'))

insert into Test (username, Pass) values ( 'Yasir3',  dbo.password('mypassword3'))

insert into Test (username, Pass) values ( 'Yasir4',  dbo.password('mypassword4'))

If you run a SELECT, you will notice that the password is encrypted

select top 100 * from Test;

If you want to update the password (say you need to reset a password)

update Test set pass =dbo.password('myNewPassword') where id=1

If you want to check if a password supplied by the user matches the password hash in the database.

SELECT * from Test WHERE username ='Yasir3' and pass=dbo.password ('WrongPassword')             -- deny

SELECT * from Test WHERE username ='Yasir3' and pass=dbo.password ('myNewPassword3')            -- grant