database - 有人可以检查我的 VB.net 代码吗？问题解释如下

Question

好的，我已经使用访问数据库在 VB.net 上创建了一个登录系统。我遇到的问题是一些用户名和密码组合工作得很好，但其中一些虽然输入正确，但根本不起作用。这是我写的代码...

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    ' Check if username or password is empty
    If textpassword.Text = "" Or textusername.Text = "" Then
        MessageBox.Show("Please complete the required fields..", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    Else
        ' Both fields were supplied
        ' Check if user exist in database
        ' Connect to DB
        Dim conn As New System.Data.OleDb.OleDbConnection()
        conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\database1.accdb"

        'conn.Open()
        'MsgBox("Susscess")

        Dim sql As String = "SELECT * FROM Accounts WHERE username='" & textusername.Text & "' AND password = '" & textpassword.Text & "'"
        Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)

        'Open Database Connection
        sqlCom.Connection = conn
        conn.Open()

        Dim sqlRead As System.Data.OleDb.OleDbDataReader = sqlCom.ExecuteReader()

        If sqlRead.Read() Then
            MemberPage.Show()
            Me.Hide()

        Else
            ' If user enter wrong username and password combination
            ' Throw an error message
            MessageBox.Show("Username and Password do not match..", "Authentication Failure", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

            'Clear all fields
            textpassword.Text = ""
            textusername.Text = ""

            'Focus on Username field
            textusername.Focus()
        End If 
    End If 
End Sub

Answer 1

不要连接字符串。它对 SQL 注入(inject)有很大的开放性。最好使用参数化查询

Dim sql As String = "SELECT * FROM Accounts WHERE username=? AND password = ?"
Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)
sqlCom.Parameters.AddWithValue("?", textusername.Text);
sqlCom.Parameters.AddWithValue("?", textpassword.Text);

你也可以使用 HasRows 属性

If sqlRead.HasRows Then
      While sqlRead.Read() 
       MemberPage.Show()
       Me.Hide()
      End While
 Else
     MessageBox.Show("Username and Password do not match..", "Authentication Failure", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) 'Clear all fields
        textpassword.Text = ""
        textusername.Text = "" 
      'Focus on Username field
        textusername.Focus()
 End If