如何避免SQL注入攻击?

Yesterday I was speaking with a developer, and he mentioned something about restricting the insertions on database field, like, strings such as -- (minus minus).

昨天我和一个开发人员谈话，他提到了一些关于限制数据库字段插入的内容，比如，字符串，比如- (--)

At the same type, what I know is that is a good approach to escape HTML chars like <, > etc. Not --. Is this true? Do I have to worry about --, ++? Is it more like a myth or old stuff?

在相同的类型中，我知道这是一个很好的方法来避免HTML字符，比如<，>等等。这是真的吗?我需要担心-，++吗?它更像神话还是古老的东西?

Update

Thanks a lot for all the answers, it's easy to understand like that since I'm kind of new to all of this. Well, to be more specific in this case our discussion was about and C# ASP.NET MVC website we're developing, so there's a complex open an account form in there with important information, so I'm not sure if MVC using Linq to interface with database already comes with this kind of protection or not. So if anyone could provides some hints about it, it would be great. Thanks again

非常感谢所有的答案，很容易理解，因为我对所有这些都很陌生。更具体地说，我们讨论的是c# ASP。我们正在开发的netmvc网站，所以有一个复杂的开帐户表单，里面有重要的信息，所以我不确定使用Linq的MVC是否与数据库连接已经带来了这种保护。如果有人能提供一些线索，那就太好了。再次感谢

6 个解决方案

#1

The proper way to avoid SQL Injection attacks is NOT to simply disallow certain problematic characters, but rather to use parameterized SQL. In short, parameterized SQL prevents the database from executing raw user input as part of the SQL command this prevents user input like "drop table" from being executed. Just escaping characters does not stop all forms of SQL injection attacks and excluding certain words such as "Drop" does not work in all cases; there can be certain fields where "Drop" is a perfectly valid part of the data entry.

避免SQL注入攻击的正确方法不是简单地禁止某些有问题的字符，而是使用参数化SQL。简而言之，参数化SQL阻止数据库执行作为SQL命令的一部分的原始用户输入，这就阻止了像“drop table”这样的用户输入被执行。只是转义字符并不能阻止所有形式的SQL注入攻击，排除“Drop”之类的特定词汇并非在所有情况下都有效;在某些字段中，“Drop”是数据条目的有效部分。

You can find some good articles on the subject of paramaterized SQL here:

您可以在这里找到一些关于副异构SQL主题的好文章:

https://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/

http://www.codeproject.com/KB/database/ParameterizingAdHocSQL.aspx

Now that you mentioned that you are working with ASP.net I can give you some links that deal specifically with SQL Injection in ASP.

既然你已经提到你在使用ASP.net，我可以给你一些链接，特别是在ASP中处理SQL注入。

https://dzone.com/articles/aspnet-preventing-sql-injectio http://www.codeproject.com/KB/aspnet/SQL_Injection_.aspx?msg=3209511

Here is a more general article on making your ASP more secure: http://www.codeproject.com/KB/web-security/Securing_ASP_NET_Apps.aspx

这里有一篇关于使您的ASP更安全的更一般的文章:http://www.codeproject.com/kb/websecurity/securing_asp_net_apps.aspx

And, of course the MSDN article on SQL injection: http://msdn.microsoft.com/en-us/library/ms998271.aspx

当然，MSDN关于SQL注入的文章:http://msdn.microsoft.com/en-us/library/ms998271.aspx

#2

SQL injection is a high security risk for most websites that allow users to squirt parameters into a statement that gets executed on a database.

对于大多数允许用户向在数据库上执行的语句中插入参数的网站来说，SQL注入是一个很高的安全风险。

A simple example would be:

一个简单的例子是:

Input field "Name: _________

输入字段名称:_________

"SELECT * FROM tblCustomer WHERE Name = '" + nameInputField + "'"

So if I type in "Bob" we have

如果我输入"Bob"我们有

"SELECT * FROM tblCustomer WHERE Name = 'Bob'"

But if I type in "'; DROP TABLE tblCustomer", we end up with the rather more sinister

但如果我输入"';“放下桌子tblCustomer”，我们的结局更加险恶

"SELECT * FROM tblCustomer WHERE Name = ''; DROP TABLE tblCustomer"

There are lots of ways to avoid these problems, and many are built into whatever language you are using - so rather than think of all the dodgy possibilities ";", "--", "/*" etc, try and use something that already exists.

