How to return data specific to 1 user in an SQL database using VB.NET

Question

This program requires a user to select their card number from a combo box, then enter their password. If their password matches the one in the database, then they get logged in. Problem is, my code doesn't select one specific user, it only takes the first user in the database, I cannot log in any other user because I don't know how to write the code that chooses a specific user.

Here is what I've written so far. The problem I think is in the RetriveAccountInformation() function. Somewhere in there I need code that specifies it retrieves the data of the CardNumber selected in the cboAccountNumbers combo box. Any help is appreciated.

Imports MySql.Data

Imports MySql.Data.MySqlClient

Public Class Form1

Dim dbCon As MySqlConnection
Dim strQuery As String = ""
Dim SQLcmd As MySqlCommand
Dim DataReader As MySqlDataReader

Private m_strPass As String
Private m_decBalance As Decimal
Private m_strName As String
Private m_strUserPass As String
Private m_strCardNumber As String



Private Sub Button2_Click(sender As Object, e As EventArgs) Handles btnLogin.Click

    'Assign users guessed password to variable
    m_strUserPass = txtPass.Text

    RetrieveAccountInformation() ' invoke 


    ' determine if PIN number is within valid range
    If m_strUserPass = m_strPass Then
        lblWelcome.Text = "Hi"


    Else

        ' indicate that incorrect password was provided
        lblWelcome.Text = "Sorry, Password the is incorrect." _
           & "Please re-enter the password ."

        ' clear user's previous PIN entry
        m_strUserPass = ""

    End If

    txtPass.Clear() ' clear TextBox

End Sub


' load application Form
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

    'Prepare connection and query
    Try
        dbCon = New MySqlConnection("Server=localhost;Database=test;Uid=root;Pwd=mysql")

        strQuery = "SELECT CardNumber " &
                   "FROM Account"

        SQLcmd = New MySqlCommand(strQuery, dbCon)

        'Open the connection
        dbCon.Open()

        ' create database reader to read information from database
        DataReader = SQLcmd.ExecuteReader

        ' fill ComboBox with account numbers
        While DataReader.Read
            cboAccountNumbers.Items.Add(DataReader("CardNumber"))
        End While

        'Close the connection
        DataReader.Close()
        dbCon.Close()

    Catch ex As Exception
        'Output error message to user with explaination of error
        MsgBox("Failure to communicate" & vbCrLf & vbCrLf & ex.Message)


    End Try
End Sub


' invoke when user provides account number
Private Sub RetrieveAccountInformation()

    ' specify account number of record from which data
    ' will be retrieved
    dbCon = New MySqlConnection("Server=localhost;Database=***;Uid=***;Pwd=***")

    strQuery = "SELECT Password, Name, Balance " &
               "FROM Account"

    SQLcmd = New MySqlCommand(strQuery, dbCon)

    dbCon.Open() ' open database connection

    ' create database reader to read information from database
    DataReader = SQLcmd.ExecuteReader

    DataReader.Read() ' open data reader connection

    ' retrieve PIN number, balance amount and first name
    ' information from database
    m_strPass = Convert.ToString(DataReader("Password"))
    m_decBalance = Convert.ToDecimal(DataReader("Balance"))
    m_strName = Convert.ToString(DataReader("Name"))


    DataReader.Close() ' close data reader connection
    dbCon.Close() ' close database connection
End Sub ' RetrieveAccountInformation

End Class

Answer 1

You need to use a WHERE clause in your sql statement to retrieve the info about a particular user.
By the way, just using the password as key to retrieve your info is not enough (what blocks two users to have the same password?) So I suppose you pass to your RetrieveAccountInformation both the Password value and the CardNumber and use these values to uniquely identify a user record in the Account.

Private Function RetrieveAccountInformation(cardNum as String, pwd As String) As Boolean

    Dim result as Boolean
    strQuery = "SELECT Password, Name, Balance FROM Account " & _
               "WHERE Password = @pwd AND CardNumber=@card"

    Using dbCon = New MySqlConnection("Server=localhost;Database=***;Uid=***;Pwd=***")
    Using SQLcmd = New MySqlCommand(strQuery, dbCon)
        dbCon.Open() 
        SQLcmd.Parameters.AddWithValue("@pwd", pwd)
        SQLcmd.Parameters.AddWithValue("@card", cardNum)
        Using DataReader = SQLcmd.ExecuteReader

            if DataReader.Read() then
                m_strPass = Convert.ToString(DataReader("Password"))
                m_decBalance = Convert.ToDecimal(DataReader("Balance"))
                m_strName = Convert.ToString(DataReader("Name"))
                result = true
            else
                result = false
            End If
        End Using
    End Using
    End Using
End Function

The revised function could be called from the first button click in this way

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles btnLogin.Click
    Dim result As Boolean 
    if cboAccountNumbers.SelectedValue Is Nothing Then
         MessageBox.Show("Please select a Card Number")
    End If

    result = RetrieveAccountInformation(cboAccountNumbers.SelectedValue.ToString, txtPass.Text) 

    If result = True Then
        lblWelcome.Text = "Hi"
    Else
        ....

I have also changed your Sub to a Boolean Function that returns true if you have found the user or false if not. As a last note, keep always in mind that passing password in clear text in a database command is not a good practice from the security standpoint. There is a large consensus that the most secure way is to use an Hashing function when storing the password and then apply the same hash to the clear text of your password before passing the value along the wire.