Login Form update query

[alexcarter404]

Hi All,
I am developing a windows form application but am struggling with running an update query when the user logs in,
I basically want the form to validate the user which it does but then i want it to update a field in the users table of an sql database.
The problem is it validates everything ok but it does not run the update query, i check the database table after i run the log in form and it does not update the logged_in field to true, it remains false.
I have
This is the code:highlighted the update query in red.

     Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        Dim connection As New SqlClient.SqlConnection
        Dim command As New SqlClient.SqlCommand
        Dim adapter As New SqlClient.SqlDataAdapter
        Dim dataset As New DataSet

        connection.ConnectionString = ("Data Source=ALEX-LAPTOP\SQLEXPRESS;Initial Catalog=sldb;Integrated Security=True;Pooling=False")
        command.CommandText = "SELECT * FROM [Users] WHERE username='" & txtUsername.Text & "' AND password='" & txtPassword.Text & "';"
        connection.Open()
        command.Connection = connection
        adapter.SelectCommand = command
        adapter.Fill(dataset, "0")
        Dim count = dataset.Tables(0).Rows.Count
        If count > 0 Then
            command.CommandText = "UPDATE Users SET logged_in=True WHERE username='" & Me.txtUsername.Text & ";"
            MsgBox("Login Successful", MsgBoxStyle.Information, "Login Passed")
            currentuser = Me.txtUsername.Text
            connection.Close()
            Me.txtUsername.Clear()
            Me.txtPassword.Clear()
            Me.Hide()
            frmHome.Show()
        Else
            MsgBox("Invalid Account", MsgBoxStyle.Critical, "Login Failed")
            txtUsername.Clear()
            txtPassword.Clear()
        End If
    End Sub

Any help would be greatly appreciated, many thanks in advance!

[jmcilhinney]

First up, using a DataSet to authenticate a user is overkill. You're not using the data you retrieve so don't retrieve it. You don't care what the matching data is, only whether or not there is matching data, so that's what your query should reflect. For a better way, follow the CodeBank link in my signature and check out my thread on Validating User Credentials.

As for the issue you stated, SQL statements don't just execute themselves. You have a SqlCommand and you have set it's CommandText to tell it what SQL code to execute, but at no point have you told it to execute that SQL. You really shouldn't be reusing the same command in that case. I strongly recommend that you create a new command object for that distinctly new usage. To learn how to execute an UPDATE statement like that, follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data.

I also strongly recommend that you follow the Blog link in my signature and check out my post on Parameters In ADO.NET to learn the why and how of the proper way to insert values into SQL code.

Finally, you appear to hiding your login form to show your main form. That is bad, bad, bad. To learn a better way to display a login form before your main form, follow the CodeBank link in my signature and check out my thread on a WinForms Login.

[Maverickz]

As jmc has already pointed out your code is not working because you never executed the Update command.

He has also given lots of good advice but I can't help but feel he left out probably the most important one of all. It is VERY bad practice to store user passwords as clear text in a DB. The best way to do it, is to use a ONE WAY hash algorithm to convert the password into a unique hash value and store that in the password field in the DB. When the user logs in, get a hash value for what he entered and then compare that to the hash value in the DB. This does require a slightly more elaborate password reset process, but prevents anyone that gains access to the DB from having the login credentials for every user.

[alexcarter404]

Hi,
Thanks for the replies, i don't suppose you could suggest some alterations to my code as i am quite new to visual basic and stuff so i am not too sure on how to do what you have said. The hash thing i am guessing i have to use some sort of SHA-256 hash function on the login input and compare it to an already hashed password? But for the update query i am not 100% on how to edit the code.

Sorry for being lazy but i just dont have a clue!

[jmcilhinney]

We've pointed out various issues so you should tackle them one by one. You probably want to tackle the issue you raised originally first. In my previous post I said:

As for the issue you stated, SQL statements don't just execute themselves. You have a SqlCommand and you have set it's CommandText to tell it what SQL code to execute, but at no point have you told it to execute that SQL. You really shouldn't be reusing the same command in that case. I strongly recommend that you create a new command object for that distinctly new usage. To learn how to execute an UPDATE statement like that, follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data.

