参数真的足以防止 Sql 注入吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/306668/
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
Are Parameters really enough to prevent Sql injections?
提问by Rune Grimstad
I've been preaching both to my colleagues and here on SO about the goodness of using parameters in SQL queries, especially in .NET applications. I've even gone so far as to promise them as giving immunity against SQL injection attacks.
我一直在向我的同事和这里宣扬在 SQL 查询中使用参数的好处，尤其是在 .NET 应用程序中。我什至承诺他们对 SQL 注入攻击具有免疫力。
But I'm starting to wonder if this really is true. Are there any known SQL injection attacks that will be successfull against a parameterized query? Can you for example send a string that causes a buffer overflow on the server?
但我开始怀疑这是否真的是真的。是否有任何已知的 SQL 注入攻击可以成功针对参数化查询？例如，您可以发送一个导致服务器缓冲区溢出的字符串吗？
There are of course other considerations to make to ensure that a web application is safe (like sanitizing user input and all that stuff) but now I am thinking of SQL injections. I'm especially interested in attacks against MsSQL 2005 and 2008 since they are my primary databases, but all databases are interesting.
当然还有其他考虑因素可以确保 Web 应用程序是安全的（例如清理用户输入和所有这些东西），但现在我正在考虑 SQL 注入。我对针对 MsSQL 2005 和 2008 的攻击特别感兴趣，因为它们是我的主要数据库，但所有数据库都很有趣。
Edit: To clarify what I mean by parameters and parameterized queries. By using parameters I mean using "variables" instead of building the sql query in a string.
So instead of doing this:
编辑：澄清我所说的参数和参数化查询的含义。通过使用参数，我的意思是使用“变量”而不是在字符串中构建 sql 查询。
所以不要这样做：
SELECT * FROM Table WHERE Name = 'a name'
We do this:
我们这样做：
SELECT * FROM Table WHERE Name = @Name
and then set the value of the @Name parameter on the query / command object.
然后在查询/命令对象上设置@Name 参数的值。
采纳答案by Adam Bellaire
Placeholdersare enough to prevent injections. You might still be open to buffer overflows, but that is a completely different flavor of attack from an SQL injection (the attack vector would not be SQL syntax but binary). Since the parameters passed will all be escaped properly, there isn't any way for an attacker to pass data that will be treated like "live" SQL.
占位符足以防止注射。您可能仍然对缓冲区溢出持开放态度，但这是一种与 SQL 注入完全不同的攻击方式（攻击向量不是 SQL 语法而是二进制）。由于传递的参数都将被正确转义，因此攻击者无法传递将被视为“实时”SQL 的数据。
You can't use functions inside placeholders, and you can't use placeholders as column or table names, because they are escaped and quoted as string literals.
您不能在占位符内使用函数，也不能将占位符用作列名或表名，因为它们被转义并作为字符串文字引用。
However, if you use parametersas part of a string concatenationinside your dynamic query, you are still vulnerable to injection, because your strings will not be escaped but will be literal. Using other types for parameters (such as integer) is safe.
但是，如果您在动态查询中使用参数作为字符串连接的一部分，您仍然容易受到注入，因为您的字符串不会被转义，而是会是文字。使用其他类型的参数（例如整数）是安全的。
That said, if you're using use input to set the value of something like security_level, then someone could just make themselves administrators in your system and have a free-for-all. But that's just basic input validation, and has nothing to do with SQL injection.
也就是说，如果您使用 use input 来设置诸如 之类的东西的值security_level，那么有人可以让自己成为您系统中的管理员并免费使用。但这只是基本的输入验证，与 SQL 注入无关。
回答by Bill Karwin
No, there is still risk of SQL injection any time you interpolate unvalidated data into an SQL query.
不，任何时候您将未经验证的数据插入 SQL 查询时，仍然存在 SQL 注入的风险。
Query parameters help to avoid this risk by separating literal values from the SQL syntax.
查询参数通过将文字值与 SQL 语法分开来帮助避免这种风险。
'SELECT * FROM mytable WHERE colname = ?'
That's fine, but there are other purposes of interpolating data into a dynamic SQL query that cannot use query parameters, because it's not an SQL value but instead a table name, column name, expression, or some other syntax.
这很好，但是将数据插入不能使用查询参数的动态 SQL 查询还有其他目的，因为它不是 SQL 值，而是表名、列名、表达式或其他一些语法。
'SELECT * FROM ' + @tablename + ' WHERE colname IN (' + @comma_list + ')'
' ORDER BY ' + @colname'
It doesn't matter whether you're using stored procedures or executing dynamic SQL queries directly from application code. The risk is still there.
无论您是使用存储过程还是直接从应用程序代码执行动态 SQL 查询都没有关系。风险仍然存在。
The remedy in these cases is to employ FIEOas needed:
在这些情况下，补救措施是根据需要使用FIEO：
Filter Input:validate that the data look like legitimate integers, table names, column names, etc. before you interpolate them.
Escape Output:in this case "output" means putting data into a SQL query. We use functions to transform variables used as string literals in an SQL expression, so that quote marks and other special characters inside the string are escaped. We should also use functions to transform variables that would be used as table names, column names, etc. As for other syntax, like writing whole SQL expressions dynamically, that's a more complex problem.
过滤输入：在插入之前验证数据看起来像合法的整数、表名、列名等。
转义输出：在这种情况下，“输出”意味着将数据放入 SQL 查询中。我们使用函数来转换在 SQL 表达式中用作字符串文字的变量，以便对字符串中的引号和其他特殊字符进行转义。我们还应该使用函数来转换将用作表名、列名等的变量。至于其他语法，例如动态编写整个 SQL 表达式，这是一个更复杂的问题。
回答by HTTP 410
There seems to be some confusion in this thread about the definition of a "parameterised query".
在这个线程中似乎有一些关于“参数化查询”的定义的混淆。
- SQL such as a stored proc that accepts parameters.
- SQL that is called using the DBMS Parameters collection.
- SQL，例如接受参数的存储过程。
- 使用 DBMS 参数集合调用的 SQL。
Given the former definition, many of the links show working attacks.
鉴于前一个定义，许多链接显示有效攻击。
But the "normal" definition is the latter one. Given that definition, I don't know of any SQL injection attack that will work. That doesn't mean that there isn't one, but I have yet to see it.
但“正常”的定义是后者。鉴于该定义，我不知道任何可行的 SQL 注入攻击。这并不意味着没有，但我还没有看到它。
From the comments, I'm not expressing myself clearly enough, so here's an example that will hopefully be clearer:
从评论来看，我表达得不够清楚，所以这里有一个例子，希望能更清楚：
This approach isopen to SQL injection
这种方法对 SQL 注入是开放的
exec dbo.MyStoredProc 'DodgyText'
This approach isn'topen to SQL injection
这种方法对 SQL 注入不开放
using (SqlCommand cmd = new SqlCommand("dbo.MyStoredProc", testConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter newParam = new SqlParameter(paramName, SqlDbType.Varchar);
newParam.Value = "DodgyText";
.....
cmd.Parameters.Add(newParam);
.....
cmd.ExecuteNonQuery();
}
回答by Steven A. Lowe
any sql parameter of string type (varchar, nvarchar, etc) that is used to construct a dynamic query is still vulnerable
用于构造动态查询的任何字符串类型（varchar、nvarchar 等）的 sql 参数仍然容易受到攻击
otherwise the parameter type conversion (e.g. to int, decimal, date, etc.) should eliminate any attempt to inject sql via the parameter
否则参数类型转换（例如到 int、decimal、date 等）应该消除任何通过参数注入 sql 的尝试
EDIT: an example, where parameter @p1 is intended to be a table name
编辑：一个例子，其中参数@p1 是一个表名
create procedure dbo.uspBeAfraidBeVeryAfraid ( @p1 varchar(64) )
AS
SET NOCOUNT ON
declare @sql varchar(512)
set @sql = 'select * from ' + @p1
exec(@sql)
GO
If @p1 is selected from a drop-down list it is a potential sql-injection attack vector;
如果从下拉列表中选择@p1，则它是潜在的 sql 注入攻击向量；
If @p1 is formulated programmatically w/out the ability of the user to intervene then it is not a potential sql-injection attack vector
如果@p1 是以编程方式制定的，没有用户干预的能力，那么它就不是潜在的 sql 注入攻击向量
回答by Blorgbeard is out
A buffer overflow is not SQL injection.
缓冲区溢出不是 SQL 注入。
Parametrized queries guarantee you are safe against SQL injection. They don't guarantee there aren't possible exploits in the form of bugs in your SQL server, but nothing will guarantee that.
参数化查询保证您可以安全地抵御 SQL 注入。他们不保证在您的 SQL 服务器中不会以错误的形式出现漏洞，但没有什么可以保证这一点。
回答by HLGEM
Your data is not safe if you use dynamic sql in any way shape or form because the permissions must be at the table level. Yes you have limited the type and amount of injection attack from that particular query, but not limited the access a user can get if he or she finds a way into the system and you are completely vunerable to internal users accessing what they shouldn't in order to commit fraud or steal personal information to sell. Dynamic SQL of any type is a dangerous practice. If you use non-dynamic stored procs, you can set permissions at the procesdure level and no user can do anything except what is defined by the procs (except system admins of course).
如果您以任何形式或形式使用动态 sql，您的数据是不安全的，因为权限必须在表级别。是的，您已经限制了来自该特定查询的注入攻击的类型和数量，但不限制用户在找到进入系统的方法后可以获得的访问权限，并且您完全不知道内部用户访问他们不应该访问的内容以进行欺诈或窃取个人信息进行出售。任何类型的动态 SQL 都是危险的做法。如果您使用非动态存储过程，您可以在过程级别设置权限，除了 procs 定义的内容（当然系统管理员除外），任何用户都不能做任何事情。
回答by Booji Boy
It is possible for a stored proc to be vulnerable to special types of SQL injection via overflow/truncation, see: Injection Enabled by Data Truncation here:
通过溢出/截断，存储过程可能容易受到特殊类型的 SQL 注入的攻击，请参阅：此处通过数据截断启用注入：
回答by nos
Just remember that with parameters you can easily store the string, or say username if you don't have any policies, "); drop table users; --"
请记住，使用参数您可以轻松存储字符串，或者如果您没有任何策略，请说 username，"); drop table users; --"
This in itself won't cause any harm, but you better know where and how that date is used further on in your application (e.g. stored in a cookie, retrieved later on to do other stuff.
这本身不会造成任何伤害，但您最好知道在您的应用程序中进一步使用该日期的位置和方式（例如，存储在 cookie 中，稍后检索以执行其他操作）。
回答by Mohamed Abbas
You can run dynamic sql as example
您可以运行动态 sql 作为示例
DECLARE @SQL NVARCHAR(4000);
DECLARE @ParameterDefinition NVARCHAR(4000);
SELECT @ParameterDefinition = '@date varchar(10)'
SET @SQL='Select CAST(@date AS DATETIME) Date'
EXEC sp_executeSQL @SQL,@ParameterDefinition,@date='04/15/2011'
