In Focus

LAME Question of the Day: How To Store a Password in a Database?

Here we will see three approaches for storing user credentials into the database. The plain-text approach should never be used. The salting and hashing technique seems secure and makes it difficult to crack for the hacker but it is never impossible to hack a database.

Question of the Day

Are you a programmer and develop web applications that communicate with databases like SQL Server, MySQL and/or Oracle? If your answer is no then this may not be the article for you and you should continue with what you're doing before stumbling on this article. But if your answer is yes then I have a million-dollar question for you: How will you store a password in a database?

    

The Assignment

Let's make the article a little more interesting and consider a scenario where you were asked to develop a web application for a client. The requirement is to create a registration page to register the user. The user registers himself on the website by providing an email-id and a password and, based on the same credentials, an authorization level is set for the user by which the user is allowed to access the resources of the website. So the question arises, how will you proceed?

There are many ways to satisfy the requirements but I'll tell you the three commonly used approaches. I have intentionally named them:

  1. The Bad Approach.
  2. The Less Bad Approach.
  3. The Good/Recommended Approach.

Let's become familiar with all the approaches and decide which one fits perfectly to complete the given task. I might sound a little skeptical but most of you will fall under the category of "The Bad Approach".

The Bad Approach

I called this approach the "beginners approach" because whenever I Google for an article about "Registration Form in ASP.NET", I'll end up with a tutorial using this approach. If you don't believe me, see for yourself [^].

Let's see how this approach works. If I were you and needed to satisfy the same requirements, I would create a simple ASP.NET project and add a web form (Registration.aspx) to the project and create a database in SQL Server with a layout as in the following:

create account

That's easy, right? On the button click event of Create Account, I'll write the code to store the user credentials into the database and bingo, the task is complete and I'm done for the day. This is the commonly used approach by most of us, well I did for some time. If you have used (or are familiar with) this approach then I have a question for you. Did you find any concerns with this approach? If I were to guess, your concerns will be:

  1. Why did you use the outdated ASP.NET Web Forms instead of ASP.NET MVC?

  2. Which technology did you use to store the user details into the database, ADO.NET or ORM?

  3. If you use ADO.NET, did you use Stored Procedures to insert records into the database?

  4. Did you use the 3-Layer architecture?

  5. What about Validations?

If your concern relate to the things I listed above, then you didn't understand the intent of this article. Your concerns are highly important and should be discussed but I want you to shift your focus for some time from "Performance Oriented Applications" to "Security Oriented Applications". This approach draws several security concerns and you as a programmer should be aware of them.

Major drawback of this approach

There are many flaws with this approach but the major one is storing the user password as "plain-text" into the database. Storing a user password as plain-text into the database is very dangerous and should never ever be used. It can cause severe hazardous results such as:

  1. If your website is compromised and they get access to the database then they would then have access to all the passwords.

  2. If the user uses the same password for other web sites as well, then the hackers can access the other sites as well.

  3. This approach weakens the security of your web site, putting your users, your employer and of course yourself at risk.

  4. Some of the major attacks against websites that store passwords this way are: Sony 2011 Hack, Plenty Of Fish Hack, Microsoft India Hack

30% of websites store plain-text Passwords:

If your password is stored as plain-text by the website, it clearly means that you are not secure and your identity is at risk. Someone can steal your password and if you are "one of those guys" that uses the same password for nearly everything, then I can only say, "Pray for Yourself". According to plain-text Offenders, over 30% of the sites store plain-text passwords and put your identity at risk.

The Less-Bad Approach

This approach is slightly better than the previous one. Instead of storing the user passwords as plain-text, we can hash the password using a one-way hash function like MD5 or SHA-1 and store the hashed value into the database. To complete the given assignment using this approach, I can hash the user passwords before storing it into the database as follows:

enter details

I have intentionally kept the password TextBox visible because I wanted to show you that I've entered the password as "sahil123" and using a hash function it is stored inside the database as a hashed string "4aa4029...0e8". Let's take some time to understand how this approach actually works.

How the hash function works

