如何使用SQL参数从SQL Server获取数据集 [英] How to use SQL parameters to get dataset from SQL Server
问题描述
我正在从事C#项目,并且对这项技术不熟悉.
I'm working on C# project and I'm new to this technology.
我想从SQL Server 2008中读取一些数据,然后编写以下代码
I want to read some data from SQL Server 2008, and I write the following code
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
但是我读过一篇有关SQL注入的文章,并且我想使用SQL参数来避免这种情况,但是我不知道如何.
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.
推荐答案
这是对代码的简单重做.尚未经过测试,但实际上,它包括在一次性用品周围添加使用声明对象以及带有参数集合的SqlCommand的使用
This is a simple rework on your code. Not tested, but essentially it consist in adding the using statement around the disposable objects and the use of a SqlCommand with its parameters collection
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;
}
}
请注意,命令文本如何不直接包含用户和密码的字符串,而是一个简单的参数占位符(@uname and @pwd)
.将参数添加到SqlCommand集合时,这些占位符称为参数名称.
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.
看看检索到的数据的用途,我强烈建议您看一下简单的ORM工具,例如
Looking at the usage of the data retrieved I strongly suggest you to look at simple ORM tools like Dapper that could directly translate all of this code in the User object
这篇关于如何使用SQL参数从SQL Server获取数据集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!