将 C# 中的预处理语句与 Mysql 结合使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11070434/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Prepared Statement in C# with Mysql
提问by user840866
I tried prepared statement in my program, but not working.
我在我的程序中尝试了准备好的语句，但没有工作。
The part commented is the Prepared Statement part. When I change it into normal statement, everything is right.
注释的部分是准备好的语句部分。当我将其更改为普通语句时，一切正常。
Can someone tell me what am I missing?
有人能告诉我我错过了什么吗？
Many thanks.
非常感谢。
private void btnLogin_Click(object sender, EventArgs e)
{
MySqlCommand cmd = MySqlConn.cmd;
//cmd = new MySqlCommand("SELECT * FROM admin WHERE admin_username='@val1' AND admin_password=PASSWORD('@val2')", MySqlConn.conn);
//cmd.Prepare();
//cmd.Parameters.AddWithValue("@val1", tboxUserName.Text);
//cmd.Parameters.AddWithValue("@val2", tboxPassword.Text);
cmd = new MySqlCommand("SELECT * FROM admin WHERE admin_username='"+tboxUserName.Text+"' AND admin_password=PASSWORD('"+tboxPassword.Text+"')", MySqlConn.conn);
MySqlDataReader res = cmd.ExecuteReader();
if (!res.HasRows) { MessageBox.Show("Error! "); res.Close(); return; }
else
{
//do something
}
res.Close();
}
采纳答案by Zbigniew
Try removing 'from your query and use Prepareafter adding parameters:
尝试'从您的查询中删除并Prepare在添加参数后使用：
cmd = new MySqlCommand("SELECT * FROM admin WHERE admin_username=@val1 AND admin_password=PASSWORD(@val2)", MySqlConn.conn);
cmd.Parameters.AddWithValue("@val1", tboxUserName.Text);
cmd.Parameters.AddWithValue("@val2", tboxPassword.Text);
cmd.Prepare();
回答by Lemonseed
Your solution is almost correct as-is. However, being this is a log-in process and thus a security-oriented task, there are a few suggestions I would like to make as well.
您的解决方案几乎是正确的。但是，由于这是一个登录过程，因此是一项面向安全的任务，因此我也想提出一些建议。
First, consider making your button event handler appear as follows:
首先，考虑使您的按钮事件处理程序如下所示：
private void btnLogin_Click(object sender, EventArgs e)
{
if (Login(tboxUserName.Text, tboxPassword.Text))
{
// Log in was successful, do something...
}
else
{
// Log in was NOT successful, inform the user...
}
}
This will make maintenance and readability of the application easier. Then declare a function named Login()to perform the heavy lifting:
这将使应用程序的维护和可读性更容易。然后声明一个名为Login()执行繁重工作的函数：
private bool Login(string username, string password)
{
try
{
MySqlCommand cmd = MySqlConn.cmd;
cmd = new MySqlCommand(
"SELECT count(*) FROM admin " +
"WHERE admin_username=@username " +
"AND admin_password=PASSWORD(@passwd)",
MySqlConn.conn);
cmd.Prepare();
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@passwd", password);
int result = (int)cmd.ExecuteReader();
// Returns true when username and password match:
return (result > 0);
}
catch (Exception e)
{
// Optional: log exception details
// Deny log in if an error has occurred:
return false;
}
}
You will notice a few things here. First, the quotes were removed from your original query string that were preventing the named parameters from working correctly. Additionally, the query returns a count()function result instead of attempting to create a result set containing the administrator username and password. Lastly, the method is encapsulated in a try-catch block , such that in the event an error occurs, the method returns falseand the log in is denied. I also broke the query into a concatenated string for easier readability.
你会注意到这里的一些事情。首先，从原始查询字符串中删除了阻止命名参数正常工作的引号。此外，查询返回一个count()函数结果，而不是尝试创建包含管理员用户名和密码的结果集。最后，该方法被封装在一个 try-catch 块中，以便在发生错误时，该方法返回false并拒绝登录。我还将查询分解为一个连接字符串，以便于阅读。
