Btukfyl

I'm trying to make a login form.

I've created a database on my server and created the rows username and password.
I then created a root user with root as password.

but I have a problem with the check if the username and password are correct,
I don't know how to give him the 2 rows.

Dim conn = New SqlConnection("Data Source=SRV-SQL;Initial Catalog=prova;User ID=user;Password=user")

Dim sda = New SqlDataAdapter("select count(*) from tblLogin where username ='" + txtUsername.Text + "' and password='" + txtUserPwd.Text + "'", conn)

Dim dt = New DataTable()

sda.Fill(dt)

If (dt.Rows().ToString() = "1") Then

    MsgBox("Logged-in successfully")

Else

    MessageBox.Show("The username or the password is wrong!", "Warning!", MessageBoxButtons.OK, MessageBoxIcon.Error)

End If

Table:

Comments and explanations in-line.

Private Sub VerifyLogin()

        'For the Return Value of the command

        Dim RetVal As Integer

        ' A Using...End Using will ensure that you connectionis closed and disposed event

        'it there is an error.

        Using conn = New SqlConnection("Data Source=SRV-SQL;Initial Catalog=prova;User ID=user;Password=user")

            'You don't need a DataAdapter, just a command

            'USE PARAMETERS. Yes, I am yelling :-) Even if you are the only user

            'it will save you headaches with syntax.

            Using cmd = New SqlCommand("select count(*) from tblLogin where username = @UserName and password= @Password;", conn)

                cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = txtUsername.Text

                cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value = txtUserPwd.Text

                'You are only returning one row

                'ExecuteScalar returns the value in the first column of the 

                'first row of the the data

                conn.Open()

                RetVal = CInt(cmd.ExecuteScalar)

            End Using

        End Using

        'No need to convert to a string just compare the Integer

        If RetVal = 1 Then

            MsgBox("Logged-in successfully")

        Else

            MessageBox.Show("The username or the password is wrong!", "Warning!", MessageBoxButtons.OK, MessageBoxIcon.Error)

        End If

End Sub

Private Function CalculateHash(password As String, salt As String) As String

    'TODO:

    ' Suggest pulling the BCrypt from the NuGet gallery for this:

    ' https://www.nuget.org/packages/BCrypt-Official/

    ' Just remember that bcyrpt lib encodes salt as part of the password hash, so the function signatures and db table will be different.

End Function



Public Function CheckCredentials(UserName As String, Password As String) As Boolean

    Using conn As New SqlConnection("Data Source=SRV-SQL;Initial Catalog=prova;User ID=user;Password=user"), _

          ' Need to add a "Salt" column to your table, create a new random salt for each user when you create the user

          cmd As New SqlCommand("SELECT Salt, PwdHash FROM tblLogin WHERE username = @Username", conn)



        'Parameterized queries or NOTHING. String concatention is NOT OKAY here

        cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = UserName



        conn.Open()   

        Using rdr As SqlDataReader = cmd.ExecuteReader()

            If Not rdr.Read() Then Return False



            Dim Salt As String = rdr("Salt")

            Dim PwdHash As String = rdr("PwdHash")



            'Compare HASHES, not Passwords

            Return PwdHash = CalculateHash(Password, Salt As String)

        End Using

    End Using

End Function



If CheckCredentials(txtUsername.Text, txtUserPwd.Text) Then

    MsgBox("Logged-in successfully")

Else

    MessageBox.Show("The username or the password is wrong!", "Warning!", MessageBoxButtons.OK, MessageBoxIcon.Error)

End If

Use DataReader instead, use this code and just call CheckLogin in login button or somthing else.

Sub CheckLogin()



    Dim conn = New SqlConnection("Data Source=SRV-SQL;Initial Catalog=prova;User ID=user;Password=user")



    conn.Open()



    Try



        Dim query As String = "select count(*) from tblLogin where username = @username and password= @password "

        Dim cmd = New SqlCommand(query, conn)

        cmd.Parameters.AddWithValue("@username", txtUsername.Text)

        cmd.Parameters.AddWithValue("@password", txtUserPwd.Text)

        Dim DR As SqlDataReader = cmd.ExecuteReader()



        If DR.HasRows Then



            MsgBox("Logged-in successfully")



        Else



            MessageBox.Show("The username or the password is wrong!", "Warning!", MessageBoxButtons.OK, MessageBoxIcon.Error)



        End If



    Catch ex As Exception

        MsgBox(ex.Message)

    End Try



    conn.Close()



End Sub