登录名、电子邮件和密码验证无法在 SQL 表 Vb 中正常工作

Question

我一直在尝试做的是从我的SQL表中获取用户名，密码和电子邮件，并通过 vb.net 上的登录表单进行验证。因此，当我在表单中输入用户名，密码和电子邮件时，它应该告诉我登录是否成功。但是，每当我在登录表单中输入我创建的sql表(MemberInfo)中的用户名，密码和电子邮件时，即使我知道用户名，密码和电子邮件是正确的(目前正在查看)，我也会不断收到错误"用户名，密码或电子邮件不正确，请重试"。我已经在youtube上尝试了多个视频，甚至在这里以及其他网站上尝试了一些解决方案，但没有任何效果。有人可以告诉我我做错了什么吗？这是我的 vb 代码：

Imports System.Data
Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Public Shared conS As String = "Server= ; Database=Yourdatabase ;Trusted_Connection=Yes;"
Public Shared con As SqlConnection = New SqlConnection(conS)
Protected Sub TextBox8_TextChanged(sender As Object, e As EventArgs) Handles TextBox8.TextChanged
End Sub
Protected Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
Dim un, pw, em, dbUN, dbPW, dbEM As String
un = TextBox7.Text
pw = TextBox8.Text
em = TextBox9.Text
Dim cmdUN As New SqlCommand("Select UserName from MembershipInfo Where Username = @p1", con)
With cmdUN.Parameters
.Clear()
.AddWithValue("@p1", un)
End With
Dim cmdPW As New SqlCommand("Select Pass from MembershipInfo Where UserName = @p1", con)
With cmdPW.Parameters
.Clear()
.AddWithValue("@p1", un)
End With
Dim cmdEM As New SqlCommand("Select Email from MembershipInfo where UserName = @p1", con)
With cmdEM.Parameters
.Clear()
.AddWithValue("@p1", un)
End With
Try
If con.State = ConnectionState.Closed Then con.Open()
dbUN = cmdUN.ExecuteScalar
dbPW = cmdPW.ExecuteScalar
dbEM = cmdEM.ExecuteScalar
Catch ex As Exception
Response.Write(ex.Message)
Finally
con.Close()
End Try
If un = dbUN And pw = dbPW And em = dbEM Then
MsgBox("Login Sucessful", vbExclamation, "Welcome")
Else
MsgBox("Username, Password or Email does not match, please try again", vbExclamation, "Error")
End If
End Sub
End Class

这是我的sql代码(我不知道是否需要它，但最好谨慎)：

Create Table MembershipInfo
(
MembershipID INT NOT NULL PRIMARY KEY Identity(1,1),
Firstname varchar(50) not null,
Lastname varchar(50) not null,
UserName char(50) not null,
Pass char(50) not null,
Email char(50) not null
); 
INSERT INTO MembershipInfo VALUES
('Jaycie', 'Adams', 'Imtotiredforthis', 'Golden1@1', '[e-mail]'),
('Bret', 'Steidinger', 'HellowWord', 'Wowwzaa12@', '[e-mail]'),
('Rebecca', 'Wong', 'SomethingSomething1@1', 'Outofideas11', '[e-mail]'),
('Ciel', 'Phantomhive', 'DownwiththeQeen1@1', 'FellintomytrapWaha22@', '[e-mail]'),
('Jane', 'Borden', 'TiredTM4@1', 'Justtakemypasswordalready@3', '[e-mail]');
Select * from MembershipInfo;

Answer 1

不要在使用它们的方法之外声明Connection。任何公开Dispose方法的数据库对象都是如此。

为什么不直接使用电子邮件作为用户名。

"创建表"中的这些行将需要固定长度的字符串。根本不是你想要的。坚持varchar或nvarchar。

UserName char(50) not null,
Pass char(50) not null,
Email char(50) not null

我不熟悉您使用的插入语法。

Using块处理关闭和处置对象，即使存在错误也是如此。

使用 Sql Server 的Parameters集合的Add方法。 http://www.dbdelta.com/addwithvalue-is-evil/和 https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/另一个： https://dba.stackexchange.com/questions/195937/addwithvalue-performance-and-plan-cache-implications 这是另一个 https://andrevdm.blogspot.com/2010/12/parameterised-queriesdont-use.html

尝试为控件使用有意义的名称。

Protected Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
If IsLoginValid(txtEmail.Text, txtPassword.Text) Then
MsgBox("Login Sucessful", vbExclamation, "Welcome")
Else
MsgBox("Username, Password or Email does not match, please try again", vbExclamation, "Error")
End If
End Sub
Private Function IsLoginValid(email As String, pword As String) As Boolean
Dim RetVal As Integer
Using cn As New SqlConnection(conS),
cmd As New SqlCommand("Select Count(*) From MembershipInfo Where Email = @Name And Pass = @Pass")
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = email
cmd.Parameters.Add("@Pass", SqlDbType.VarChar, 50).Value = pword
cn.Open()
RetVal = CInt(cmd.ExecuteScalar)
End Using
If RetVal = 1 Then
Return True
End If
Return False
End Function

此代码最糟糕的部分是您将密码存储为纯文本。密码应在存储前进行加盐和哈希处理。