asp.net中的查询出错
本文关键字:查询 出错 net asp | 更新日期: 2024-09-22 12:25:48
错误显示无效列名mustubain。mustubain是UserName.Text.toString()
的值
string query = "select userid from register where username = " + UserName.Text.ToString() + " and " + "password = " + Password.Text.ToString();
SqlCommand cmd1 = new SqlCommand(query,connection);
connection.Open();
SqlDataReader rd1 = cmd1.ExecuteReader();
while(rd1.Read())
{
Session["checkuserid"] = rd1["userid"];
}
connection.Close();
首先,您不应该使用字符串串联来构建查询,因为这会使您容易受到SQL注入攻击,并且可能导致查询不正确(因为您的参数周围缺少勾号):
// This would attempt to state username = mustufain instead of
// username = 'mustufain' (and SQL doesn't know what mustufain is)
var query = "select userid from register where username = '" + UserName.Text + "' and " + "password = '" + Password.Text + "'";
使用参数化的更好方法如下所示,它避免了错误的语法,并为您提供了防止任何恶意注入的保护:
// Open your connection
using(var connection = new SqlConnection("{your connection string}"))
{
// Build your query
var query = "SELECT TOP 1 userid FROM register WHERE username = @username AND password = @password";
// Build a command (to execute your query)
using(var command = new SqlCommand(query, connection))
{
// Open your connection
connection.Open();
// Add your parameters
command.Parameters.AddWithValue("@username",UserName.Text);
command.Parameters.AddWithValue("@password",Password.Text);
// Execute your query
var user = Convert.ToString(command.ExecuteScalar());
// If a user was found, then set it
if(!String.IsNullOrEmpty(user))
{
Session["checkuserid"] = user;
}
else
{
// No user was found, consider alerting the user
}
}
}
最后,您可能需要重新考虑如何存储凭据(以明文形式)。ASP.NET提供了各种各样的提供程序,可以帮助您处理此过程,这样您就不必自己处理了。
您正试图连接字符串以构建sql查询,但通常会出现错误。在特定情况下,您忘记将字符串值括在单引号之间。但进行此查询的唯一正确方法是通过参数化查询
string query = @"select userid from register
where username = @name and password = @pwd";
using(SqlCommand cmd1 = new SqlCommand(query,connection))
{
connection.Open();
cmd1.Parameters.Add("@name", SqlDbType.NVarChar).Value = UserName.Text;
cmd1.Parameters.Add("@pwd", SqlDbType.NVarChar).Value = Password.Text;
using(SqlDataReader rd1 = cmd1.ExecuteReader())
{
....
}
}
还要注意,在数据库中以明文形式存储密码是一种非常糟糕的做法,并且存在很大的安全风险。在这个网站上有很多问题和答案,解释了如何创建密码的哈希并存储该哈希而不是明文
例如:在数据库中存储密码的最佳方式