C# 如何使用 SQL 参数从 SQL Server 获取数据集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18752181/
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
How to use SQL parameters to get dataset from SQL Server
提问by Fadi Khalil
I'm working on C# project and I'm new to this technology.
我正在研究 C# 项目，我对这项技术不熟悉。
I want to read some data from SQL Server 2008, and I write the following code
我想从 SQL Server 2008 中读取一些数据，我编写了以下代码
public User select(string username, string password)
{
string connection = ConfigurationManager.ConnectionStrings["lawyersDBConnectionString"].ConnectionString.ToString();
string sql = string.Format("select * from users where userName = '{0}' and password = '{1}'", username, password);
SqlConnection con = new SqlConnection();
con.ConnectionString = connection;
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sql, con);
User user = new User();
DataRow dr;
try
{
da.Fill(ds);
dr = ds.Tables[0].Rows[0];
user.Id = Convert.ToInt16(dr["userID"]);
user.FirstName = (string)dr["firstName"];
user.LastName = (string)dr["lastName"];
user.Email = (string)dr["email"];
user.Username = (string)dr["userName"];
user.Password = (string)dr["password"];
user.type = (string)dr["type"];
return user;
}
catch (Exception ex)
{
return null;
}
}//end of select method
But I had read an article about SQL injection, and I want to use SQL parameters to avoid this, but I don't know how.
但是我看过一篇关于SQL注入的文章，我想使用SQL参数来避免这种情况，但我不知道如何。
采纳答案by Steve
This is a simple rework on your code. Not tested, but essentially it consist in adding the using statementaround the disposable objects and the use of a SqlCommand with its parameters collection
这是对您的代码的简单返工。未经测试，但本质上它包括在一次性对象周围添加using 语句以及使用 SqlCommand 及其参数集合
string connection = ConfigurationManager.ConnectionStrings ["lawyersDBConnectionString"].ConnectionString.ToString();
string sql = "select * from users where userName = @uname and password = @pwd";
DataSet ds = new DataSet();
using(SqlConnection con = new SqlConnection(connection))
using(SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();
cmd.Parameters.AddWithValue("@uname", username);
cmd.Parameters.AddWithValue("@pwd", password);
using(SqlDataAdapter da = new SqlDataAdapter(cmd))
{
User user = new User();
DataRow dr;
da.Fill(ds);
dr = ds.Tables[0].Rows[0];
user.Id = Convert.ToInt16(dr["userID"]);
user.FirstName = (string)dr["firstName"];
user.LastName = (string)dr["lastName"];
user.Email = (string)dr["email"];
user.Username = (string)dr["userName"];
user.Password = (string)dr["password"];
user.type = (string)dr["type"];
return user;
}
}
Notice how the command text doesn't contain directly the strings for user and password but a simple parameter placeholder (@uname and @pwd). These placeholders are referred as the parameters name when adding the parameters to the SqlCommand collection.
请注意命令文本如何不直接包含用户和密码字符串，而是一个简单的参数占位符(@uname and @pwd)。在将参数添加到 SqlCommand 集合时，这些占位符称为参数名称。
Looking at the usage of the data retrieved I strongly suggest you to look at simple ORM tools like Dapperthat could directly translate all of this code in the User object
查看检索到的数据的使用情况，我强烈建议您查看简单的 ORM 工具，例如Dapper，它可以直接翻译 User 对象中的所有代码
回答by Spencer Ruport
Interestingly enough, the way String.Format works isn't much different from SQL parameters. The only real difference is that you specify the type of data each parameter is which allows the SQLCommand to properly sanitize (read: prevent sql injection) your user's input.
有趣的是，String.Format 的工作方式与 SQL 参数没有太大区别。唯一真正的区别是您指定每个参数的数据类型，这允许 SQLCommand 正确清理（读取：防止 sql 注入）您的用户输入。
Here's an example of how you might alter your code to use SQL Parameters.
下面是一个示例，说明如何更改代码以使用 SQL 参数。
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand("select * from users where userName = @pUsername and password = @pPassword", connection))
{
command.Parameters.Add(new SqlParameter("pUsername", username));
command.Parameters.Add(new SqlParameter("pPassword", password));
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(command);
// The rest of your code here...
}
}
A few things I'd like to point out though:
不过我想指出几点：
- Usernames are typically case insensitive so the query should probably use a LIKE or UCASE() comparison to look for the username.
- It's apparent from the query that your password is not hashed or salted. This is very bad. Read up on hashing passwords. https://crackstation.net/hashing-security.htm
- Basically what you're creating here is called an object relational manager. Unless you're specifically interested in learning how to develop one I highly recommend you use one that's been tried and tested. Personally I use nHibernate. Hibernate was written as an ORM for Java and nHibernate is extremely popular for .Net applications. Entity Framework is Microsoft's ORM. I don't think it's quite on par with nHibernate yet but it's constantly improving.
- 用户名通常不区分大小写，因此查询可能应该使用 LIKE 或 UCASE() 比较来查找用户名。
- 从查询中可以明显看出您的密码没有经过哈希处理或加盐处理。这真是太糟了。阅读散列密码。https://crackstation.net/hashing-security.htm
- 基本上，您在此处创建的内容称为对象关系管理器。除非您对学习如何开发一个特别感兴趣，否则我强烈建议您使用经过尝试和测试的一个。我个人使用 nHibernate。Hibernate 是作为 Java 的 ORM 编写的，nHibernate 在 .Net 应用程序中非常流行。实体框架是微软的 ORM。我不认为它与 nHibernate 相当，但它正在不断改进。
回答by jonathana
Here is a reusable method I wrote for that need:
这是我为该需求编写的可重用方法：
public static DataSet GetDataSetWithParameters(string query, List<SqlParameter> parameters)
{
DataSet ds = new DataSet();
SqlConnection Con = new SqlConnection(ConnectionString);
Con.Open();
try
{
using (SqlCommand cmd = new SqlCommand(query, Con))
{
if (parameters != null)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
using (SqlDataAdapter Adapter = new SqlDataAdapter(cmd))
{
Adapter.Fill(ds);
}
return ds;
}
}
catch
{
throw;
}
finally
{
CloseConnection(ref Con);
}
}