To explain this approach at the very basic level, consider a box of Mangoes and you want to make a Mango shake from it. You put the Mangoes into the mixer, start the mixer and your shake is ready. The key point to note is that you cannot make Mangoes from that Mango shake, that means the process is only one-way, the same principle applies to one-way hash functions.

A hash function is a one-way function that takes a string as input and transforms that input to a fixed-length hash value that represents the original string. The input string is called the message and the output hash value is called a message digest or simply digest. This approach is commonly used because the output hash value is considered nearly impossible to inverse, however some hackers are successful in reversing the process.

message digest

So, instead of storing plain-text passwords, we store the output of a hash function into the database. You might be wondering how to authenticate the user if we cannot return to the plain-text password from a hash. In fact, we don't need to. To verify if a user has entered a correct password during log-in, we use the same hash function on the input text by the user, then compare the newly constructed hash with the hash we've stored in the database. If they match, then the user is allowed into the site, otherwise an incorrect password is displayed.

Major drawback of this approach

You might be thinking that this approach is better than storing plain-text passwords and while your passwords are stored as hashes that are difficult to crack, you are secure. Let's do it. Hold your Horses for a second, there are certain drawbacks with this approach as well and you should be aware of them:

  1. Suppose two users have entered the same password during registration, so their hashes are also the same and stored into the database as the same. This is called Hash Collision and if a hacker is able to find the password of one of these users, he technically can have the passwords of all the other people with the same password.

  2. Another problem with this approach is the commonly used hash functions. Most of them, including MD5 and SHA-1, are not completely "one way" and are considered broken. Several security experts advise avoiding such functions for hashing.

  3. Since this approach is better than storing plain-text passwords, it is possible for hackers to try and guess your passwords with techniques like Dictionary Attacks using the existing standard Rainbow Tables.

  4. The biggest blunder resulting from this approach was: LinkedIn Hack.

The Good/Recommended Approach

I was reading about database security the other day and stumbled on a very nice article [^]. It was only then I realized that I was doing the wrong approach all this time and should change. Until now in the article, we are able to obfuscate the password from someone viewing it directly from the database, but it is still possible for hackers to try-and-guess the password. We need to take extra measures to secure the password and this is where this technique is useful.

In this technique, instead of hashing the password alone, we concatenate the password with a unique random string called the salt then hash the obtained string using a hash function and then store the salt and hashed value into the database. Using this approach, we have added one more layer of security for passwords and even if two people have entered the same password, their salt will always be different and the hash produced should never be the same.

To complete the given task with this approach, I need to modify my database a little bit and add a column to store the salt. It may be noted that the salt should be unique for every user, otherwise collision can occur. The modified layout should be like this:

create new account

If you're interested in the actual implementation of this approach using ASP.NET/C#, this article will be beneficial for you [^].

How this approach works

To explain this approach in simple terms, let's take the same example of Mangoes. We know that we cannot convert a Mango shake into a Mango but by tasting the shake we can detect that the shake is made from Mangoes and this is the problem with the previous (salt-less) approach. To make it more secure, we can mix some strawberries and apples with Mangoes in the mixer. In this way, the resulting shake is not a "Mango shake" any more and turned into a "mixed shake", so to guess the original ingredient is quite difficult.

approach works

With this approach, we can make the database more secure for hackers to attack. Some of the important things to note when using this approach are:

  1. The salt should always be unique and randomly generated for each new password, otherwise it won't be useful.

  2. The one-way hash function should be secure and slow. Avoid using MD5 and SHA-1, instead use bcrypt.

Conclusion

We have seen three approaches for storing user credentials into the database. The “plain-text” approach should never be used. The “salting and hashing” technique seems secure and makes it difficult to crack for the hacker but it is never impossible to hack a database. We can only make the process harder. I'm not an expert in security, only a beginner. This article is the result of my studies and understanding of database security. I may have missed several important points or conclude multiple meanings in this article. I would love to learn more from anyone out there. Your feedback and constructive criticism is always appreciated, keep it coming. Until then try to put a ding in the universe.