.net SQLParameter 如何防止 SQL 注入？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4892166/
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
How does SQLParameter prevent SQL Injection?
提问by Abe Miessler
What exactly is going on in the background that makes it so SQLParameterprevents SQL Inection attacks in a .NET Parameterized query? Is it just stripping out any suspect characters or is there something more to it?
在后台究竟发生了什么，使得SQLParameter可以防止 .NET 参数化查询中的 SQL Inection 攻击？它只是去除了任何可疑字符还是还有更多内容？
Has anyone out there checked to see what actually gets to SQL Server when you pass malicious input?
有没有人检查过当您传递恶意输入时实际到达 SQL Server 的内容？
Related: Can you use a SQLParameter in the SQL FROM statement?
回答by KeithS
Basically, when you perform a SQLCommandusing SQLParameters, the parameters are never inserted directly into the statement. Instead, a system stored procedure called sp_executesqlis called and given the SQL string and the array of parameters.
基本上，当您执行SQLCommandusing 时SQLParameters，参数永远不会直接插入到语句中。相反，系统存储过程sp_executesql被调用并给出 SQL 字符串和参数数组。
When used as such, the parameters are isolated and treated as data, instead of having to be parsed out of the statement (and thus possibly changing it), so what the parameters contain can never be "executed". You'll just get a big fat error that the parameter value is invalid in some way.
当这样使用时，参数被隔离并被视为数据，而不必从语句中解析出来（因此可能会更改它），因此参数包含的内容永远不会被“执行”。你只会得到一个很大的错误，参数值在某种程度上是无效的。
回答by anaik
A easier-to-understand, and a more general answer goes like this:
一个更容易理解，更通用的答案是这样的：
Imagine a dynamic SQL query:
想象一个动态 SQL 查询：
sqlQuery='SELECT * FROM custTable WHERE User=' + Username + ' AND Pass=' + password
sqlQuery='SELECT * FROM custTable WHERE User=' + Username + ' AND Pass=' + password
A simple SQL injection would be just to put the Username in as ' OR 1=1--
一个简单的 SQL 注入就是将用户名输入为 ' OR 1=1--
This would effectively make the SQL query:
这将有效地进行 SQL 查询：
sqlQuery='SELECT * FROM custTable WHERE User='' OR 1=1-- ' AND PASS=' + password
sqlQuery='SELECT * FROM custTable WHERE User='' OR 1=1-- ' AND PASS=' + password
This says select all customers where their username is blank ('') or 1=1, which is a boolean, equating to true. It then uses --to comment out the rest of the query. So this will print out the entirecustomer table, or enable you to do whatever you want with it.
这表示选择用户名为空 ( '') 或 的所有客户1=1，这是一个布尔值，等于 true。然后它--用于注释掉查询的其余部分。因此，这将打印出整个客户表，或者使您能够对其进行任何操作。
Now parameterized queries do it differently, with code like:
现在参数化查询的做法有所不同，代码如下：
sqlQuery='SELECT * FROM custTable WHERE User=? AND Pass=?'
parameters.add("User", username)
parameters.add("Pass", password)
sqlQuery='SELECT * FROM custTable WHERE User=? AND Pass=?'
parameters.add("User", username)
parameters.add("Pass", password)
where username and password are variables pointing to the associated inputed username and password.
其中 username 和 password 是指向关联的输入用户名和密码的变量。
Now at this point, you may think, this doesn't change anything at all. Surely you could still just put into the username field something like Nobody OR 1=1'--, effectively making the query:
现在，您可能会认为，这根本不会改变任何事情。当然，您仍然可以在用户名字段中输入类似Nobody OR 1=1'-- 的内容，从而有效地进行查询：
sqlQuery='SELECT * FROM custTable WHERE User=Nobody OR 1=1'-- AND Pass=?'
sqlQuery='SELECT * FROM custTable WHERE User=Nobody OR 1=1'-- AND Pass=?'
And this would seem like a valid argument. But, you would be wrong.
这似乎是一个有效的论点。但是，你错了。
The way parameterized queries work, is that the SQL query is sent as a query, and the database knows exactly what this query will do, and only then will it insert the username and passwords merely as values. This means they cannot affect the query, because the database already knows what the query will do. So in this case it would look for a username of Nobody OR 1=1'--and a blank password, which should come up false.
参数化查询的工作方式是将 SQL 查询作为查询发送，数据库确切地知道该查询将执行什么操作，然后才将用户名和密码仅作为值插入。这意味着它们不能影响查询，因为数据库已经知道查询将做什么。所以在这种情况下，它会寻找用户名Nobody OR 1=1'--和空白密码，这应该是错误的。
This isn't a complete solution though, and input validation will still need to be done, since this won't affect other problems, such as xssattacks, as you could still put javascript into the database. Then if this is read out onto a page, it would display it as normal javascript, depending on any output validation. So really the best thing to do is still use input validation, but using parameterized queries or stored procedures to stop any SQL attacks.
但这不是一个完整的解决方案，输入验证仍然需要完成，因为这不会影响其他问题，例如xss攻击，因为您仍然可以将 javascript 放入数据库中。然后，如果将其读出到页面上，它将根据任何输出验证将其显示为普通的 javascript。所以真正最好的做法是仍然使用输入验证，但使用参数化查询或存储过程来阻止任何 SQL 攻击。
Source: http://www.lavamunky.com/2011/11/why-parameterized-queries-stop-sql.html
来源：http: //www.lavamunky.com/2011/11/why-parameterized-queries-stop-sql.html
回答by Dave Brace
"Parameter collections such as SqlParameterCollection provide type checking and length validation. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code. An additional benefit of using a parameters collection is that you can enforce type and length checks. Values outside of the range trigger an exception. This is a good example of defense in depth."
“SqlParameterCollection 等参数集合提供类型检查和长度验证。如果使用参数集合，输入将被视为文字值，而 SQL Server 不会将其视为可执行代码。使用参数集合的另一个好处是可以强制执行类型和长度检查。超出范围的值会触发异常。这是纵深防御的一个很好的例子。”
回答by MatthewMartin
When using parameterized queries, the attack surface is reduced to monkeying around with the parameters.
使用参数化查询时，攻击面会减少到随意使用参数。
Do use SqlParameters, but don't forget about overflow, underflow and unvalidated parameters. For example, if the method is "proc buy_book (@price money)", a malicious attacker would attempt to trick the application to running with @priceset to 0.01, or attempting to get the application to do something interesting by submitting something that causes an overflow. Sql Overflows tend not to be interesting (i.e. they just cause exceptions, you are unlikely to be able to write to adjacent memory)
请使用SqlParameters，但不要忘记上溢、下溢和未验证的参数。例如，如果方法是“proc buy_book (@price money)”，恶意攻击者会试图诱使应用程序以@priceset to运行0.01，或者试图通过提交导致溢出的内容来让应用程序做一些有趣的事情。Sql 溢出往往不有趣（即它们只会导致异常，您不太可能写入相邻的内存）
