参数化查询与SQL注入 [英] parameterized queries vs. SQL injection
问题描述
我是新来Asp.net,我刚开始带班工作。我最近创建了一个类,将处理我的大多数SQL查询,为我,让我不必重复创建了我的所有文件的新连接。
一个我创建的方法需要在SQL查询作为参数,并返回结果。我知道我应该使用参数化查询,以避免SQL注入。我的问题是,我怎么能做到这一点时,我传递查询作为字符串参数?
例如,这里有一个方法,我会打电话:
公共静态数据表SqlDataTable(SQL字符串)
{
使用(SqlConnection的康恩=新的SqlConnection(DatabaseConnectionString))
{
CMD的SqlCommand =新的SqlCommand(SQL,conn);在
cmd.Connection.Open();
数据表不是Temptable =新的DataTable();
TempTable.Load(cmd.ExecuteReader());
回归不是Temptable;
}
}
因此,从另一个文件,我想用这个方法像这样:
DataTable的DT =新的DataTable();DT = SqlComm.SqlDataTable(SELECT * FROM用户其中username ='+ login.Text +'和密码='+ password.Text +');如果(dt.Rows.Count大于0)
{
//做一些事情,如果查询返回的行
}
这工作,但仍然是脆弱的打针吧?有没有一种方法,我可以通过该变量的字符串作为参数?我知道我能做到这一点,如果我创建了查询的新SqlCommand对象,并使用Parameters.AddWithValue,但我想我所有的SQL命令是在单独的类。
这工作,但仍然是脆弱的打针吧?
块引用>呀,你的code是可怕容易受到SQL注入。
我知道我应该使用参数化查询,以避免SQL注入。
块引用>哦,绝对是的。
我的问题是,我怎么能做到这一点时,我传递查询作为字符串参数?
块引用>您根本不应该传递查询作为字符串参数。相反,你应该传递查询作为含占位符和值这些占位符字符串参数:
公共静态数据表SqlDataTable(字符串SQL,IDictionary的<字符串对象>的值)
{
使用(SqlConnection的康恩=新的SqlConnection(DatabaseConnectionString))
使用(CMD的SqlCommand = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = SQL;
的foreach(KeyValuePair<字符串对象>的值项)
{
cmd.Parameters.AddWithValue(@+ item.Key,item.Value);
} 数据表表=新的DataTable();
使用(VAR读卡器= cmd.ExecuteReader())
{
table.Load(读卡器);
返回表;
}
}
}然后用你的功能如下:
DataTable的DT = SqlComm.SqlDataTable(
SELECT * FROM用户其中username = @用户和密码= @Password
新字典<字符串对象>
{
{用户名,login.Text},
{密码,password.Text},
}
);如果(dt.Rows.Count大于0)
{
//做一些事情,如果查询返回的行
}I am new to Asp.net and I'm just starting to work with classes. I recently created a class that will handle most of my SQL queries for me so that I don't have to repeatedly create new connections over all my files.
One of the methods I've created takes in an SQL query as a parameter and returns the result. I know that I should be using parameterized queries to avoid SQL injections. My question is, how can I do this when I'm passing the query as a string parameter?
For example, here's a method I'll be calling:
public static DataTable SqlDataTable(string sql) { using (SqlConnection conn = new SqlConnection(DatabaseConnectionString)) { SqlCommand cmd = new SqlCommand(sql, conn); cmd.Connection.Open(); DataTable TempTable = new DataTable(); TempTable.Load(cmd.ExecuteReader()); return TempTable; } }
So from another file I'd like to use this method like so:
DataTable dt = new DataTable(); dt = SqlComm.SqlDataTable("SELECT * FROM Users WHERE UserName='" + login.Text + "' and Password='" + password.Text + "'"); if (dt.Rows.Count > 0) { // do something if the query returns rows }
This works but would still be vulnerable to injections right? Is there a way I can pass the variables to the string as parameters? I know I can do this if I create a new SQLCommand object for the query and use Parameters.AddWithValue, but I wanted all my SQL commands to be in the separate class.
解决方案This works but would still be vulnerable to injections right?
Yeah, your code is terrifyingly vulnerable to SQL injections.
I know that I should be using parameterized queries to avoid SQL injections.
Oh absolutely yeah.
My question is, how can I do this when I'm passing the query as a string parameter?
You simply shouldn't be passing the query as a string parameter. Instead you should be passing the query as string parameter containing placeholders and the values for those placeholders:
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; } } }
and then use your function like this:
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 }
这篇关于参数化查询与SQL注入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!