登录成功后如何将SQL数据库的用户姓名直接填充到VB程序的文本框中

原代码核心错误点 #

• 姓名查询的SQL语法错误：Select Firstname, Lastname From MembershipInfo where Username = @p1, Password = @p2, Email = @p3 中WHERE后的多条件不能用逗号分隔，要换成AND连接
• 声明的cmdPUN没有绑定数据库连接对象con，执行查询时会直接抛出异常
• 逻辑顺序错误：当前代码先尝试读取姓名填充文本框，再校验登录是否成功，即使用户信息不匹配也可能触发填充逻辑
• 多余的数据库查询：分3次单独查用户名、密码、邮箱，再查一次姓名，完全可以合并为1次查询，减少数据库交互开销
• 安全隐患：数据库明文存储密码属于高危操作，生产环境必须使用加盐哈希算法处理密码后再存储

修正后的完整代码 #

Protected Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
    ' 非空校验
    If String.IsNullOrWhiteSpace(TextBox7.Text) Then
        MsgBox("Please enter Username", vbExclamation, "Error")
        Exit Sub
    End If
    If String.IsNullOrWhiteSpace(TextBox8.Text) Then
        MsgBox("Please enter Password", vbExclamation, "Error")
        Exit Sub
    End If
    If String.IsNullOrWhiteSpace(TextBox9.Text) Then
        MsgBox("Please enter Email", vbExclamation, "Error")
        Exit Sub
    End If

    Dim un As String = TextBox7.Text
    Dim pw As String = TextBox8.Text
    Dim em As String = TextBox9.Text
    Dim myreader As SqlDataReader = Nothing

    ' 合并为一次查询，取出所有需要的字段，修正SQL条件语法，绑定连接对象con
    Dim cmd As New SqlCommand("Select UserName, Password, Email, Firstname, Lastname From MembershipInfo where Username = @p1 AND Password = @p2 AND Email = @p3", con)

    With cmd.Parameters
        .Clear()
        .AddWithValue("@p1", un)
        .AddWithValue("@p2", pw)
        .AddWithValue("@p3", em)
    End With

    Try
        If con.State = ConnectionState.Closed Then con.Open()
        myreader = cmd.ExecuteReader()

        If myreader.HasRows AndAlso myreader.Read() Then
            ' 匹配到用户，直接从查询结果取字段校验，不需要额外查询
            Dim dbUN As String = myreader.Item("UserName").ToString()
            Dim dbPW As String = myreader.Item("Password").ToString()
            Dim dbEM As String = myreader.Item("Email").ToString()

            If un = dbUN AndAlso pw = dbPW AndAlso em = dbEM Then
                ' 登录校验通过后再填充姓名文本框
                TextBox1.Text = myreader.Item("Firstname").ToString()
                TextBox2.Text = myreader.Item("Lastname").ToString()
                MsgBox("Login Sucessful", vbExclamation, "Welcome")
            End If
        Else
            ' 没有匹配到用户，判断是哪个字段错误
            ' 生产环境建议统一返回「用户名、密码或邮箱不匹配」，避免被枚举有效用户名
            Dim checkUserCmd As New SqlCommand("SELECT COUNT(1) FROM MembershipInfo WHERE UserName = @p1", con)
            checkUserCmd.Parameters.AddWithValue("@p1", un)
            Dim userCount As Integer = CInt(checkUserCmd.ExecuteScalar())
            If userCount = 0 Then
                MsgBox("Username does not match, please try again", vbExclamation, "Error")
            Else
                Dim checkPwCmd As New SqlCommand("SELECT COUNT(1) FROM MembershipInfo WHERE UserName = @p1 AND Password = @p2", con)
                checkPwCmd.Parameters.AddWithValue("@p1", un)
                checkPwCmd.Parameters.AddWithValue("@p2", pw)
                Dim pwCount As Integer = CInt(checkPwCmd.ExecuteScalar())
                If pwCount = 0 Then
                    MsgBox("Password does not match, please try again", vbExclamation, "Error")
                Else
                    MsgBox("Email does not match, please try again", vbExclamation, "Error")
                End If
            End If
        End If

    Catch ex As Exception
        Response.Write(ex.Message)
    Finally
        ' 先关闭DataReader再关闭连接
        If myreader IsNot Nothing AndAlso Not myreader.IsClosed Then
            myreader.Close()
        End If
        con.Close()
    End Try

    ' 清空登录输入框
    TextBox7.Text = String.Empty
    TextBox8.Text = String.Empty
    TextBox9.Text = String.Empty
End Sub

补充说明 #

• 原代码使用的MsgBox是服务器端弹窗，仅本地调试时可见，部署到线上后客户端无法看到，建议替换为ClientScript.RegisterStartupScript调用前端JS的alert实现客户端弹窗
• 生产环境绝对不能明文存储密码，需要用BCrypt、SHA256加盐等方式对密码做哈希处理后再存入数据库，避免数据泄露后用户密码直接暴露

内容的提问来源于stack exchange，提问作者Cornelius Spark