3 回答
TA贡献1856条经验 获得超11个赞
这行得通,但仍然容易受到注射的侵害吧?
是的,您的代码非常容易受到SQL注入的攻击。
我知道我应该使用参数化查询来避免SQL注入。
哦,是的。
我的问题是,当我将查询作为字符串参数传递时,该怎么办?
您根本不应该将查询作为字符串参数传递。相反,您应该将查询作为包含占位符和这些占位符值的字符串参数传递:
public static DataTable SqlDataTable(string sql, IDictionary<string, object> values)
{
using (SqlConnection conn = new SqlConnection(DatabaseConnectionString))
using (SqlCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = sql;
foreach (KeyValuePair<string, object> item in values)
{
cmd.Parameters.AddWithValue("@" + item.Key, item.Value);
}
DataTable table = new DataTable();
using (var reader = cmd.ExecuteReader())
{
table.Load(reader);
return table;
}
}
}
然后像这样使用您的函数:
DataTable dt = SqlComm.SqlDataTable(
"SELECT * FROM Users WHERE UserName = @UserName AND Password = @Password",
new Dictionary<string, object>
{
{ "UserName", login.Text },
{ "Password", password.Text },
}
);
if (dt.Rows.Count > 0)
{
// do something if the query returns rows
}
TA贡献1811条经验 获得超6个赞
您走在正确的道路上,我实际上也已经完成了您在寻找自己的事情。但是,我不仅传递字符串给函数,还传递了SQL Command对象...这样,您可以正确构建所有命令和参数,然后说...在这里,运行它,准备出发了。就像是
public static DataTable SqlDataTable(SqlCommand cmd)
{
using (SqlConnection conn = new SqlConnection(DatabaseConnectionString))
{
cmd.Connection = conn; // store your connection to the command object..
cmd.Connection.Open();
DataTable TempTable = new DataTable();
TempTable.Load(cmd.ExecuteReader());
return TempTable;
}
}
public DataTable GetMyCustomers(string likeName)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select * from SomeTable where LastName like "@someParm%";
cmd.Parameters.Add( "whateverParm", likeName ); // don't have SQL with me now, guessing syntax
// so now your SQL Command is all built with parameters and ready to go.
return SqlDataTable( cmd );
}
- 3 回答
- 0 关注
- 629 浏览
添加回答
举报