C# 避免没有参数的 SQL 注入

Avoiding SQL injection without parameters

提问by Rune Grimstad

We are having another discussion here at work about using parametrized sql queries in our code. We have two sides in the discussion: Me and some others that say we should always use parameters to safeguard against sql injections and the other guys that don't think it is necessary. Instead they want to replace single apostrophes with two apostrophes in all strings to avoid sql injections. Our databases are all running Sql Server 2005 or 2008 and our code base is running on .NET framework 2.0.

我们正在这里进行另一个关于在我们的代码中使用参数化 sql 查询的讨论。我们有两个方面的讨论：我和其他一些人说我们应该始终使用参数来防止 sql 注入，而其他人则认为没有必要。相反，他们希望在所有字符串中用两个撇号替换单个撇号，以避免 sql 注入。我们的数据库都运行 Sql Server 2005 或 2008，我们的代码库运行在 .NET framework 2.0 上。

Let me give you a simple example in C#:

让我给你一个简单的 C# 例子：

I want us to use this:

我希望我们使用这个：

string sql = "SELECT * FROM Users WHERE Name=@name";
SqlCommand getUser = new SqlCommand(sql, connection);
getUser.Parameters.AddWithValue("@name", userName);
//... blabla - do something here, this is safe

While the other guys want to do this:

而其他人想要这样做：

string sql = "SELECT * FROM Users WHERE Name=" + SafeDBString(name);
SqlCommand getUser = new SqlCommand(sql, connection);
//... blabla - are we safe now?

Where the SafeDBString function is defined as follows:

其中 SafeDBString 函数定义如下：

string SafeDBString(string inputValue) 
{
    return "'" + inputValue.Replace("'", "''") + "'";
}

Now, as long as we use SafeDBString on all string values in our queries we should be safe. Right?

现在，只要我们在查询中的所有字符串值上使用 SafeDBString，我们就应该是安全的。对？

There are two reasons to use the SafeDBString function. First, it is the way it has been done since the stone ages, and second, it is easier to debug the sql statements since you see the excact query that is run on the database.

使用 SafeDBString 函数有两个原因。首先，这是自石器时代以来的做法，其次，由于您看到在数据库上运行的 excact 查询，因此更容易调试 sql 语句。

So then. My question is whether it really is enough to use the SafeDBString function to avoid sql injection attacks. I have been trying to find examples of code that breaks this safety measure, but I can't find any examples of it.

那么。我的问题是使用SafeDBString函数来避免sql注入攻击是否真的足够了。我一直在尝试查找破坏此安全措施的代码示例，但找不到任何示例。

Is there anybody out there that can break this? How would you do it?

有没有人可以打破这个？你会怎么做？

EDIT:To summarize the replies so far:

编辑：总结到目前为止的答复：

• Nobody has found a way to get around the SafeDBString on Sql Server 2005 or 2008 yet. That is good, I think?
• Several replies pointed out that you get a performance gain when using parametrized queries. The reason is that the query plans can be reused.
• We also agree that using parametrized queries give more readable code that is easier to maintain
• Further it is easier to always use parameters than to use various versions of SafeDBString, string to number conversions and string to date conversions.
• Using parameters you get automatic type conversion, something that is especially useful when we are working with dates or decimal numbers.
• And finally: Don't try to do security yourselfas JulianR wrote. The database vendors spend lots of time and money on security. There is no way we can do better and no reason we should try to do their job.

• 还没有人找到绕过 Sql Server 2005 或 2008 上的 SafeDBString 的方法。那很好，我想？
• 一些回复指出，使用参数化查询时会获得性能提升。原因是查询计划可以重复使用。
• 我们也同意使用参数化查询提供更易于维护的可读代码
• 此外，始终使用参数比使用各种版本的 SafeDBString、字符串到数字转换和字符串到日期转换更容易。
• 使用参数可以获得自动类型转换，这在我们处理日期或十进制数字时特别有用。
• 最后：不要像 JulianR 写的那样尝试自己做安全。数据库供应商在安全性上花费了大量时间和金钱。我们没有办法做得更好，也没有理由尝试完成他们的工作。

So while nobody was able to break the simple security of the SafeDBString function I got lots of other good arguments. Thanks!

因此，虽然没有人能够破坏 SafeDBString 函数的简单安全性，但我得到了许多其他很好的论据。谢谢！

采纳答案by JulianR

I think the correct answer is:

我认为正确答案是：

Don't try to do security yourself. Use whatever trusted, industry standard library there is available for what you're trying to do, rather than tryingto do it yourself. Whatever assumptions you make about security, might be incorrect. As secure as your own approach may look (and it looks shaky at best), there's a risk you're overlooking something and do you really want to take that chance when it comes to security?

不要试图自己做安全。使用任何可信赖的行业标准库来完成您想要做的事情，而不是尝试自己做。无论您对安全性做出何种假设，都可能是不正确的。尽管您自己的方法可能看起来很安全（而且充其量看起来很不稳定），但您可能会忽略某些东西，并且您真的想在安全方面抓住机会吗？

