ASP.NET C#登录sqlexpress数据库的意外结果

Question

让我先说一下。我是一名学生，这是一个班级的项目，我已经将教授Visual Basic的代码大致翻译成了C#，我的代码后面的代码如下。

protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
{
SqlConnection conUser = new SqlConnection(ConfigurationManager.ConnectionStrings["COP2823ConnectionString"].ToString());
SqlCommand cmdUser = new SqlCommand();
string strSQL;
SqlDataReader dtrUser;
DateTime timestamp = new DateTime();
strSQL = "select * FROM students WHERE studentid=@studentid AND courseid=@courseid AND password=@pwd";
cmdUser.CommandType = System.Data.CommandType.Text;
cmdUser.CommandText = strSQL;
cmdUser.Parameters.Add("@studentid", System.Data.SqlDbType.VarChar).Value = Login1.UserName;
cmdUser.Parameters.Add("@courseid", System.Data.SqlDbType.VarChar).Value = "COP2823";
cmdUser.Parameters.Add("@pwd", System.Data.SqlDbType.VarChar).Value = Login1.Password;
conUser.Open();
cmdUser.Connection = conUser;
dtrUser = cmdUser.ExecuteReader();
if (dtrUser.HasRows)
{
dtrUser.Read();
HttpContext.Current.Session["sid"] = dtrUser["studentid"];
HttpContext.Current.Session["fname"] = dtrUser["first"];
HttpContext.Current.Session["lname"] = dtrUser["last"];
HttpContext.Current.Session["ulevel"] = dtrUser["userlevel"];
HttpContext.Current.Session["email"] = dtrUser["email"];
HttpContext.Current.Session["course"] = "COP2823";
dtrUser.Close();
conUser.Close();
strSQL = "UPDATE students SET lastlogin=@lastlogin WHERE studentid=@user2";
cmdUser.CommandType = System.Data.CommandType.Text;
cmdUser.CommandText = strSQL;
timestamp = DateTime.Now;
cmdUser.Parameters.Add("@user2", System.Data.SqlDbType.VarChar).Value = Login1.UserName;
cmdUser.Parameters.Add("@lastlogin", System.Data.SqlDbType.VarChar).Value = timestamp.ToString();
conUser.Open();
cmdUser.Connection = conUser;
dtrUser = cmdUser.ExecuteReader();
dtrUser.Close();
conUser.Close();
}
dtrUser.Close();
conUser.Close();
}
protected void LoginButton_Click(object sender, EventArgs e)
{
Login1_Authenticate(null, null);
}

现在我遇到的问题是，当我使用登录功能时，我可以键入我想要的任何内容，它会授予我访问权限，如果我按下实际的登录按钮，它会显示登录失败。我的假设是这与我的有关

cmdUser.Parameters.Add("@studentid", System.Data.SqlDbType.VarChar).Value = Login1.UserName;
cmdUser.Parameters.Add("@courseid", System.Data.SqlDbType.VarChar).Value = "COP2823";
cmdUser.Parameters.Add("@pwd", System.Data.SqlDbType.VarChar).Value = Login1.Password;

但我一直找不到合适的用法。如果这是一个重复的问题，我很抱歉，我已经解决了许多与数据库有关的其他问题，我有一个使用数据库的评论部分，工作得很好，一个访问数据库的网格视图也很好。

Answer 1

当进入登录页面时，会话应该被清除，可能在第一次成功登录后，会话仍然持有值，因此可以进行正确的身份验证。

//here in login page
protected void Page_Load(object sender, EventArgs e)
{
HttpContext.Current.Session["studentid"] = null;
//repeat code for other sessions
}

这是向cmd命令添加参数值的正确用法

cmd.Parameters.AddWithValue("@param", value);

关于即使凭据不正确也能登录：

您正在分配会话，当您重定向到所需页面时，您是否正在检查如果会话已加载？。像这样：

protected void Page_Load(object sender, EventArgs e)
{
if (Session["studentid"] == null)
{
Response.Redirect("login.aspx");
}
}

如果没有什么会使网页阻止你访问它

Answer 2

不是一个完整的答案，但我想帮助您清理一些东西，新代码包含了一些可能缺失的东西，以直接解决问题。

protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
{
string SQL = "
SELECT * 
FROM students 
WHERE studentid=@studentid AND courseid=@courseid AND password=@pwd;
-- Update login time as part of the SAME SQL BATCH!
If @@rowcount = 1 BEGIN
UPDATE students
SET LastLogin = current_timestamp
WHERE studentid = @studentid ;
END";
// using blocks guarantee the connection is closed, even if an exception is thrown.
// The original code would have left the connection hanging open if there was an exception.
using (var conUser = new SqlConnection(ConfigurationManager.ConnectionStrings["COP2823ConnectionString"].ToString()))
using (var cmdUser = new SqlCommand(SQL, conUser))
{
cmdUser.Parameters.Add("@studentid", System.Data.SqlDbType.VarChar).Value = Login1.UserName;
cmdUser.Parameters.Add("@courseid", System.Data.SqlDbType.VarChar).Value = "COP2823";
// Should be using a HASH VALUE here!
cmdUser.Parameters.Add("@pwd", System.Data.SqlDbType.VarChar).Value = CreatePwdHash(Login1.Password);
conUser.Open();
using (SqlDataReader dtrUser = cmdUser.ExecuteReader())
{
if (dtrUser.Read())          
{
HttpContext.Current.Session["sid"] = dtrUser["studentid"];
HttpContext.Current.Session["fname"] = dtrUser["first"];
HttpContext.Current.Session["lname"] = dtrUser["last"];
HttpContext.Current.Session["ulevel"] = dtrUser["userlevel"];
HttpContext.Current.Session["email"] = dtrUser["email"];
HttpContext.Current.Session["course"] = "COP2823";
if (e is object) e.Authenticated = true;
}
else //This section was missing!
{
HttpContext.Current.Session["sid"] = null;
HttpContext.Current.Session["fname"] = null;
HttpContext.Current.Session["lname"] = null;
HttpContext.Current.Session["ulevel"] = null;
HttpContext.Current.Session["email"] = null;
HttpContext.Current.Session["course"] = null;
if (e is object) e.Authenticated = false;
}
dtrUser.Close();
}
}
}