为什么要使用的SqlParameter []，而不是嵌入参数？ [英] Why use SqlParameter[] and not embed parameters?

问题描述

我有一个提供SQLHelper类，包含一个重载的ExecuteNonQuery：一是只用一个参数（CommandText中），另一种用两个参数（CommandText中，的SqlParameter []）

I have a sqlhelper class that includes an overloaded ExecuteNonQuery: one with just one parameter (commandText) and another one with two parameters (commandText, SqlParameter[]).

假设我有没有用户交互的独立的控制台应用程序，我会打电话给一个存储过程，将只与3个参数更新表，什么是使用的SqlParameter []如果我可以很轻松地构建字符串的好处？而只是把它作为CommandText中

Assuming I have a stand-alone console application with no user interaction, and I will call a stored procedure that will just update a table with 3 parameters, what are the benefits of using SqlParameter[] if I can just as easily build the string and just send it as commandText?

在换句话说，为什么使用以下命令：

In other words, why use the following:

SqlParameter[] parameters =
    {    
        new SqlParameter("parm1" SqlDbType.VarChar, 3),
        new SqlParameter("parm2", SqlDbType.VarChar, 8),
        new SqlParameter("parm3", SqlDbType.VarChar, 2),
        new SqlParameter("parm4", SqlDbType.VarChar, 4)
    };

parameters[0].Value = p1;
parameters[1].Value = p2;
parameters[2].Value = p3;
parameters[3].Value = p4;

当我可以使用这样的：

strQueryToRun = string.Format("exec updateTable {0}, {1}, {2}, {3}", p1, p2, p3, p4);

这是一个独立的控制台应用程序，所以没有SQL注入的可能性。

This is a stand-alone console application so there's no possibility of sql injection.

感谢。

推荐答案

第一，绝对最重要的原因是为了让您的查询呢你的期望它做的，不是什么人的恶意使得它的事情。看一看在 SQL注入维基百科的文章。

The first and absolutely most important reason is so that your query does what you expect it to do, not what someone maliciously makes it do. Have a look at the Wikipedia article on SQL Injection.

在除了缓解（有效消除）SQL注入的风险，使用的参数也允许SQL Server利用高速缓存的查询计划的优势。这是不太你的特定实例的一个问题（如你只是调用存储过程，几乎可以肯定已经编译和缓存的计划），但是这就是为什么你需要参数查询更一般的原因。

In addition to mitigating (effectively eliminating) the risk of SQL injection, using parameters also allows SQL Server to take advantage of cached query plans. This is less of an issue in your specific instance (where you're simply calling a stored procedure, whose plan is almost certainly already compiled and cached), but this is a more general reason why you need to parameterize your queries.

另外一个原因（如在另一个答案指出，由阿里）是使用的String.Format 方法是要给你无论参数字符串表示的是原生的.NET类型。为数字，这不是一个问题。对于字符串类型，你就必须用单引号括并正确逃生的所有嵌入式引号（可能其他消毒程序）。使用参数让SQL客户端库担心数据如何被传递到服务器。

Another reason (as pointed out by Ali in another answer) is that using the string.Format method is going to give your parameters whatever the string representation is of the native .NET type. For numerics, this is not an issue. For string types, you would have to enclose in single quotes and properly escape any embedded quotes (and likely other sanitizing routines). Using the parameter lets the SQL client libraries worry about how the data gets passed to the server.

这是说，我不会用，你在上面写它的代码。我不会建的SqlParameter 的数组■在所有。有多种方式将参数添加到的SqlCommand （或的DbCommand 或你使用任何）如 AddWithValue ，它提供了更简洁的机制足以为得到补充大多数参数。

That said, I would not use the code as you have written it above. I wouldn't construct an array of SqlParameters at all. There are a variety of ways to add a parameter to a SqlCommand (or DbCommand or whatever you're using), such as AddWithValue that provides a less verbose mechanism that is sufficient for most parameters that get added.

即使忽略 AddWithValue ，我仍然会创建单独的变量，每个参数并将它们命名为有意义的事。

Even ignoring AddWithValue, I would still create individual variables for each parameter and name them something meaningful.

var parm1 = new SqlParameter("parm1", SqlDbType.VarChar, 3);
var parm2 = new SqlParameter("parm2", SqlDbType.VarChar, 8);
var parm3 = new SqlParameter("parm3", SqlDbType.VarChar, 2);
var parm4 = new SqlParameter("parm4", SqlDbType.VarChar, 4);

parm1.Value = p1;
parm2.Value = p2;
parm3.Value = p3;
parm4.Value = p4;

（显然，像 PARM1 的名称或 parm2 是远远有意义的，但我会假设你的实际的参数名称有更多的意义比你的例子）

(Obviously a name like parm1 or parm2 is far from meaningful, but I would assume that your actual parameter names have more meaning than your example)

这篇关于为什么要使用的SqlParameter []，而不是嵌入参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！