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

Why do we always prefer using parameters in SQL statements?

提问by Sandy

I am very new to working with databases. Now I can write SELECT, UPDATE, DELETE, and INSERTcommands. But I have seen many forums where we prefer to write:

我对使用数据库很陌生。现在我可以写SELECT，UPDATE，DELETE，和INSERT命令。但是我看过很多论坛，我们更喜欢写：

SELECT empSalary from employee where salary = @salary

...instead of:

...代替：

SELECT empSalary from employee where salary = txtSalary.Text

Why do we always prefer to use parameters and how would I use them?

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

I wanted to know the use and benefits of the first method. I have even heard of SQL injection but I don't fully understand it. I don't even know if SQL injection is related to my question.

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

回答by Chad Levy

Using parameters helps prevent SQL Injection attackswhen the database is used in conjunction with a program interface such as a desktop program or web site.

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

In your example, a user can directly run SQL code on your database by crafting statements in txtSalary.

在您的示例中，用户可以通过在txtSalary.

For example, if they were to write 0 OR 1=1, the executed SQL would be

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

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

whereby all empSalaries would be returned.

从而将返回所有 empSalaries。

Further, a user could perform far worse commands against your database, including deleting it If they wrote 0; Drop Table employee:

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

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

The table employeewould then be deleted.

employee然后将删除该表。

---

---

In your case, it looks like you're using .NET. Using parameters is as easy as:

在您的情况下，您似乎在使用 .NET。使用参数非常简单：

C#

C＃

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();
}

VB.NET

网络

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

---

---

Edit 2016-4-25:

编辑 2016-4-25：

As per George Stocker's comment, I changed the sample code to not use AddWithValue. Also, it is generally recommended that you wrap IDisposables in usingstatements.

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

回答by NullUserException

You are right, this is related to SQL injection, which is a vulnerability that allows a malicioius user to execute arbitrary statements against your database. This old time favorite XKCD comicillustrates the concept:

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

---

---

In your example, if you just use:

在您的示例中，如果您只使用：

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

You are open to SQL injection. For example, say someone enters txtSalary:

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

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

When you execute this query, it will perform a SELECTand an UPDATEor DROP, or whatever they wanted. The --at the end simply comments out the rest of your query, which would be useful in the attack if you were concatenating anything after txtSalary.Text.

当您执行此查询时，它将执行 aSELECT和 an UPDATEor DROP，或他们想要的任何内容。将--在年底只需注释掉查询的其余部分，如果你串联后什么这将是在进攻中非常有用txtSalary.Text。

---

---

The correct way is to use parameterized queries, eg (C#):

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

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

With that, you can safely execute the query.

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

For reference on how to avoid SQL injection in several other languages, check bobby-tables.com, a website maintained by a SO user.

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

回答by Oleg

In addition to other answers need to add that parameters not only helps prevent sql injection but can improve performance of queries. Sql server caching parameterized query plans and reuse them on repeated queries execution. If you not parameterized your query then sql server would compile new plan on each query(with some exclusion) execution if text of query would differ.

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

More information about query plan caching

有关查询计划缓存的更多信息

回答by bbsimonbb

Two years after my first go, I'm recidivating...

在我第一次去两年后，我正在重蹈覆辙……

Why do we prefer parameters? SQL injection is obviously a big reason, but could it be that we're secretly longing to get back to SQL as a language. SQL in string literals is already a weird cultural practice, but at least you can copy and paste your request into management studio. SQL dynamically constructed with host language conditionals and control structures, when SQL has conditionals and control structures, is just level 0 barbarism. You have to run your app in debug, or with a trace, to see what SQL it generates.

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

Don't stop with just parameters. Go all the way and use QueryFirst(disclaimer: which I wrote). Your SQL lives in a .sql file. You edit it in the fabulous TSQL editor window, with syntax validation and Intellisense for your tables and columns. You can assign test data in the special comments section and click "play" to run your query right there in the window. Creating a parameter is as easy as putting "@myParam" in your SQL. Then, each time you save, QueryFirst generates the C# wrapper for your query. Your parameters pop up, strongly typed, as arguments to the Execute() methods. Your results are returned in an IEnumerable or List of strongly typed POCOs, the types generated from the actual schema returned by your query. If your query doesn't run, your app won't compile. If your db schema changes and your query runs but some columns disappear, the compile error points to the line in your codethat tries to access the missing data. And there are numerous other advantages. Why would you want to access data any other way?

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

回答by Emaad Ali

In Sql when any word contain @ sign it means it is variable and we use this variable to set value in it and use it on number area on the same sql script because it is only restricted on the single script while you can declare lot of variables of same type and name on many script. We use this variable in stored procedure lot because stored procedure are pre-compiled queries and we can pass values in these variable from script, desktop and websites for further information read Declare Local Variable, Sql Stored Procedureand sql injections.

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

Also read Protect from sql injectionit will guide how you can protect your database.

另请阅读Protect from sql injection它将指导您如何保护您的数据库。

Hope it help you to understand also any question comment me.

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

回答by bbsimonbb

Other answers cover why parameters are important, but there is a downside! In .net, there are several methods for creating parameters (Add, AddWithValue), but they all require you to worry, needlessly, about the parameter name, and they all reduce the readability of the SQL in the code. Right when you're trying to meditate on the SQL, you need to hunt around above or below to see what value has been used in the parameter.

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

I humbly claim my little SqlBuilder class is the most elegant way to write parameterized queries. Your code will look like this...

我谦虚地声称我的小 SqlBuilder 类是编写参数化查询的最优雅的方式。你的代码看起来像这样......

C#

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();

Your code will be shorter and much more readable. You don't even need extra lines, and, when you're reading back, you don't need to hunt around for the value of parameters. The class you need is here...

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

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();
}
}

回答by Arvin Amir

Old post but wanted to ensure newcomers are aware of Stored procedures.

旧帖子但想确保新人了解存储过程。

My 10￠ worth here is that if you are able to write your SQL statement as a stored procedure, that in my view is the optimum approach. I ALWAYSuse stored procs and never loop through records in my main code. For Example: SQL Table > SQL Stored Procedures > IIS/Dot.NET > Class.

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

When you use stored procedures, you can restrict the user to EXECUTEpermission only, thus reducing security risks.

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

Your stored procedure is inherently paramerised, and you can specify input and output parameters.

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

The stored procedure (if it returns data via SELECTstatement) can be accessed and read in the exact same way as you would a regular SELECTstatement in your code.

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

It also runs faster as it is compiled on the SQL Server.

它还运行得更快，因为它是在 SQL Server 上编译的。

Did I also mention you can do multiple steps, e.g. updatea table, check values on another DB server, and then once finally finished, return data to the client, all on the same server, and no interaction with the client. So this is MUCH faster than coding this logic in your code.

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