Use parameters.

使用参数。

回答by John Saunders

I'd use stored procedures or functions for everything, so the question wouldn't arise.

我会为所有事情使用存储过程或函数，所以不会出现这个问题。

Where I have to put SQL into code, I use parameters, which is the only thing that makes sense. Remind the dissenters that there are hackers smarter than they are, and with better incentive to break the code that's trying to outsmart them. Using parameters, it's simply not possible, and it's not like it's difficult.

在我必须将 SQL 放入代码的地方，我使用参数，这是唯一有意义的事情。提醒持不同政见者，有些黑客比他们更聪明，并且更有动力去破解试图超越他们的代码。使用参数，根本不可能，也不难。

回答by RedBlueThing

I have used both approaches to avoid SQL injection attacks and definitely prefer parametrized queries. When I have used concatenated queries I have used a library function to escape the variables (like mysql_real_escape_string) and wouldn't be confident I have covered everything in a proprietary implementation (as it seems you are too).

我已经使用这两种方法来避免 SQL 注入攻击，并且绝对更喜欢参数化查询。当我使用连接查询时，我使用了一个库函数来转义变量（如 mysql_real_escape_string），并且不会确信我已经涵盖了专有实现中的所有内容（因为您似乎也是如此）。

回答by Marc Gravell

And then somebody goes and uses " instead of '. Parameters are, IMO, the only safe way to go.

然后有人去使用“而不是'。参数是，IMO，唯一安全的方法。

It also avoids a lot of i18n issues with dates/numbers; what date is 01/02/03? How much is 123,456? Do your servers (app-server and db-server) agree with each-other?

它还避免了许多日期/数字的 i18n 问题；01/02/03 是什么日期？123,456 是多少？你的服务器（app-server 和 db-server）是否相互一致？

If the risk factor isn't convincing to them, how about performance? The RDBMS can re-use the query plan if you use parameters, helping performance. It can't do this with just the string.

如果风险因素不能令他们信服，那么绩效如何？如果使用参数，RDBMS 可以重用查询计划，从而提高性能。它不能只用字符串来做到这一点。

回答by Joel Coehoorn

First of all, your sample for the "Replace" version is wrong. You need to put apostrophes around the text:

首先，您的“替换”版本示例是错误的。您需要在文本周围放置撇号：

string sql = "SELECT * FROM Users WHERE Name='" + SafeDBString(name) & "'";
SqlCommand getUser = new SqlCommand(sql, connection);

So that's one other thing parameters do for you: you don't need to worry about whether or not a value needs to be enclosed in quotes. Of course, you could build that into the function, but then you need to add a lot of complexity to the function: how to know the difference between 'NULL' as null and 'NULL' as just a string, or between a number and a string that just happens to contain a lot of digits. It's just another source for bugs.

所以这是参数为您做的另一件事：您无需担心值是否需要用引号括起来。当然，您可以将其构建到函数中，但随后您需要为函数添加很多复杂性：如何知道 'NULL' 作为 null 和 'NULL' 作为字符串之间的区别，或者数字和一个恰好包含很多数字的字符串。这只是错误的另一个来源。

Another thing is performance: parameterized query plans are often cached better than concatenated plans, thus perhaps saving the server a step when running the query.

另一件事是性能：参数化查询计划通常比串联计划更好地缓存，因此在运行查询时可能会为服务器节省一个步骤。

Additionally, escaping single quotes isn't good enough.Many DB products allow alternate methods for escaping characters that an attacker could take advantage of. In MySQL, for example, you can also escape a single quote with a backslash. And so the following "name" value would blow up MySQL with just the SafeDBString()function, because when you double the single quote the first one is still escaped by the backslash, leaving the 2nd one "active":

此外，转义单引号还不够好。许多 DB 产品允许使用替代方法来转义攻击者可以利用的字符。例如，在 MySQL 中，您还可以使用反斜杠转义单引号。因此，以下“名称”值将仅使用该SafeDBString()函数就炸毁 MySQL ，因为当您将单引号加倍时，第一个仍然会被反斜杠转义，而第二个是“活动的”：

x\' OR 1=1;--

x\' 或 1=1;--

---

---

Also, JulianR brings up a good point below: NEVERtry to do security work yourself. It's so easy to get security programming wrong in subtle ways that appearto work, even with thorough testing. Then time passes and a year later your find out your system was cracked six months ago and you never even knew it until just then.

此外，JulianR 在下面提出了一个很好的观点： 永远不要尝试自己做安全工作。即使经过彻底的测试，安全编程也很容易以看似有效的微妙方式出错。然后时间过去了，一年后你发现你的系统在六个月前被破解了，直到那时你才知道它。

Always rely as much as possible on the security libraries provided for your platform. They will be written by people who do security code for a living, much better tested than what you can manage, and serviced by the vendor if a vulnerability is found.

