参数化查询与SQL注入

Question

我是Asp.net的新手，我刚刚开始使用课程。最近，我创建了一个类，该类将为我处理大多数SQL查询，从而不必在所有文件上重复创建新连接。

我创建的方法之一将SQL查询作为参数并返回结果。我知道我应该使用参数化查询来避免SQL注入。我的问题是，当我将查询作为字符串参数传递时，该怎么办？

例如，这是我要调用的方法：

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;

    }

}

因此，从另一个文件中，我想使用这种方法：

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

}

这行得通，但仍然容易受到注射的侵害吧？有没有办法将变量作为参数传递给字符串？我知道如果我为查询创建一个新的SQLCommand对象并使用Parameters.AddWithValue，则可以执行此操作，但是我希望所有SQL命令都位于单独的类中。

Answer 1

这行得通，但仍然容易受到注射的侵害吧？是的，您的代码非常容易受到SQL注入的攻击。我知道我应该使用参数化查询来避免SQL注入。哦，是的。我的问题是，当我将查询作为字符串参数传递时，该怎么办？您根本不应该将查询作为字符串参数传递。相反，您应该将查询作为包含占位符和这些占位符值的字符串参数传递：public static DataTable SqlDataTable(string sql, IDictionary<string, object> values){&nbsp; &nbsp; using (SqlConnection conn = new SqlConnection(DatabaseConnectionString))&nbsp; &nbsp; using (SqlCommand cmd = conn.CreateCommand())&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; conn.Open();&nbsp; &nbsp; &nbsp; &nbsp; cmd.CommandText = sql;&nbsp; &nbsp; &nbsp; &nbsp; foreach (KeyValuePair<string, object> item in values)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cmd.Parameters.AddWithValue("@" + item.Key, item.Value);&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; DataTable table = new DataTable();&nbsp; &nbsp; &nbsp; &nbsp; using (var reader = cmd.ExecuteReader())&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; table.Load(reader);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return table;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }}然后像这样使用您的函数：DataTable dt = SqlComm.SqlDataTable(&nbsp; &nbsp; "SELECT * FROM Users WHERE UserName = @UserName AND Password = @Password",&nbsp; &nbsp; new Dictionary<string, object>&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; { "UserName", login.Text },&nbsp; &nbsp; &nbsp; &nbsp; { "Password", password.Text },&nbsp; &nbsp; });if (dt.Rows.Count > 0){&nbsp; &nbsp;// do something if the query returns rows}

Answer 2

您走在正确的道路上，我实际上也已经完成了您在寻找自己的事情。但是，我不仅传递字符串给函数，还传递了SQL Command对象...这样，您可以正确构建所有命令和参数，然后说...在这里，运行它，准备出发了。就像是public static DataTable SqlDataTable(SqlCommand cmd){&nbsp; &nbsp; using (SqlConnection conn = new SqlConnection(DatabaseConnectionString))&nbsp; &nbsp; {&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; cmd.Connection = conn;&nbsp; &nbsp;// store your connection to the command object..&nbsp; &nbsp; &nbsp; &nbsp; cmd.Connection.Open();&nbsp; &nbsp; &nbsp; &nbsp; DataTable TempTable = new DataTable();&nbsp; &nbsp; &nbsp; &nbsp; TempTable.Load(cmd.ExecuteReader());&nbsp; &nbsp; &nbsp; &nbsp; return TempTable;&nbsp; &nbsp; }}public DataTable GetMyCustomers(string likeName){&nbsp; &nbsp; SqlCommand cmd = new SqlCommand();&nbsp; &nbsp; cmd.CommandText = "select * from SomeTable where LastName like "@someParm%";&nbsp; &nbsp; cmd.Parameters.Add( "whateverParm", likeName );&nbsp; // don't have SQL with me now, guessing syntax&nbsp; &nbsp; // so now your SQL Command is all built with parameters and ready to go.&nbsp; &nbsp; return SqlDataTable( cmd );}