试图验证用户名（电子邮件地址）和密码 - 有问题 - vb.net

Question

我有一个Windows窗体应用程序没有正确验证用户输入信息。需要一些帮助。 我插入了Microsoft登录表单并正在编写代码以验证用户凭据。使用Access数据库来存储和检索信息。两个表 - 一个用于电子邮件地址，另一个用于密码。
我使用正则表达式来验证电子邮件地址的格式。这工作得很好。 我验证电子邮件地址的格式是否正确，并验证它是否在表格中（这很有效）。然后我尝试读取密码（看起来不像预期的那样工作），然后从表中读取两位信息。接下来，我测试以确保两者都存在。如果两者都存在，控制权将传递给另一种形式。

我的问题是阅读/验证密码。

这是我的Visual Studio VB.net代码。

Private Sub OK_Click(sender As System.Object, e As System.EventArgs) Handles OK.Click 

    Try 

     If MsgBox("Is your information correct?", MsgBoxStyle.YesNo, "M&P Records") = MsgBoxResult.Yes Then 

      Dim pattern As String = "^[A-Z][A-Z|0-9|]*[a-z][a-z|0-9|]*([_][a-z|0-9]+)*([.][a-z|0-9]+([_][a-z|0-9]+)*)[email protected][a-z][a-z|0-9|]*\.([a-z][a-z|0-9]*(\.[a-z][a-z|0-9]*)?)$" 

      Dim match As System.Text.RegularExpressions.Match = Regex.Match(txtUsername.Text.Trim(), pattern, RegexOptions.IgnoreCase) 
      If (match.Success) Then 


       Try 
        If i = 0 Then 
         provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" 

         'Change the following to your access database location 
         dataFile = "\11_2017_Spring\CSCI-2999_Capstone\DB_M&PRecords.accdb" 

         connString = provider & dataFile 
         myConnection.ConnectionString = connString 
         myConnection.Open() 
         i = 1 
        End If 

       Catch ex As Exception 
        ' An error occured! Show the error to the user and then exit. 
        MessageBox.Show(ex.Message) 
       End Try 


       'the query: 

       Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [EmailAddress] WHERE [emailAddress] = '" & txtUsername.Text & "'", myConnection) 
       Dim com As OleDbCommand = New OleDbCommand("SELECT * FROM [Password] WHERE [Password] = '" & txtPassword.Text & "'", myConnection2) 

       Dim dr As OleDbDataReader = cmd.ExecuteReader() 
       Dim drp As OleDbDataReader = com.ExecuteReader() 

       ' the following variable is hold true if EmailAddress is found, and false if EmailAddress is not found 
       Dim userFound As Boolean = False 

       ' the following variable is hold true if Password is found, and false if Password is not found 
       Dim passwordFound As Boolean = False 

       ' the following variables will hold the EmailAddress and Password if found. 
       Dim EmailAddressText As String = "" 
       Dim PasswordText As String = "" 

       'if found: 
       While dr.Read() 
        userFound = True 
        EmailAddressText = dr("EmailAddress").ToString 
       End While 

       While drp.Read() 
        passwordFound = True 
        PasswordText = drp("Password").ToString 
       End While 

       'checking the result 
       If userFound = True And passwordFound = True Then 
        frmMain.Show() 
        frmMain.Label1.Text = "Welcome " & EmailAddressText & " " 
       Else 
        MsgBox("Sorry, username or password not found", MsgBoxStyle.OkOnly, "M&P Records - Invalid Login") 

        With txtPassword 
         .Clear() 
        End With 

        With txtUsername 
         .Clear() 
         .Focus() 
        End With 

       End If 

      Else 
       MessageBox.Show("Please enter a valid email address", "M&P Records - Email Check") 

       With txtPassword 
        .Clear() 
       End With 

       With txtUsername 
        .Clear() 
        .Focus() 
       End With 
      End If 

     End If 

    Catch ex As Exception 
     ' An error occured! Show the error to the user and then exit. 
     MessageBox.Show(ex.Message) 
    End Try 

End Sub 

Answer 1

