Building a Secure Login Form (Parameterized Queries Part 2)

Building a Secure Login Form (Parameterized Queries Part 2)

Rate This
  • Comments 28

In my first post on parameterized queries I built a simple login form that really was a contrived example meant to showcase how to use the TableAdapter Configuration Wizard to configure a parameterized query. However, since I opened myself up here, I felt it socially responsible to show how to make this a bit more secure by showing you how to store passwords in a secure way in a database.

There are actually many many secure ways to store data in your database including using the encryption features of SQL-Server 2005 which allows you to protect columns inside your database at the database level, independent of the application. Additionally, if we're just talking about user's logins and you're building a multi-tier or SO application then using the ASP.NET membership services is probably your best choice. In next version of Visual Studio, Microsoft made these ASP.NET services easily accessible to any client application (Winforms, WPF, Silverlight) not just WebForms. But what if you're building a single-user application or a client-server app with only a handful of users and you don't have (or want) a web server or maybe you're not using SQL-Server as your database?

The first recommendation for this scenario is don't store passwords at all. If you don't have passwords in your application then you don't have to worry about someone stealing them. Instead, consider using the Windows Identity as the user of your application. This means that your application will not need to store passwords, only user names, because it would be using the logged in Windows user which has already been authenticated through the Windows OS. To access the user name of the currently logged in user from your client code:

Imports System.Security.Principal
...
Dim user As WindowsIdentity = WindowsIdentity.GetCurrent()
Dim userName As String = user.Name

The only thing you would need to do in your database Login table is make the UserName field unique. Then you could write a very simple parameterized query.

SELECT COUNT(*) FROM Login WHERE UserName = @UserName

So your login code would be very simple. If you name the above parameterized query on your TableAdapter "GetLoginByUserName" then it would be something like:

Imports System.Security.Principal
...
Dim user As WindowsIdentity = WindowsIdentity.GetCurrent()
Dim userName As String = user.Name

If CType(Me.LoginTableAdapter1.GetLoginByUserName(userName), Integer) = 1 Then

    MsgBox("Welcome to my application!")

Else

    MsgBox("Invalid username or password.")

End If

(NOTE: This code assumes that the application is connecting to the database directly and not through a service layer. If you are connecting to a service layer then you need to configure your web server to authenticate Windows users by not allowing anonymous access and only allowing Windows Authentication. For more information, read this.)

However, what if you cannot use this method of authentication? For instance, your application runs on a shared computer that remains logged in under one Windows login, but you require users to login separately to your application. In that case you're going to need to store passwords. However, if we store passwords as clear text in our database, anybody that can get a glimpse of the Login table will have a bunch of user credentials to access the application! The safest thing to do is to use a one-way hashing algorithm and store the hashes in your database table instead. The .NET Framework gives you a lot of help here by providing a handful of proven hashing algorithms in the System.Security.Cryptography namespace. The most common are SHA-1 and MD5. To hash a string using the SHA-1 becomes very simple in .NET: 

Imports System.Security.Cryptography

Imports System.Text

... 

Function HashEncryptString(ByVal s As String) As String

    Dim hasher As New SHA1CryptoServiceProvider()

    Dim clearBytes As Byte() = Encoding.UTF8.GetBytes(s)

    Dim hashedBytes As Byte() = hasher.ComputeHash(clearBytes)

 

    Return Convert.ToBase64String(hashedBytes)

End Function

Hash algorithms are one-way so it's very very difficult to tell what the original password is from a computed hash. (So if a user forgets their password, you won't be able to tell them what it was.) So when we store user names and passwords in our Login table we can easily hash the value of the submitted password and store that instead. So we're secure now, right? Well almost! Let's take a look at my Login table in this example:

Notice that Beth and Joe both have the same hashed password. This means that both these passwords are the same as clear text as well. An attacker could probably figure out the password by using a dictionary attack on our table. So what can we do?

There's a technique called salting where you take the password and "salt" it with a random value and then hash that. This random value is different for each login. This will create different hashed passwords for the same clear text password, making it extremely difficult to break. To be even more secure, you should store this salt value in a separate table from the passwords. To obtain an appropriate salt (random) value in .NET you can use the RNGCryptoServiceProvider class.

Imports System.Security.Cryptography

...

