避免SQL注入不带参数 [英] Avoiding SQL injection without parameters

问题描述

我们在这里有另一个讨论在工作中有关我们的code。使用参数化的SQL查询。我们在讨论两个方面：我和其他一些人认为说我们应该总是使用参数，以防止SQL注入，并且不认为这是必要的其他球员。相反，他们希望在所有字符串两个撇号，以取代单引号，以避免SQL注入。我们的数据库是所有正在运行的2005或2008年，我们的code基上的.NET framework 2.0运行SQL Server。

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.

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

Let me give you a simple example in 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?

凡SafeDBString函数定义如下：

Where the SafeDBString function is defined as follows:

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

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

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

有使用SafeDBString功能两方面的原因。首先，它是自石器时代已经做的方式，二是很容易，因为你看到的是数据库上运行excact查询调试SQL语句。

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功能，避免SQL注入攻击。我一直在试图找到code，打破这种安全措施的例子，但我找不到它的任何实例。

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.

有没有人在那里，可以打破这个？你会怎么做呢？

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

编辑：
总结的答复迄今：

To summarize the replies so far:

• 没有人已经找到了一种方法来解决SQL Server上的SafeDBString 2005或2008呢。那好，我觉得呢？


• 一些答复指出，在使用参数化查询时获得的性能增益。其原因是，该查询计划可以重复使用。


• 我们还同意，使用参数化查询提供更多的可读性code，它更容易维护


• 此外它更容易总是使用参数，而不是使用各种版本的SafeDBString，串号的转换和字符串到日期的转换。


• 使用参数，你会得到自动类型转换，而这是非常有用的，当我们与日期或十进制数的工作。


• 最后：<一href=\"http://stackoverflow.com/questions/910465/avoiding-sql-injection-without-parameters/910694#910694\">Don't尝试自己做安全作为写JulianR。数据库厂商花费在安全大量的时间和金钱。没有办法，我们可以做的更好，没有理由，我们应该尽量做好自己的工作。

• 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 yourself as 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.

所以，尽管没有人能够打破SafeDBString功能的简单的安全我有很多其他很好的理由。谢谢！

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

推荐答案

我认为正确的答案是：

不要试图做保障自己。使用任何可信的，行业标准库没有可供你想要做什么，而不是的尝试的做自己。您作出任何安全的假设，可能是不正确的。由于安全作为自己的方法看起来（和它看起来摇摇欲坠最好），还有就是你忽视的东西危险，并且你真的想利用这样的机会，当涉及到安全性？

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 trying to 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?

使用的参数。

这篇关于避免SQL注入不带参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！