在SQL Server中动态创建的SQL vs参数 [英] Dynamically created SQL vs Parameters in SQL Server

问题描述

如果我要从表中选择一行，我基本上有两个选择，或者是这样

If I were to select a row from a table I basically have two options, either like this

int key = some_number_derived_from_a_dropdown_or_whatever
SqlCommand cmd = new SqlCommand("select * from table where primary_key = " + key.ToString());

或使用参数

SqlCommand cmd = new SqlCommand("select * from table where primary_key = @pk");
SqlParameter param  = new SqlParameter();
param.ParameterName = "@pk";
param.Value         = some_number_derived_from_a_dropdown_or_whatever;
cmd.Parameters.Add(param);

现在，我知道第一种方法因为可能的sql注入攻击而皱眉了，但是在这种情况下该参数是整数，因此实际上不可能注入恶意代码。

Now, I know the first method is frowned upon because of possible sql injection attacks, but in this case the parameter is a integer and thus should not really be possible to inject malicious code.

我的问题是：您在生产代码中使用选项1是因为您考虑使用由于易于使用和对所插入参数的控制（例如以上所述，或者该参数是在代码中创建的），是否安全？还是无论如何总是使用参数？参数100％注入安全吗？

My question is this: Do you use option 1 in production code because you consider the use safe because of ease of use and control over the inserted parameter (like the above, or if the parameter is created in code)? Or do you always use parameters no matter what? Are parameters 100% injection safe ?

推荐答案

我将跳过SQL注入参数，这是众所周知的，只是关注

I'll skip the SQL Injection argument, that is just too well known and just focus on the SQL aspect of parameters vs. non parameters.

当您将SQL批处理发送到服务器时，任何批处理都必须对其进行解析才能理解。与其他任何编译器一样，SQL编译器必须从文本中生成 AST ，然后对其进行操作语法树。最终，优化器将语法树转换为执行树，最后生成一个执行计划，该计划实际上已经运行。早在1995年左右的黑暗年代，如果批处理是一个即席查询或存储过程，它就有所不同，但是今天，它绝对没有使它们完全一样。

When you send a SQL batch to the server, any batch, it has to be parsed to be comprehended. Like any other compiler, the SQL compiler has to produce an AST from the text and then operate on the syntax tree. Ultimately the optimizer transforms the syntax tree into an execution tree and finally produces an execution plan and that is actually run. Back in the dark ages of circa 1995 it made a difference if the batch was an Ad-Hoc query or a stored procedure, but today it makes absolutely none, they all the same.

现在参数有所不同的地方是，客户端发送的查询如 select * from table from primary_key = @pk 会发送完全相同的SQL文本每次，无论感兴趣的是什么值。然后发生的事情就是上述的 entire 过程发生了短路。 SQL将在内存中搜索执行计划以查找其接收到的原始，未分析的文本（基于输入的哈希摘要），如果找到，则将执行该计划。这意味着没有解析，没有优化，什么也没有，该批处理直接执行。在每秒运行成千上万个小请求的OLTP系统上，这种快速路径会带来巨大的性能差异。

Now where parameters make a difference is that a client that sends a query like select * from table where primary_key = @pk will send exactly the same SQL text every time, no matter what value is interested in. What happens then is that the entire process I described above is short-circuited. SQL will search in memory an execution plan for the raw, unparsed, text it received (based on a hash digest of the input) and, if found, will execute that plan. That means no parsing, no optimization, nothing, the batch goes straight into execution. On OLTP systems that run hundreds and thousands of small requests every second, this fast path makes a huge performance difference.

如果您以<$ c $的形式发送查询c>从其中primary_key = 1 的表中选择*，然后SQL将至少必须解析它以了解文本中的内容，因为文本可能是新的文本，不同于它看到的任何先前批处理（甚至像 1 与 2 之类的单个字符也会使整个批次不同）。然后，它将对结果语法树进行操作，并尝试一个名为简单参数化。如果查询可以自动参数化，则SQL可能会从以前使用其他pk值运行的其他查询中为其找到一个缓存的执行计划，然后重用该计划，因此至少不需要优化查询，您可以跳过该查询。生成实际执行计划的步骤。但这绝不是实现完整的短路，这是通过真正的客户端参数化查询实现的最短路径。

If you send the query in the form select * from table where primary_key = 1 then SQL will have to at least parse it to understand what is inside the text, since the text is likely a new one, different from any previous batch it seen (even a single character like 1 vs. 2 makes the entire batch different). It will then operate on the resulted syntax tree and attempt a process called Simple Parameterisation. If the query can be auto-paremeterized, then SQL will likely find a cached execution plan for it from other queries that run previously with other pk values and reuse that plan, so at least your query does not need to be optimized and you skip the step of generating an actual execution plan. But by no mean did you achieve the complete short-circuit, the shortest possible path you achieve with a true client parameterized query.

您可以查看服务器的SQL Server，SQL统计对象性能计数器。计数器 Auto-Param Attempts / sec 每秒将显示多次，SQL必须将收到的不带参数的查询转换为自动参数化的查询。如果在客户端中正确地设置了查询参数，则可以避免每次尝试。如果您也有大量的自动参数失败/秒甚至更严重，这意味着查询将进入优化和执行计划生成的整个周期。

You can look into the SQL Server, SQL Statistics Object performance counter of your server. The counter Auto-Param Attempts/sec will show many times per second SQL has to translate a query received without parameters into an auto-parameterized one. Each attempt can be avoided if you properly parameterize the query in the client. If you also have a high number of Failed Auto-Params/sec is even worse, it means the queries are going the full cycle of optimization and execution plan generation.

这篇关于在SQL Server中动态创建的SQL vs参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！