为什么我们总是喜欢在SQL语句中使用参数？

Question

我对数据库工作非常陌生。现在我可以编写 SELECT、UPDATE、DELETE 和 INSERT 命令。但我见过很多论坛，我们更喜欢写：

SELECT empSalary from employee where salary = @salary

...而不是：

SELECT empSalary from employee where salary = txtSalary.Text

为什么我们总是喜欢使用参数以及我将如何使用它们？

我想知道第一种方法的用途和好处。我什至听说过SQL注入，但我并不完全理解它。我什至不知道SQL注入是否与我的问题有关。

Answer 1

当数据库与桌面程序或网站等程序界面结合使用时，使用参数有助于防止SQL注入攻击。

在您的示例中，用户可以通过在 txtSalary 中编写语句来直接在数据库上运行 SQL 代码。

例如，如果他们编写0 OR 1=1，则执行的 SQL 将

 SELECT empSalary from employee where salary = 0 or 1=1

返回所有 empSalaries。

此外，用户可能会对您的数据库执行更糟糕的命令，包括删除它如果他们写了 0;删除表员工：

SELECT empSalary from employee where salary = 0; Drop Table employee

然后将删除表employee。

---

就您而言，您似乎正在使用 .NET。使用参数就像：

string sql = "SELECT empSalary from employee where salary = @salary";

using (SqlConnection connection = new SqlConnection(/* connection info */))
using (SqlCommand command = new SqlCommand(sql, connection))
{
    var salaryParam = new SqlParameter("salary", SqlDbType.Money);
    salaryParam.Value = txtMoney.Text;

    command.Parameters.Add(salaryParam);
    var results = command.ExecuteReader();
}

Dim sql As String = "SELECT empSalary from employee where salary = @salary"
Using connection As New SqlConnection("connectionString")
    Using command As New SqlCommand(sql, connection)
        Dim salaryParam = New SqlParameter("salary", SqlDbType.Money)
        salaryParam.Value = txtMoney.Text

        command.Parameters.Add(salaryParam)

        Dim results = command.ExecuteReader()
    End Using
End Using

编辑 2016 年 4 月 25 日：

根据 George Stocker 的评论，我将示例代码更改为不使用 AddWithValue。另外，通常建议您将 IDisposable 包装在 using 语句中。

Answer 2

你是对的，这与SQL注入有关，这是一个允许恶意用户针对您的数据库执行任意语句。这部旧时最受欢迎的 XKCD 漫画 阐释了这个概念：

---

在你的例子中，如果你只是使用：

var query = "SELECT empSalary from employee where salary = " + txtSalary.Text;
// and proceed to execute this query

你就对 SQL 注入持开放态度。例如，假设有人输入 txtSalary：

1; UPDATE employee SET salary = 9999999 WHERE empID = 10; --
1; DROP TABLE employee; --
// etc.

当您执行此查询时，它将执行 SELECT 和 UPDATE 或 DROP，或者他们想要的任何内容。最后的 -- 只是注释掉查询的其余部分，如果您在 txtSalary.Text 之后连接任何内容，这在攻击中会很有用。

---

正确的方法是使用参数化查询，例如（C#）：