So, I told you where to find the information you need to fix that issue. Have you followed those instructions? If not then you in fact do have a clue but you just haven't bothered to use it. If you have followed those instructions and you don't understand then what is the problem? The examples in the CodeBank thread I suggested each have a title that indicates the type of scenario that they demonstrate. Have you read those titles at least? There are only a very small number. As a starting point, which of those do you think corresponds to your situation? Have you thought about that?

[Maverickz]

The hash thing i am guessing i have to use some sort of SHA-256 hash function on the login input and compare it to an already hashed password? But for the update query i am not 100% on how to edit the code.

Yes, one of the SHA algorithms would work fine. As to if you need SHA1, SHA-256, SHA-384, or SHA-512. The minimum level that you need will depend on what you are securing. For basic logins, such as this website, SHA-1 is probably fine. If you are securing personally identifiable customer data (name, address, telephone, etc.), then 256 might be a minimum. If you are securing financial data, credit card data, Social Security numbers, corporate classified data, etc. then 384 or 512 is probably preferred. The more valuable the information to a potential hacker the more secure you want to be. If in doubt the more secure is safer.

[alexcarter404]

Thanks for the advice everyone and sorry for being lazy! i have had a look through some of the code banks and edited my code.
I have updated the login process to not use a dataset which works fine but i am having trouble running the update query still! I will implement the hash algorithm at a later date as i have not made a registration form to hash inputted data yet which means i cannot yet compare to a hash value.