Function GetSalt(ByVal saltSize As Integer) As String

    Dim buffer() As Byte = New Byte(saltSize) {}

    Dim rng As New RNGCryptoServiceProvider()

    rng.GetBytes(buffer)

    Return Convert.ToBase64String(buffer)

End Function

So now we can take the salt value and store that in a table called Salt which has a foreign key to our Login table. Then I can create a couple parameterized queries on my TableAdapters for Login and Salt. 

 

On the LoginTableAdapter we can add a parameterized query called GetLoginByUserNameAndPassword where we pass the UserName and the salted hashed password. The select statement returns a scalar value and we add it through the TableAdapter Query Configuration Wizard just like I showed in my previous post.

SELECT COUNT(*) FROM Login WHERE UserName = @UserName AND Password = @Password

In order to pass the correct value for the @Password parameter, we need the salt value first. On the SaltTableAdapter we can add a parameterized query that returns the salt value for a given UserName called GetSaltByUserName.

SELECT TOP (1) Salt.Salt FROM Salt INNER JOIN Login ON Salt.LoginID = Login.LoginID WHERE        (Login.UserName = @UserName)

To make it easier to access the hashing functions we can create a module called PasswordCrypto:

Imports System.Security.Cryptography
Imports System.Text

 

Module PasswordCrypto

 

    Private Hasher As New SHA1CryptoServiceProvider()

 

    Friend Function GetSalt(ByVal saltSize As Integer) As String

        Dim buffer() As Byte = New Byte(saltSize) {}

        Dim rng As New RNGCryptoServiceProvider()

        rng.GetBytes(buffer)

        Return Convert.ToBase64String(buffer)

    End Function

 

    Friend Function HashEncryptString(ByVal s As String) As String

        Dim clearBytes As Byte() = Encoding.UTF8.GetBytes(s)

        Dim hashedBytes As Byte() = Hasher.ComputeHash(clearBytes)

        Return Convert.ToBase64String(hashedBytes)

    End Function

 

    Friend Function HashEncryptStringWithSalt(ByVal s As String, _

           ByVal salt As String) As String

        Return HashEncryptString(salt + s)

    End Function

 

End Module

Now that we have our hashing code and our TableAdapters configured, taking our Login form we can add code like this to verify whether a user's entered password matches the hashed password in the Login table:

Try

  Dim isOK As Boolean = False

  'Get the salt value for this username

  Dim saltValue As Object = _

  Me.SaltTableAdapter1.GetSaltByUserName(Me.txtUserName.Text)

 

  If Not IsDBNull(saltValue) Then

    'Hash the user entered password with the salt value stored in the Salt table

    Dim password As String = _
    PasswordCrypto.HashEncryptStringWithSalt(Me.txtPassword.Text, saltValue.ToString)

 

    'Now check the Login table to see if this hashed password matches

    isOK = CType(Me.LoginTableAdapter1.GetLoginByUserNameAndPassword( _
           Me.txtUserName.Text, password), Integer) = 1

  End If

 

  If isOK Then

    MsgBox("Welcome to my Application!")

  Else

    MsgBox("Invalid user name or password.")

  End If

 

Catch ex As Exception

  MsgBox(ex.ToString)

End Try

So this is how we can store passwords in a secure way in our database, even if our database does not support encrypted columns. With .NET, accessing hashing algorithms is a snap. I've attached a complete sample that demonstrates these techniques (as well as saving users passwords and salts) so that you can learn from them. You'll need Visual Studio or Visual Basic Express and SQL-Server Express installed to compile and run the sample.

Enjoy! And be secure!

