参数化查询与 SQL 注入 [英] parameterized queries vs. SQL injection

问题描述

我是 Asp.net 的新手，我刚刚开始使用类.我最近创建了一个类，它将为我处理大部分 SQL 查询，这样我就不必在所有文件上重复创建新连接.

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

例如，我将调用以下方法:

public static DataTable SqlDataTable(string sql){使用 (SqlConnection conn = new SqlConnection(DatabaseConnectionString)){SqlCommand cmd = new SqlCommand(sql, conn);cmd.Connection.Open();DataTable TempTable = new DataTable();TempTable.Load(cmd.ExecuteReader());返回临时表；}}

所以从另一个文件我想像这样使用这个方法:

DataTable dt = new DataTable();dt = SqlComm.SqlDataTable("SELECT * FROM Users WHERE UserName='" + login.Text + "' and Password='" + password.Text + "'");如果(dt.Rows.Count > 0){//如果查询返回行，做一些事情}

这有效，但仍然容易受到注射的影响，对吗?有没有办法可以将变量作为参数传递给字符串?我知道如果我为查询创建一个新的 SQLCommand 对象并使用 Parameters.AddWithValue，我可以做到这一点，但我希望我的所有 SQL 命令都在单独的类中.

解决方案

这有效，但仍然容易受到注射的影响，对吗?

是的，您的代码非常容易受到 SQL 注入的攻击.<​​/p><块引用>

我知道我应该使用参数化查询来避免 SQL 注入.

哦，绝对是的.

<块引用>

我的问题是，当我将查询作为字符串参数传递时，我该怎么做?

您根本不应该将查询作为字符串参数传递.相反，您应该将查询作为包含占位符和这些占位符的值的字符串参数传递:

public static DataTable SqlDataTable(string sql, IDictionary values){使用 (SqlConnection conn = new SqlConnection(DatabaseConnectionString))使用 (SqlCommand cmd = conn.CreateCommand()){conn.Open();cmd.CommandText = sql;foreach(KeyValuePair 值中的项){cmd.Parameters.AddWithValue("@" + item.Key, item.Value);}DataTable table = new DataTable();使用 (var reader = cmd.ExecuteReader()){table.Load(读者);返回表；}}}

然后像这样使用你的函数:

DataTable dt = SqlComm.SqlDataTable("SELECT * FROM Users WHERE UserName = @UserName AND Password = @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屋！