Tag Archives: databases

Starting and Salting

Hello. I’ve been meaning to start a technical blog for a while covering some interesting aspects of what I do at work. I work for the IT services division of a large telecoms company, mostly developing complex web applications in ASP.Net, C#, Oracle and SQL Server. The specifics are sometimes sensitive so I won’t go into those, but sometimes there are techniques that come out of it that are worth discussing.

To start things off, password salting. This is a technique for preventing passwords being stored in plain text where it’s easy for them to be stolen. A very basic login table in a database may have something like this:

Username Password
Alice 123456
Bob password

If someone manages to get a copy of this table, they can easily log in as any user. Why not generate some summary of the password, called a hash, and store that instead? For a very simple method you could just add up the ASCII values of each character and store that. If you store the hash, you can compare that to a hashed version of what someone tries to use as a password. However this simple algorithm has the disadvantage that different  passwords might have the same hash, known as a collision. Someone using 123456 as a password would have the same hash as someone using 235416, so you could get in with either. There are better algorithms that reduce the problem of collisions. If we decide to use the SHA-1 algorithm as one of them, our table above would therefore be changed to this:

Username Hashed_Password
Alice 7c4a8d09ca3762af61e59520943dc26494f8941b
Bob 5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8

If someone then tries to log in as Alice with a password of 123457, they get a very different hash value of 908f704ccaadfd86a74407d234c7bde30f2744fe.

To get an SHA1 hash in SQL Server, use:

SELECT HASHBYTES ('SHA1', <password>)

Obviously this is an improvement over just storing the password. However, hashed passwords are vulnerable to a Rainbow Table attack, which is basically a lookup table of hashes and their unhashed equivalent. Also, if several people have the same hash, you know they also have the same password. To avoid this, it would be good to make passwords more complex, which is where salting comes in. A salt is a random string of characters that is different for each user. Take your pick of how to generate one. I like the NEWID() function in SQL Server which generates a random GUID each time it’s called:

SELECT NEWID()

Once you have the salt, it can be stored alongside the user details. It doesn’t need to be hashed or encrypted. The password hash is now stored as <password><salt>, or

SELECT HASHBYTES ('SHA1', <password> + <salt>)

This would make our table like this:

Username Salt Hashed_Password
Alice D0157CCE-C811-4E4E-8C4F-AD4ED37023AF 5f9f5a293710fdad1abbdb02bc4ac2eccfd81e7e
Bob F45D4893-1A46-4C2F-B783-E0903E2EB5CF 45843ca5737eccc04690dd566ee933a414a74a62

To compare an incoming password attempt, we combine it with the hash to see if it matches the hashed value. In T-SQL:

DECLARE @pass VARCHAR (10)
SET @pass = '123457'
SELECT CASE WHEN HASHBYTES ('SHA1', @pass + salt)  = Hashed_Password THEN 'Match' ELSE 'No match' END
FROM usernames

A bit more complex than just comparing two strings, but a lot more secure. As long as some method of generating hashes is available it isn’t too difficult to implement (the .Net library has some methods and I’d be very surprised if Oracle and Java didn’t have some as well) and it saves a system being compromised. The main drawback of hashing and salting passwords is that it is one way: if you forget your password, there’s no easy way to decrypt it. I’ve used the SHA1 algorithm here because it’s one of the most common but better ones are available. SQL Server 2012 supports SHA2, and SHA3 is in the process of being finalized.