Attachment: LoginClient.zip
Leave a Comment
  • Please add 6 and 7 and type the answer here:
  • Post
  • Beth, fantastic post!!

    Speaking of Security Engineering, I think worth to mention MS patterns & practices Security Engineering Index

    http://msdn.com/SecurityEngineering

    Thanks

    alikl

  • A few comments:

    1. Would it not be better to use the users ID instead of their name as people can be renamed in active directory (i.e. getting married).

    2. SHA1 is a weak encryption and fairly easy to crack now with using a rainbow table attack (http://blogs.solidhouse.com/david.woods/PermaLink,guid,6061116e-9570-4f79-9247-692bbe81c894.aspx).

    It is better to use SHA256 or SHA512 which are much harder to crack through.

    3. Storing the salt in another table does not give much in the way of security that I can see. A technique I have used is to not have a separate salt column but to hash the users phone number (or other user data) as the salt value

    Hasher.ComputeHash(password + Hasher.ComputeHash(phoneNumber))

    This really obfuscates what you are doing to an attacker.

  • Hi Dave! Hope you're doing well :-)

    Re 1) Storing and ID or a name doesn't matter as long as it's unique and the user can remember it for when they log in.

    Re 2) SHA-1 is good enough for this scenario becasue we are adding a random salt. You can make the salt as large as you want and instead off prepending the value you can inject the values into the original clear text password instead if you want, as long as you know how to do it again next time you compare.

    Re 3) It's better to separate the salt value and the password into separate tables that way if your login table is hijacked then your attacker would have to also hijack the salt values -- this way he may not know you even salted the hashes. I can only see your technique working if you never allowed the phone number to be changed. You can definately create a salt from another column in the database but you still need to store the salt value somewhere.

    Cheers,

    -B

  • Things are great! Hope the same for you.

    Re 1) If the username changes then the username must be changed in both AD and your application (so more of an administrative headache for this point)

    Re 2) Rainbow tables can be computed with salts in them as well so adding a salt is not necessarily adding security (i.e. if I did a table lookup and found a password of kick32%7% I would assume the password is kick and the rest of the information is the salt) . Granted this is moving towards a fairly far reaching example as a large enough salt (as you show here) would make it impractical to crack with a table but still is a possibility as hard drive and processor speeds increase. Using SHA256 or SHA512  creates larger hashes (which should also be salted) for which I have never even seen tables for.

    Also SHA1 has been shown to have collision issues (two words leading to the same hash) which makes it faster to break. Using a salt does defeat this though (but just another reason I avoid SHA1 myself)

    Re 3) Good point on changing data. For what I was doing it was unchanging user related data I was using for a salt (so phone number was a really bad example). I can see that storing the salt in another table adds a little bit of obscurity to the password storage mechanism and even a little bit could throw off an attacker so I will take it :D

  • Hi Dave,

    Re 2) All the more reason to have a strong password from the begining. You can have Windows enforce this and it would be a good idea to have your application do this as well if you took on storing passwords yourself. Regarding SHA-1, there are other providers in the namespace you could use as well. How about a sample? :-)

    Take Care!

    -B

  • thanks beth!!

  • bath is there any video of this article

    i am new and now confused

    so help me

    one thing i want to Open the NEXT Page if the User name and password is correct NOT a Text Box. Thanks In Advance

  • beth is there any video of this article

    i am new and now confused

    so help me

    one thing i want to Open the NEXT Page if the User name and password is correct NOT a Text Box. Thanks In Advance

  • I am trying to figure out how to write the SQL statement without using @. In VS.Net 2005 the@ is not accept. Is there another way to write the statements.

  • Hi Kay,

    What kind of database are you using? If it's Access then you'll need to use just a question mark in place of the variable name:

    SELECT Fields FROM MyTable WHERE Field1 = ?

    HTH,

    -B

  • Hi Beth,

    This tutorial is great!  The only thing I am having trouble with is updating the table adapters.  Salt and Login.  I have replicated the examples from the downloaded source and find my self at a loss.  I get an exception:

    Column 'UserName' does not allow nulls. when ever I try to load the form.

    I would appreciate a little guidence.

    kind regards

    Simon

  • Hi Beth,

    Fantastic post! Thanks a ton!

    The only question I have is how did you insert the hashed passwords into the table? The hashing is done using a VB.NET procedure, so I'm guessing you have to have a VB.NET procedure for inserting passwords into the table - Is there a way to pre-populate the table without using the application?

    Thanks a ton!

    Paul

  • Hi Paul,

    Take a look at the attached sample, I believe it does this by simply updating through the dataset.

    HTH,

    -B

  • Hi Simon,

    Does the sample run for you? Sounds like your database allows nulls for the username but your typed DataTable does not.

    -B

  • Ok finally got back to this. I was going to include a sample but all you would have to change would be:

    Private Hasher As New SHA1CryptoServiceProvider()

    to

    Private Hasher As New SHA512Managed()

Page 1 of 2 (28 items) 12