有很多方法可以避免这些问题，而且很多都是你所使用的语言所固有的——所以不要去想所有的不可靠的可能性。、“-”、“/*”等等，尝试使用已经存在的东西。

Shout out what language you're using and I'm sure we can tell you how to avoid these attacks.

大声说出你在用什么语言，我相信我们可以告诉你如何避免这些攻击。

#3

Use parameterized queries. These queries represent the variables as a placeholder in the SQL, such as select * from person where name = ?. After creating the SQL query, you set the parameter values in the query. Parameterized queries ensure that whatever was substituted for the placeholder will not be considered as part of the SQL statement.

使用参数化查询。这些查询将变量表示为SQL中的占位符，例如select * from person where name = ?创建SQL查询后，在查询中设置参数值。参数化查询确保任何被替代的占位符都不会被视为SQL语句的一部分。

See Jeff Atwood's article for a good overview of parameterized queries.

有关参数化查询的详细概述，请参阅Jeff Atwood的文章。

#4

He was talking about SQL Injection attacks, as is quite right in what he said.

他谈论的是SQL注入攻击，正如他所说的那样。

The problem is not with such data existing in the database, but in passing input data directly to the database without sanitizing it.

问题不在于数据库中存在这样的数据，而在于将输入数据直接传递给数据库而不进行清理。

Without cleaning it up, if someone passes in a string ending with a ; they can then follow it with anything they want (select * from sys.objects, for example) or something more malicious, like dropping some tables.

如果没有清理它，如果有人通过了以a结尾的字符串;然后，他们可以使用任何他们想要的东西来跟踪它(从sys中选择*)。对象(例如)或更恶意的东西，如删除某些表。

It is difficult to guard against fully, but if you use a good DB library from your code and follow known practices, such as using paremeterized queries you limit the possible damage.

要完全避免这种情况是很困难的，但是如果您从代码中使用一个好的DB库并遵循已知的实践，例如使用pareme查询，那么您将限制可能的损害。

Store as many -- in your database as you want, but do not pass that through to your database without going through a cleanup process (this is where a good DB library is vital - it should cleanup quotes and other potentially harmful input).

存储尽可能多的数据——在您的数据库中，但是不要在不经过清理过程的情况下将其传递到数据库中(这是一个好的DB库的关键所在——它应该清除引号和其他可能有害的输入)。

#5

There's nothing "dangerous" about inserting a string containing -- in a database.

插入包含在数据库中的字符串没有什么“危险”。

It is dangerous to insert anything in a database table that comes directly from user input without processing it, otherwise you leave yourself open to SQL injection attacks. Example: A coder lets the user type in their name in a field, and the user types:

在数据库表中插入任何直接来自用户输入而不处理它的东西是很危险的，否则您就会对SQL注入攻击开放。示例:程序员允许用户在字段中输入他们的名字，用户类型:

Joe '); drop table users; commit transaction; --

and then the coder puts that in their MySQL database like so:

然后编码器把它放到MySQL数据库中

conn.execute("insert into users (username) values ('" + userInput + "')");

Boom The user has deleted the users table (assuming the database login had rights to do that, which it shouldn't -- but that's a different topic), because the coder didn't ensure that the string from the user was escaped correctly, and so it got sent directly to the DB engine and the attacker has a good laugh. :-)

繁荣用户删除用户表(假设数据库登录有权利这样做,它不应该——但这是一个不同的主题),因为编码器没有确保用户的字符串是正确了,所以直接发送给了DB引擎和攻击者有良好的笑。:-)

Use whatever tools your environment provides to ensure that strings are escaped correctly. For instance, JDBC uses the PreparedStatement class for this. Most environments will have something similar.

使用您的环境所提供的任何工具来确保字符串正确地转义。例如，为此使用PreparedStatement类。大多数环境都会有类似的情况。

#6

It is not dangerous as long as you correctly escape the data when doing INSERT/UPDATE/...

只要在执行INSERT/UPDATE/…时正确地转义数据，就没有危险。

And escaping HTML characters is NOT a good approach. Imagine you wrote a function that escapes such characters and you have stored some escaped text in the database. Then you notice that your function did not escape '<', so you change the function... now what happens to the records that are already in the database? - Their '<' characters will stay unescaped. Thus, NEVER escape text before storing it in the database (escape the SQL query, of course). Escaping should happen when the HTML/XML/... page is produced out of the text, that is, after querying the original text from the database!

转义HTML字符不是一个好的方法。假设您编写了一个函数来转义这些字符，并在数据库中存储了一些转义文本。然后你注意到你的函数没有转义为'<'，所以你改变了函数…现在数据库中已经出现了哪些记录呢?-他们的“< >”角色将不会被逃脱。因此，在将文本存储到数据库(当然，要避免SQL查询)之前，永远不要逃避文本。当HTML/XML/…页面是由文本生成的，即查询数据库中的原始文本之后!

#1