SqlCommand query =  new SqlCommand("SELECT empSalary FROM employee 
                                    WHERE salary = @sal;");
query.Parameters.AddWithValue("@sal", txtSalary.Text);

这样，您就可以安全地执行查询。

有关如何避免其他几种语言中的 SQL 注入的参考，请查看 bobby-tables.com，该网站由SO 用户。

Answer 3

除了其他答案之外，还需要添加参数不仅有助于防止 sql 注入，而且可以提高查询性能。 Sql 服务器缓存参数化查询计划并在重复查询执行时重用它们。如果您没有对查询进行参数化，那么如果查询文本不同，sql server 将为每个查询编译新计划（有一些排除）执行。

有关查询计划缓存的详细信息

Answer 4

在我第一次尝试两年后，我又重新开始......

为什么我们更喜欢参数？ SQL 注入显然是一个重要原因，但是否我们暗自渴望回到 SQL 作为一种语言。字符串文字中的 SQL 已经是一种奇怪的文化实践，但至少您可以将您的请求复制并粘贴到 Management Studio 中。使用宿主语言条件和控制结构动态构造的 SQL，当 SQL 有条件和控制结构时，只是 0 级野蛮行为。您必须在调试或跟踪中运行您的应用程序，才能查看它生成的 SQL。

不要仅仅停留在参数上。一路走下去并使用 QueryFirst （免责声明：我写的）。您的 SQL位于 .sql 文件中。您可以在精美的 TSQL 编辑器窗口中对其进行编辑，并对表和列进行语法验证和智能感知。您可以在特殊注释部分分配测试数据，然后单击“播放”以在窗口中运行您的查询。创建参数就像在 SQL 中输入“@myParam”一样简单。然后，每次保存时，QueryFirst 都会为您的查询生成 C# 包装器。您的参数会弹出，强类型化，作为 Execute() 方法的参数。您的结果以 IEnumerable 或强类型 POCO 列表的形式返回，这些类型是根据查询返回的实际架构生成的。如果您的查询未运行，您的应用程序将无法编译。如果您的数据库架构发生更改并且您的查询运行，但某些列消失，则编译错误将指向代码中尝试访问丢失数据的行。还有许多其他优点。 为什么您想以其他方式访问数据？

Answer 5

在Sql中，当任何单词包含@符号时，它意味着它是变量，我们使用这个变量来设置其中的值，并在同一个sql脚本的数字区域上使用它，因为它只限制在单个脚本上，而你可以声明很多变量许多脚本上具有相同的类型和名称。我们在存储过程中使用此变量，因为存储过程是预编译的查询，我们可以从脚本、桌面和网站传递这些变量中的值，以获取更多信息，请阅读 声明局部变量, Sql 存储过程 和 sql 注入。

另请阅读防止 SQL 注入它将指导您如何保护数据库。

希望它能帮助您理解任何问题也可以评论我。

Answer 6

旧帖子，但希望确保新人了解存储过程。

我的 10 美分价值是，如果您能够将 SQL 语句编写为存储过程，那么在我看来，这是最佳方法。我始终使用存储过程，并且从不在主代码中循环记录。例如：SQL 表> SQL存储过程> IIS/Dot.NET >类。

当您使用存储过程时，您可以限制用户仅具有EXECUTE权限，从而降低安全风险。

您的存储过程本质上是参数化的，您可以指定输入和输出参数。

存储过程（如果它通过 SELECT 语句返回数据）的访问和读取方式与代码中的常规 SELECT 语句完全相同。

由于它是在 SQL Server 上编译的，因此运行速度也更快。

我是否还提到过您可以执行多个步骤，例如更新表，检查另一个数据库服务器上的值，然后最终完成后，将数据返回给客户端，所有这些都在同一服务器上，并且没有交互与客户。因此，这比在代码中编写此逻辑要快得多。

Answer 7

其他答案解释了为什么参数很重要，但有一个缺点！在.net中，有多种创建参数的方法（Add、AddWithValue），但它们都需要您不必要地担心参数名称，并且它们都会降低代码中SQL的可读性。当您尝试思考 SQL 时，您需要在上方或下方寻找参数中使用的值。

我谦虚地声称我的小 SqlBuilder 类是编写参数化查询的最优雅的方式。您的代码将如下所示...

C#

var bldr = new SqlBuilder( myCommand );
bldr.Append("SELECT * FROM CUSTOMERS WHERE ID = ").Value(myId);
//or
bldr.Append("SELECT * FROM CUSTOMERS WHERE NAME LIKE ").FuzzyValue(myName);
myCommand.CommandText = bldr.ToString();

您的代码将更短且更具可读性。您甚至不需要额外的行，并且当您回读时，您不需要四处寻找参数的值。你需要的课程都在这里...

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

public class SqlBuilder
{
private StringBuilder _rq;
private SqlCommand _cmd;
private int _seq;
public SqlBuilder(SqlCommand cmd)
{
    _rq = new StringBuilder();
    _cmd = cmd;
    _seq = 0;
}
public SqlBuilder Append(String str)
{
    _rq.Append(str);
    return this;
}
public SqlBuilder Value(Object value)
{
    string paramName = "@SqlBuilderParam" + _seq++;
    _rq.Append(paramName);
    _cmd.Parameters.AddWithValue(paramName, value);
    return this;
}
public SqlBuilder FuzzyValue(Object value)
{
    string paramName = "@SqlBuilderParam" + _seq++;
    _rq.Append("'%' + " + paramName + " + '%'");
    _cmd.Parameters.AddWithValue(paramName, value);
    return this;
}
public override string ToString()
{
    return _rq.ToString();
}
}