那么首先你的做法是不是真的安全由于该密码是不加密的，要么有电子邮件地址和密码之间没有联系最好你将有表，例如：

USER --uid --email

PASS --id --uid --pass

你会散列您的密码，例如sha512为了更安全，您将使用Salt和证书来保护数据库连接。

然后，你可以这样做： 在文本框中散列当前密码并执行：

"SELECT USER.Email FROM USER,PASS WHERE USER.Email='TEXTBOX_EMAIL' AND USER.UID = PASS.UID" 

检查您是否已经导致，如果是你的连接。

但是，我试图纠正一下你在上面的代码中做了什么。已经只用SQLCLIENT而不是Olecommand我试图让你没那么有可能是一些什么语法错误，但应该没问题：

Try 
    If MsgBox("Is your information correct?", MsgBoxStyle.YesNo, "M&P Records") = MsgBoxResult.Yes Then 
     Dim pattern As String = "^[A-Z][A-Z|0-9|]*[a-z][a-z|0-9|]*([_][a-z|0-9]+)*([.][a-z|0-9]+([_][a-z|0-9]+)*)[email protected][a-z][a-z|0-9|]*\.([a-z][a-z|0-9]*(\.[a-z][a-z|0-9]*)?)$" 
     Dim match As System.Text.RegularExpressions.Match = Regex.Match(txtUsername.Text.Trim(), pattern, RegexOptions.IgnoreCase) 
     If (match.Success) Then 
      Dim passwordFound As Boolean 
      Dim userFound As Boolean 
      Using con As New SqlClient.SqlConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source =\ 11_2017_Spring\CSCI-2999_Capstone\DB_M&PRecords.accdb") 
       'Using to make sure connection is disposed 
       'Open connection 
       con.Open() 
       'Prepare sql 
       Dim command As New OleDbCommand("SELECT [emailAddress] FROM [EmailAddress] WHERE [emailAddress] = '" & txtUsername.Text & "';", con) 
       'Create the reader 
       Dim reader As OleDbDataReader = command.ExecuteReader() 
       Dim Id As String = "" 
       ' Call Read before accessing data. 
       While reader.Read() 
        'Get data 
        Id = reader(0) 
       End While 
       'Close Reader 
       reader.Close() 
       If Id <> "" Then 
        'User found 
        userFound = True 
        'Prepare the second sql 
        Dim command2 As New OleDbCommand("SELECT [Password] FROM [Password] WHERE [Password] = '" & txtPassword.Text & "';", con) 
        'Prepare second reader 
        Dim reader2 As OleDbDataReader = command.ExecuteReader() 
        Dim Pass As String = "" 
        ' Call Read before accessing data. 
        While reader2.Read() 
         'Get tdata 
         Pass = reader2(0) 
        End While 
        reader.Close() 
        If Pass <> "" Then 
         'Pass found 
         passwordFound = True 
        Else 
         passwordFound = False 
        End If 
       Else 
        userFound = False 
       End If 
       'Close connection 
       con.Close() 
       'Clear connection pool 
       SqlConnection.ClearPool(con) 
      End Using 
      'checking the result 
      If userFound = True And passwordFound = True Then 
       frmMain.Show() 
       frmMain.Label1.Text = "Welcome " & EmailAddressText & " " 
      Else 
       MsgBox("Sorry, username or password not found", MsgBoxStyle.OkOnly, "M&P Records - Invalid Login") 
       With txtPassword 
        .Clear() 
       End With 
       With txtUsername 
        .Clear() 
        .Focus() 
       End With 
      End If 
     Else 
      MessageBox.Show("Please enter a valid email address", "M&P Records - Email Check") 
      With txtPassword 
       .Clear() 
      End With 
      With txtUsername 
       .Clear() 
       .Focus() 
      End With 
     End If 
    End If 
Catch ex As Exception 
    ' An error occured! Show the error to the user and then exit. 
    MessageBox.Show(ex.Message) 
End Try 

Answer 2

决定将电子邮件和密码组合成一个表。现在让所有事情变得更容易感谢您的帮助和建议。