C# 参数绑定：幕后发生了什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14934/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Parameter Binding: What happens under the hood?
提问by
.NET, Java and other high level database API's in various language often provide techniques known as prepared statements and parameter binding as opposed to sending plain text commands to the Database server. What I would like to know is what happens when you execute a statement like this:
.NET、Java 和其他各种语言的高级数据库 API 通常提供称为准备语句和参数绑定的技术，而不是向数据库服务器发送纯文本命令。我想知道的是当你执行这样的语句时会发生什么：
SqlCommand cmd = new SqlCommand("GetMemberByID");
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@ID", memberID);
para.DbType = DbType.Integer;
cmd.Parameters.Add(param);
I know this is a best practice. SQL injection attacks are minimized this way. But what exactly happens under the hood when you execute these statements? Is the end result still a SQL safe string? If not, what is the end result? And is this enough to prevent SQL injection attacks?
我知道这是最佳实践。SQL 注入攻击以这种方式被最小化。但是当你执行这些语句时到底发生了什么？最终结果仍然是 SQL 安全字符串吗？如果没有，最终的结果是什么？这足以防止 SQL 注入攻击吗？
采纳答案by Eli Courtwright
The MySQL manual pageon prepared statements provides lots of information (which should apply to any other RDBMS).
准备好的语句的 MySQL 手册页提供了大量信息（应该适用于任何其他 RDBMS）。
Basically, your statement is parsed and processed ahead of time, and the parameters are sent separately instead of being handled along with the SQL code. This eliminates SQL-injection attacks because the SQL is parsed before the parameters are even set.
基本上，您的语句会被提前解析和处理，并且参数是单独发送的，而不是与 SQL 代码一起处理。这消除了 SQL 注入攻击，因为在设置参数之前就已解析 SQL。
回答by Kev
If you're using MS SQL, load up the profiler and you'll see what SQL statements are generated when you use parameterised queries. Here's an example (I'm using Enterprise Libary 3.1, but the results are the same using SqlParameters directly) against SQL Server 2005:
如果您使用 MS SQL，请加载分析器，您将看到使用参数化查询时生成的 SQL 语句。下面是一个针对 SQL Server 2005 的示例（我使用的是 Enterprise Libary 3.1，但直接使用 SqlParameters 的结果是一样的）：
string sql = "SELECT * FROM tblDomains WHERE DomainName = @DomName AND DomainID = @Did";
Database db = DatabaseFactory.CreateDatabase();
using(DbCommand cmd = db.GetSqlStringCommand(sql))
{
db.AddInParameter(cmd, "DomName", DbType.String, "xxxxx.net");
db.AddInParameter(cmd, "Did", DbType.Int32, 500204);
DataSet ds = db.ExecuteDataSet(cmd);
}
This generates:
这会产生：
exec sp[underscore]executesql N'SELECT * FROM tblDomains WHERE DomainName = @DomName AND DomainID = @Did',
N'@DomName nvarchar(9),
@Did int',
@DomName=N'xxxxx.net',
@Did=500204
You can also see here, if quotation characters were passed as parameters, they are escaped accordingly:
您还可以在这里看到，如果引号字符作为参数传递，它们会相应地进行转义：
db.AddInParameter(cmd, "DomName", DbType.String, "'xxxxx.net");
exec sp[underscore]executesql N'SELECT * FROM tblDomains WHERE DomainName = @DomName AND DomainID = @Did',
N'@DomName nvarchar(10),
@Did int',
@DomName=N'''xxxxx.net',
@Did=500204
回答by SQLMenace
in layman terms: if a prepared statement is sent then the DB will use a plan if it is available, it doesn't not have to recreate a plan every time this query is sent over but only the values of the params have changed. this is very similar to how procs work, the additional benefit with procs is that you can give permission through procs only and not to the underlying tables at all
通俗地说：如果发送了一个准备好的语句，那么数据库将使用一个可用的计划，它不必在每次发送此查询时重新创建一个计划，但只有参数的值发生了变化。这与 procs 的工作方式非常相似，procs 的额外好处是您只能通过 procs 授予权限，而根本不能授予基础表
