SQL if statemnt in visual basic

Question

Hi Guys,

I'm trying to display different forms based on the value in the usertype field in the DB but I can't get it to work. I presume it's a problem with calling the table but I don't know where I'm going wrong.

Any help would be appreciated.

Thanks

Public Class Login

    Private Sub BtnLogin_Click(sender As Object, e As EventArgs) Handles BtnLogin.Click

        Dim con As SqlConnection = New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\pdroz\Desktop\NewTrialApp\NewTrialApp\PiotrsAppDB.mdf;Integrated Security=True")
        Dim cmd As SqlCommand = New SqlCommand("select * from LoginTable where username='" & Employeetxt.Text & "'and password='" & Password.Text & "'", con)
        Dim sda As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim dt As DataTable = New DataTable()
        sda.Fill(dt)
        If (dt.Rows.Count > 0) Then
            MessageBox.Show("You are logged in as " + dt.Rows(0)(2))
            If (cmdusertype.value = "user") Then
                Dim u As New user
                u.Show()
                Me.Hide()

            ElseIf
                (cmdusertype.value = "admin")
                Dim a As New admin
                a.Show()
                Me.Hide()

            Else
                Dim m As New manager
                m.Show()
                Me.Hide()


            End If


        Else
            MessageBox.Show("Error")
        End If


    End Sub
End Class

Answer 1

Probably you must add something like ‘Dim usertype As String = dt.Rows(0)(3).ToString’, with correct index instead of 3, then use usertype in next Ifs instead of cmdusertype.

By the way, because you are not using Parameterised Queries, an attacker can use your program without knowing the password.

Answer 2

Although @Viorel helped solve your immediate issue, this code has another serious that need's to be addressed:

Dim cmd As SqlCommand = New SqlCommand("select * from LoginTable where username='" & Employeetxt.Text & "'and password='" & Password.Text & "'", con)  

One should use parameters for values that vary by execution. Never build SQL statement strings with literals for these values, especially from untrusted sources like user input. Here's an example of a correctly parameterized query.

Dim cmd As SqlCommand = New SqlCommand("select * from LoginTable where username=@username and password=@password", con)  
cmd.Parameters.Add(@username,SqlDbType.VarChar, 50).Value = Employeetxt.Text  
cmd.Parameters.Add(@password,SqlDbType.VarChar, 50).Value = Password.Text  

Parameterized queries have many benefits , including:

• improve security by preventing injection of unwanted SQL (barring non-parameterized server-side dynamic SQL)
• eliminate need to enclose literals in quotes (or not) depending on data type
• eliminate need prefix Unicode literal strings with N
• allow single quotes within strings without the need to escape them
• avoid the need to format date/time/datetime string literals in a particular way (which vary by culture)
• do not require decimal separators (which vary by culture)
• improve performance by reducing compilation costs and promoting execution plan cache reuse
• allow WHERE clause predicates with Always Encrypted columns
• code that’s cleaner and easier to maintain

Another concern here is that storing clear passwords in a database used to authenticate users should be avoided for security reasons. Instead, store and validate passwords using a salted hash so password credentials cannot be retrieved even if data are compromised.