The problem i am having is that i get an error of sqlexception was unhandled - invalid column name true. This is when running the update query section of the code

       Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        'create connection string and connect to db
        Dim conn As SqlClient.SqlConnection
        conn = New SqlClient.SqlConnection
        conn.ConnectionString = ("Data Source=ALEX-LAPTOP\SQLEXPRESS;Initial Catalog=sldb;Integrated Security=True;Pooling=False")
        Try
            conn.Open()
        Catch myerror As Exception
            MessageBox.Show("Error Connecting to Database: " & myerror.Message)
        End Try

        'create query, execute and put into reader
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim sqlquery = "SELECT username, password FROM Users Where username='" & txtUsername.Text & "' and password='" & txtPassword.Text & "'"
        Dim myCommand As New SqlClient.SqlCommand
        myCommand.Connection = conn
        myCommand.CommandText = sqlquery
        myAdapter.SelectCommand = myCommand
        Dim myData As SqlClient.SqlDataReader
        myData = myCommand.ExecuteReader()
        'check for correct credentials
        If myData.HasRows = 0 Then
            MessageBox.Show("Invalid Login Details", "Login Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            currentuser = Me.txtUsername.Text

            'run update query to update logged_in field to true
            conn = New SqlClient.SqlConnection
            conn.ConnectionString = ("Data Source=ALEX-LAPTOP\SQLEXPRESS;Initial Catalog=sldb;Integrated Security=True;Pooling=False")
            conn.Open()
            Dim sqlquery2 = "UPDATE Users SET logged_in=True WHERE username='" & txtUsername.Text & "' and password='" & txtPassword.Text & "'"
            Dim mycommand2 As New SqlClient.SqlCommand(sqlquery2, conn)
            mycommand2.ExecuteNonQuery()

            Dim homeform = New frmHome
            homeform.Show()
            Me.Visible = False
        End If
    End Sub

Am i running the update query correctly? I know i need to run it as an executenoquery but for some reason it doesn't like the update query. Any Suggestions?

[alexcarter404]

I have also done another edit, this seems to run OK but it still does not execute the update query
I have spent almost a week on this and cannot for the life of me get it to work!! please help me!

 Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        'create connection string and connect to db
        Dim conn As SqlClient.SqlConnection
        conn = New SqlClient.SqlConnection
        conn.ConnectionString = ("Data Source=ALEX-LAPTOP\SQLEXPRESS;Initial Catalog=sldb;Integrated Security=True;Pooling=False")
        Try
            conn.Open()
        Catch myerror As Exception
            MessageBox.Show("Error Connecting to Database: " & myerror.Message)
        End Try

        'create query, execute and put into reader
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim sqlquery = "SELECT username, password FROM Users Where username='" & txtUsername.Text & "' and password='" & txtPassword.Text & "'"
        Dim myCommand As New SqlClient.SqlCommand
        myCommand.Connection = conn
        myCommand.CommandText = sqlquery
        myAdapter.SelectCommand = myCommand
        Dim myData As SqlClient.SqlDataReader
        myData = myCommand.ExecuteReader()
        'check for correct credentials
        If myData.HasRows = 0 Then
            MessageBox.Show("Invalid Login Details", "Login Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            currentuser = Me.txtUsername.Text

            'run update query to update logged_in field to true
            conn.Close()
            Dim myAdapter2 As New SqlClient.SqlDataAdapter
            Dim sqlquery2 = "UPDATE Users SET logged_in=True WHERE username='" & txtUsername.Text & "' and password='" & txtPassword.Text & "'"
            Dim myCommand2 As New SqlClient.SqlCommand
            myCommand2.Connection = conn
            myCommand2.CommandText = sqlquery2
            myAdapter.UpdateCommand = myCommand2
            conn.Open()
            myCommand.ExecuteNonQuery()
            conn.Close()
            Dim homeform = New frmHome
            homeform.Show()
            Me.Visible = False
        End If
    End Sub

[m.davide]

the second myCommand.ExecuteNonQuery(), change it to myCommand2.ExecuteNonQuery() to execute update statement.

[jmcilhinney]

I think that this is a perfect example of why you should always give everything a meaningful name. If you use names like myCommand and myCommand2 then it's not obvious from simply reading the code that you're executing the wrong one. If your commands had names like retrieveCommand and updateCommand or perhaps even better than that then it would be obvious whether you're executing the correct one or not.

[alexcarter404]

Hi All,
Thanks for all the help the below is my final mark up of the code minus the hash function on the password data,

  Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        'create connection string and connect to db
        Dim conn As SqlClient.SqlConnection
        conn = New SqlClient.SqlConnection
        conn.ConnectionString = ("Data Source=ALEX-LAPTOP\SQLEXPRESS;Initial Catalog=sldb;Integrated Security=True;Pooling=False")
        Try
            conn.Open()
        Catch myerror As Exception
            MessageBox.Show("Error Connecting to Database: " & myerror.Message)
        End Try

        'create query, execute and put into reader
        Dim selectAdapter As New SqlClient.SqlDataAdapter
        Dim sqlselectquery = "SELECT username, password FROM Users Where username='" & txtUsername.Text & "' and password='" & txtPassword.Text & "'"
        Dim selectCommand As New SqlClient.SqlCommand
        selectCommand.Connection = conn
        selectCommand.CommandText = sqlselectquery
        selectAdapter.SelectCommand = selectCommand
        Dim selectData As SqlClient.SqlDataReader
        selectData = selectCommand.ExecuteReader()
        'check for correct credentials
        If selectData.HasRows = 0 Then
            MessageBox.Show("Invalid Login Details", "Login Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            currentuser = Me.txtUsername.Text

            'run update query to update logged_in field to true
            conn.Close()
            Dim updateAdapter As New SqlClient.SqlDataAdapter
            Dim sqlupdatequery = "UPDATE Users SET logged_in='True' WHERE username='" & txtUsername.Text & "' and password='" & txtPassword.Text & "'"
            Dim updatecommand As New SqlClient.SqlCommand
            updatecommand.Connection = conn
            updatecommand.CommandText = sqlupdatequery
            updateAdapter.UpdateCommand = updatecommand
            conn.Open()
            updatecommand.ExecuteNonQuery()
            conn.Close()
            Dim homeform = New frmHome
            homeform.Show()
            Me.Visible = False
        End If
    End Sub

This works perfectly and i have also modified the select query to work on the home form to decide whether the user is an admin or not and display the admin button.

For the hash function should i have a registration form which hashes the user's password when it is added to the database? Is this enough or do i also need to hash the username aswell? Also as for the data stored in the database other than the authentication data, should that also be hashed or is it OK to just display it as text? This would be people's personal details and information on when they took leave in their work place.

Thanks!
Alex

[Maverickz]

For the hash function should i have a registration form which hashes the user's password when it is added to the database? Is this enough or do i also need to hash the username aswell? Also as for the data stored in the database other than the authentication data, should that also be hashed or is it OK to just display it as text? This would be people's personal details and information on when they took leave in their work place.

Thanks!
Alex

Only hash the password. Remember a hash is like one way encryption. Once it is hashed you cannot unhash it to get the original values. This is why you hash the entered password at login and compare the hashes instead of unhashing the stored password. For sensitive data that you need to be able to retrieve the original values for, such as credit card or social security numbers, you should use two way encryption instead of a hash.