TOC
Introduction
This article will explain how to store passwords in a databse the secure way,
and how to create a simple login.
It's not an article about architecture or general security,
but how to transport password securely in and out of database and how to store it there.
Background
The reason for writing this article is after reading a lot of questions here at codeproject and other forums.
A lot the code out there is vulnerable for sql injections and the password is often stored in clear text.
I will will try to explain how to deal with this in a simple but understandable way so even beginners can secure their data.
Using the code
First of all you will need somewhere to store usernames and passwords. In this article I will use Sql server, but it's the
same technique for all types of databases.
I will not create a huge table keeping all sort of userdata, but simply three properties. UserId, Username and password.
To get started I will create a table to store users. I'll call this table "Users" and it will contain 3 columns.
(I will add one more column later)
UserId int (not null) Identity
Username varchar(50)
Password varchar(50)
CREATE TABLE [Users](
[UserId] [int] IDENTITY(1,1) NOT NULL,
[Username] [varchar](50) NOT NULL,
[Password] [varchar](50) NOT NULL
)
To add users to our database we can create a function to do this. A function which takes arguments username and password.
I'll create a class "UserDatabase" to keep all my interaction with the Users table in one place.
public class UserDatabase
{
private static string connectionstring = "SERVER=127.0.0.1; DATABASE=MyDatabase; UID=***; PWD=***";
public static bool AddUser(string username, string password)
{
SqlConnection con = new SqlConnection(connectionstring);
using (SqlCommand cmd = new SqlCommand("INSERT INTO [Users] VALUES (@username, @password)", con))
{
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@password", password);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
return true;
}
}
Now we have a simple function to add users. It does not ckeck if username exists or anything, but simply inserts
a new user.
So now we can add a webform and let the users register:
<form id="form1" runat="server">
<asp:TextBox ID="txtUsername" runat="server"/>
<asp:TextBox ID="txtPassword" runat="server" TextMode="Password"/>
<asp:Button ID="btnAddUser" runat="server" Text="Submit" OnClick="btnAddUser_Click" />
</form>
And the code behind:
protected void btnAddUser_Click(object sender, EventArgs e)
{
string username = txtUsername.Text;
string password = txtPassword.Text;
bool result = UserDatabase.AddUser(username, password);
}
Now a user can fill in the form, click submit and the user and password will be stored in our database.
The only problem is that the password will be stored as clear text and that is not ok!
If I fill out the form and hits submit this will get stored in our table:
UserId | Username | Password
-------------------------------------------
1 | AlluvialDeposit | mySecretPassword
And that is what this article will help you avoid using hashed passwords.
What is a hash?
There are tons of articles about hashing and how they work. But I'll explain the very basics.
If you hash a string (or other data) you will receive a string that is impossible to transform back to it's original form.
But the key thing is that you will always get the same result if you use the same hash algorithm.
If I hash (using sha1) my password "mySecretPassword" I will get the string "F032680299B077AFB95093DE4082F625502B8251"
That string is impossible to revert back to the original value!
Hash a string is easy. This function will do the job
public class Security
{
public static string HashSHA1(string value)
{
var sha1 = System.Security.Cryptography.SHA1.Create();
var inputBytes = Encoding.ASCII.GetBytes(value);
var hash = sha1.ComputeHash(inputBytes);
var sb = new StringBuilder();
for (var i = 0; i < hash.Length; i++)
{
sb.Append(hash[i].ToString("X2"));
}
return sb.ToString();
}
}
I'm using SHA-1 which will return a string with a length of 40 no matter what the input value is. If you hash a megabyte of plain text, the hash would still be 40 characters long. So yes, there is a theoretical way of producing 2 identical hash-values with 2 different input values. But that's rare! Very rare.
Add hash functionality
So if we look back on AddUser function in our UserDatabase class:
cmd.Parameters.AddWithValue("@password", password);
cmd.Parameters.AddWithValue("@password", Security.HashSHA1(password));
So if you go back to your browser and try to register a new user, the table will get another row with userId 2.
But this time the users password is hashed!
UserId | Username | Password
-------------------------------------------
1 | AlluvialDeposit | mySecretPassword
2 | AlluvialDeposit | F032680299B077AFB95093DE4082F625502B8251
If someone hack our database they will not be able to see our users password! or?
Well that is not true. A hash is impossible to reverse, but hackers often use large table of hashed strings.
If a hacker has a generated database with 10 million random passwords (and the most common used ones), he could easily look in
his database for the the same string "F032680299B077AFB95093DE4082F625502B8251" to see if he finds any match.
Unfortunately he will often find a match!
Another problem with this approach is that two users with the same password will have the same hashed password as well.
That could also be a security risk.
Fortunately this is quite easy to avoid. The thing is that you will need some kind of unique string for each
user which are used when you hash the password. That is called "password salt". I use a System.Guid
to do the job.
This Guid must be stored in the user table so that we can use it to validate the password when the user wants to authenticate.
Add column "UserGuid" to "Users" table with the datatype uniqueidentifier
ALTER TABLE [Users]
ADD UserGuid uniqueidentifier NULL
We have to do a few changes to our AddUser() method as well:
public static bool AddUser(string username, string password)
{
Guid userGuid = System.Guid.NewGuid();
string hashedPassword = Security.HashSHA1(password + userGuid.ToString());
SqlConnection con = new SqlConnection(connectionstring);
using (SqlCommand cmd = new SqlCommand("INSERT INTO [Users] VALUES (@username, @password, @userguid)", con))
{
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@password", hashedPassword); cmd.Parameters.AddWithValue("@userguid", userGuid);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
return true;
}
If we fire up our AddUser.aspx again and store another user with the same password as previous ones, we
will get this result:
UserId | Username | Password | UserGuid
--------------------------------------------------------------------------------------
1 | AlluvialDeposit | mySecretPassword | null
2 | AlluvialDeposit | F032680299B077AFB95093DE4082F625502B8251 | null
3 | AlluvialDeposit | 42444F86F185B7F229DD155E8BCF2A9E6D06453C | E089D2FC-97DB-4DA5-A13C-9FCBBD8B7E95
And if we add another user with the same criterias once more:
UserId | Username | Password | UserGuid
--------------------------------------------------------------------------------------
1 | AlluvialDeposit | mySecretPassword | null
2 | AlluvialDeposit | F032680299B077AFB95093DE4082F625502B8251 | null
3 | AlluvialDeposit | 42444F86F185B7F229DD155E8BCF2A9E6D06453C | E089D2FC-97DB-4DA5-A13C-9FCBBD8B7E95
4 | AlluvialDeposit | D329E99795960F93857CA49D5FB0A8F141C34671 | C6B73B2D-F704-4250-81C4-EFAEC5DB5F54
User id 3 and 4 has the same password! But in the database it's impossible to tell!
And a hacker cannot check our passwordHash to see if he finds any matches with his pregenerated data.
It is possible for a hacker to find out what the password is, but that will take a lot of time! And he will
have to do a time consuming operation for each user in the datasbase.
So now our data is safe (enough).
Don't forget to check if username already exists when a new user signs up!
Authenticate users
Add this function to your class "UserDatabase" This function will give you a userId based on username and password.
public static int GetUserIdByUsernameAndPassword(string username, string password)
{
int userId = 0;
SqlConnection con = new SqlConnection(connectionstring);
using (SqlCommand cmd = new SqlCommand("SELECT UserId, Password, UserGuid FROM [Users] WHERE username=@username", con))
{
cmd.Parameters.AddWithValue("@username", username);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while(dr.Read())
{
int dbUserId = Convert.ToInt32(dr["UserId"]);
string dbPassword = Convert.ToString(dr["Password"]);
string dbUserGuid = Convert.ToString(dr["UserGuid"]);
string hashedPassword = Security.HashSHA1(password + dbUserGuid);
if(dbPassword == hashedPassword)
{
userId = dbUserId;
}
}
con.Close();
}
return userId;
}
What we do here is to get the user from the database based on username. If we find a matching user we will have to check
whether the password is correct. The password is hashed with password salt so we have to do the same procedure (hashing) with
the password the user typed, as we did when the user signed up. If the password is the same, the hash function will return the same hash-result as the hash-password in the database.
We compare these two hashed password to see if they match.
Now we can authenticate the user using this simple code:
int userId = UserDatabase.GetUserIdByUsernameAndPassword("AlluvialDeposit", "mySecretPassword");
if(userId > 0)
{
}
Create a simple login-form and validate username/password in codebehind:
<form id="form1" runat="server">
<asp:TextBox ID="txtUsername" runat="server"/>
<asp:TextBox ID="txtPassword" runat="server" TextMode="Password"/>
<asp:Button ID="btnLogin" runat="server" Text="Submit" OnClick="btnLogin_Click" />
<hr />
<asp:Label runat="server" id="lblLoginResult"/>
</form>
protected void btnLogin_Click(object sender, EventArgs e)
{
var username = txtUsername.Text;
var password = txtPassword.Text;
int userId = UserDatabase.GetUserIdByUsernameAndPassword(username, password);
if(userId > 0)
{
lblLoginResult.Text = string.Format("You are userId : {0}", userId);
}
else
{
lblLoginResult.Text = "Wrong username or password";
}
}
What about password recovery
Sometimes users forget their passwords. We will have to deal with it but we can't read a users password and then we can't send en email
telling the user his/her password (that would be a bad idea anyway!).
So if the user has lost the password, we will have to send an email with a link to a page where
the user can set a new password and store it the same way as we did when the user signed up.
A very basic explanation about sql-injections
Sql-injections is one of the most used ways to hack a website. It's easy to do and there are
a lot of websites which are vulnerable for this kind of attack.
Sql-injection is to tamper your sql-query. If I am able to put some of my strings into your sql-query, then you have a problem!
Lets say you have a login-form with this code:
protected void btnLogin_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(connectionstring);
string sql = "SELECT UserId FROM [Users] WHERE username='" + TextBox1.Text + "'";
using (SqlCommand cmd = new SqlCommand(sql, con))
{
SqlDataReader dr = cmd.ExecuteReader();
}
}
The variable sql will hold the query to get executed against our database. So if a user writes
"hello" in TextBox1, the sql would be:
SELECT UserId FROM [Users] WHERE username='hello'
That would not be a problem. But if a user writes
'; DELETE FROM [Users] our sql would be:
SELECT UserId FROM [Users] WHERE username=''; DELETE FROM [Users]
Now all your users are deleted!
That is just an easy example to show what is happening. I clever user could write something in that textbox
to log in as any user in your database.
If you always use SqlParameters (like in this article) you will avoid the most obvious way of sql-injections.
Summary
This is an example and it covers the very basics. But if you follow this guidelines you will take a huge step in the right direction. It's a matter of your users security!
Good luck with your secure user-database 
