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 3 and type the answer here:
  • Post
  • Hello Beth,

    Enjoying your blog.  I'm using Access and I'm not getting a salt back from the query when I test it.  I had to remove the SELECT TOP (1).  It didn't seem to like it. What do I do next?

  • You have the GetSalt function but it isn't being called from anywhere that I can see.  Where did I go wrong?  Thanks

  • Hello Beth

    Thanks very much for this. Great stuff!

  • Thanks Beth, Very nicely done and explained.

  • very confusing, only a beginner looking for an easier way

  • Hi,

    If I want to create the login form as the startup form, how to i create an account in my Login database?

  • Hi Jason,

    Take a look at the attached sample at the end of this post. It shows a technique on how to add users and secure passwords to the database.

    HTH,

    -B

  • 'usersTableAdapter' is not a member of 'System.log'.

    this message appear for me?!!

    what i must do?

    my code like this

    ______________

    Imports system.Security.Principal

    Public Class log

       Private Sub log_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

           Dim user As WindowsIdentity = WindowsIdentity.GetCurrent()

           Dim username As String = user.Name

           If CType(Me.usersTableAdapter.GetLoginByUserName(username), Integer) = 1 Then

               MsgBox("Welcome to my application!")

           Else

               MsgBox("Invalid username or password.")

           End If

       End Sub

    End Class

    ________________

  • 'usersTableAdapter' is not a member of 'System.log'.

    this message appear for me?!!

    what i must do?

    my code like this

    ______________

    Imports system.Security.Principal

    Public Class log

       Private Sub log_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

           Dim user As WindowsIdentity = WindowsIdentity.GetCurrent()

           Dim username As String = user.Name

           If CType(Me.usersTableAdapter.GetLoginByUserName(username), Integer) = 1 Then

               MsgBox("Welcome to my application!")

           Else

               MsgBox("Invalid username or password.")

           End If

       End Sub

    End Class

    ________________

  • I get the following error:

    Null Reference Exception Occured.  Object reference not set to an instance of an object.

    Which relates to:

    Dim password As String = _

                   PasswordCrypto.HashEncryptStringWithSalt(Me.PasswordTextBox.Text, saltValue.ToString)

    Any thoughts?

  • Hi Beth!

    SELECT        TOP(1) Salt.Salt

    FROM           Salt INNER JOIN

                         Login ON Salt.LoginID = Login.LoginID

    WHERE        (Login.UserName = @UserName)

    is not working, I'm getting this Wizard error result:

    The wizard detected the following problems when configuring TableAdapter query "SALT":

    Details:

    Generated SELECT statement:

    Error in list of function arguments: '.' not recognized

    Error in list of function arguments: ')' not recognized

    Unable to parse query text

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

    What should I do?

    Thanks much!

  • how to create a secure login form in visual studio for the window application. i have used login form but in dont know how to make the login form to be secure with appropiate username and password.

  • Well thanks a lot for the method shown in here.

    I have only used the HashEncryptString(s) method and it is successfully implemented.

    But what if we want to retrieve the password?

    Thank you

    IndyaKing

Page 2 of 2 (28 items) 12