避免无参数的 SQL 注入 [英] Avoiding SQL injection without parameters

问题描述

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

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#:

我希望我们使用这个:

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 函数有两个原因.首先，这是自石器时代以来的方式，其次，由于您看到在数据库上运行的精确查询，因此更容易调试 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 注入攻击.我一直在尝试找到违反此安全措施的代码示例，但我找不到任何示例.

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 2005 或 2008 上的 SafeDBString 的方法.我认为这很好?
• 一些回复指出，使用参数化查询可以提高性能.原因是查询计划可以重复使用.
• 我们也同意，使用参数化查询可以提供更易维护的可读性更高的代码
• 始终使用参数比使用各种版本的 SafeDBString、字符串到数字的转换和字符串到日期的转换更容易.
• 使用参数可以自动进行类型转换，这在我们处理日期或十进制数字时特别有用.
• 最后:不要尝试自己做安全性正如 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屋！