始终尽可能依赖为您的平台提供的安全库。它们将由以编写安全代码为生的人编写，比您可以管理的测试要好得多，如果发现漏洞，则由供应商提供服务。

回答by Steve Willcock

With parameterised queries you get more than protection against sql injection. You also get better execution plan caching potential. If you use the sql server query profiler you can still see the 'exact sql that is run on the database' so you're not really losing anything in terms of debugging your sql statements either.

使用参数化查询，您获得的不仅仅是针对 sql 注入的保护。您还可以获得更好的执行计划缓存潜力。如果您使用 sql server 查询分析器，您仍然可以看到“在数据库上运行的确切 sql”，因此您在调试 sql 语句方面也不会真正丢失任何东西。

回答by Tim Scarborough

You aren't able to easily do any type checking of the user input without using parameters.

如果不使用参数，您将无法轻松地对用户输入进行任何类型检查。

If you use the SQLCommand and SQLParameter classes to make you're DB calls, you can still see the SQL query that's being executed. Look at the SQLCommand's CommandText property.

如果您使用 SQLCommand 和 SQLParameter 类进行数据库调用，您仍然可以看到正在执行的 SQL 查询。查看 SQLCommand 的 CommandText 属性。

I'm always a litle suspect of the roll-your-own approach to preventing SQL injection when parameterized queries are so easy to use. Second, just because "it's always been done that way" doesn't mean it's the right way to do it.

当参数化查询如此易于使用时，我总是有点怀疑自己的方法来防止 SQL 注入。其次，仅仅因为“一直都是这样做的”并不意味着这是正确的做法。

回答by quamrana

From the very short time I've had to investigate SQL injection problems, I can see that making a value 'safe' also means that you're shutting the door to situations where you might actually want apostrophes in your data - what about someone's name, eg O'Reilly.

从我不得不调查 SQL 注入问题的很短的时间里，我可以看到，使值“安全”也意味着您正在关闭您可能真正想要在数据中使用撇号的情况的大门 - 某人的名字怎么样，例如奥莱利。

That leaves parameters and stored procedures.

这留下了参数和存储过程。

And yes, you should always try to implement code in the best way you know now - not just how its always been done.

是的，您应该始终尝试以您现在知道的最佳方式来实现代码 - 而不仅仅是它一直是如何完成的。

回答by joshcomley

This is only safe if you're guaranteed that you're going to pass in a string.

只有当你保证你要传入一个字符串时，这才是安全的。

What if you're not passing in a string at some point? What if you pass just a number?

如果您在某个时候没有传入字符串怎么办？如果你只传递一个数字怎么办？

http://www.mywebsite.com/profile/?id=7;DROP DATABASE DB

Would ultimately become:

最终会变成：

SELECT * FROM DB WHERE Id = 7;DROP DATABASE DB

回答by HLGEM

Here are a couple of articles that you might find helpful in convincing your co-workers.

这里有几篇文章，您可能会发现它们有助于说服您的同事。

http://www.sommarskog.se/dynamic_sql.html

http://www.sommarskog.se/dynamic_sql.html

http://unixwiz.net/techtips/sql-injection.html

http://unixwiz.net/techtips/sql-injection.html

Personally I prefer to never allow any dynamic code to touch my database, requiring all contact to be through sps (and not one which use dynamic SQl). This means nothing excpt what I have given users permission to do can be done and that internal users (except the very few with production access for admin purposes) cannot directly access my tables and create havoc, steal data or commit fraud. If you run a financial application, this is the safest way to go.

就我个人而言，我更喜欢永远不允许任何动态代码接触我的数据库，要求所有联系都通过 sps（而不是使用动态 SQl 的那个）。这意味着除了我授予用户权限之外的任何事情都无法完成，并且内部用户（除了极少数出于管理目的具有生产访问权限的用户）无法直接访问我的表并造成破坏、窃取数据或进行欺诈。如果您运行财务应用程序，这是最安全的方法。

回答by Darren Greaves

Agree hugely on the security issues.
Another reason to use parameters is for efficiency.

在安全问题上非常同意。
使用参数的另一个原因是为了效率。

Databases will always compile your query and cache it, then re-use the cached query (which is obviously faster for subsequent requests). If you use parameters then even if you use different parameters the database will re-use your cached query as it matches based on the SQL string before binding the parameters.

数据库将始终编译您的查询并将其缓存，然后重新使用缓存的查询（对于后续请求显然更快）。如果您使用参数，那么即使您使用不同的参数，数据库也会重新使用您的缓存查询，因为它会在绑定参数之前根据 SQL 字符串进行匹配。

If however you don't bind parameters then the SQL string changes on every request (that has different parameters) and it will never match what's in your cache.

但是，如果您不绑定参数，则 SQL 字符串在每个请求（具有不同的参数）上都会更改，并且永远不会与缓存中的内容匹配。