的PHP:SQL注入>当没有准备好的语句可以使用时，如何防止? [英] php: sql-injection > how to prevent when no prepared statement can be used?
问题描述
一个简单的问题:我有一个不使用预准备语句(mySQLi)的框架. 当我有一些用户输入(通过公共形式但不允许HTML代码)时，如何避免sql注入. 该注释必须存储在mysqli数据库中.
A simple question: I have a framework which does not use prepared statements (mySQLi). How can sql injections be avoided when I have some user input (made through a public form but no HTML-code is allowed). That comment has to be stored in a mysqli database.
检测并删除可能的注入代码的最佳实践是什么? 请不要提出任何使用准备好的语句的建议，如果不重写整个框架，这些建议将不可用！
What is the best practice to detect and remove possible injection code? Please no suggestions to use prepared statements, they are not availabe without rewriting the whole framework!
编辑
我没有问如何进行MySQLi查询！ 我问如何在php级别上防止sql注入. 重复:一些网站访问者在纯文本字段中留下评论. 该访客(脚本小子)认为，在此评论中，他可以发布sql-injection.
I was NOT asking how to do a MySQLi query! I asked how to prevent sql-injections on the php-level. To repeat: some website visitor leave a comment in a plain text field. Inside this comment this visitor (the script kiddie) thinks, he can post a sql-injection.
再次:在将注释添加到数据库之前，如何从注释中删除此伪代码. 诸如嘿，使用准备好的语句"，使用pdo"，对这些参数使用这种查询"之类的答案无法回答我的问题. 还可以回答诸如更改"您的框架"或不足够！
Now again: how the delete this pseudocode from the comment BEFORE adding it to the database. Answers like "hey, use a prepared statement", "use pdo", "use this kind of query with these parameters" are NOT answering my question. Also answers like "change" your framework" or NOT adequate!
我知道可以通过preg_replace(..)等删除此类代码. 是的，我知道这里有些人不使用这种技术的意见..因此，最好不要朝这个方向回答！
I know that this could such code could be removed by preg_replace(..) and so on. And yes, I know the opinion of some here not to use this technique .. so better do not answer in that direction!
推荐答案
You might think that using an "escaping" function like mysqli::real_escape_string() on every input is just as safe as using prepared statements.
除了:
-
转义不适用于数字输入，仅适用于带引号的字符串和带引号的日期.
Escaping doesn't work for numeric inputs, only quoted strings and quoted dates.
转义在某些情况下无法正常工作.这些是极端情况，但它们存在.请参见围绕mysql_real_escape_string()
Escaping has some unexpected cases where it doesn't work. These are edge cases, but they exist. See some examples in answers for SQL injection that gets around mysql_real_escape_string()
编写代码时转义比较困难.正确地进行操作既费时又细致，而且容易出错.开发人员通常很想跳过它.
Escaping is harder when writing code. Doing it correctly is time-consuming and meticulous, and easy to get wrong. Developers are often tempted to skip it.
关心防止SQL注入的开发人员出于某些原因建议改用查询参数，包括但不限于以下原因:
Developers who care about preventing SQL injection recommend using query parameters instead for several reasons, including but not limited to the following:
-
查询参数也适用于数字值.
Query parameters also work for numeric values.
使用查询参数的代码更易于编写和阅读，并且不易出错.我们希望一旦他们习惯了使用查询参数，开发人员将更可靠地使用此方法，因此漏洞的可能性较小.
Code that uses query parameters is easier to write and read, and less prone to mistakes. We expect that once they have the habit of using query parameters, developers use this method more reliably, and therefore vulnerabilities are less likely.
例如，您可以编写如下代码:
For example, you could write code like the following:
$sql = "INSERT INTO mytable (col1, col2, col3, col4, col5, col6)
VALUES ('" . mysqli_real_escape_string($_POST['col1']) . "', "
. $mysqli->real_escape_string($_POST['col2']) . "', '"
. $mysqli->real_escape_string($_POST['col3']) . "', '"
. $mysqli->real_escape_string($_POST['col4']) . ", '"
. $mysqli->real_escape_string($_POST['col5']) . "', '"
. $mysqli->real_escape_string($_POST['col6']) . "')";
您能发现错误吗?只要有足够的时间，我相信您可以.但这会减慢您的编码速度，并且在寻找缺少引号字符和其他错误时可能会给您带来疲劳.
Can you spot the mistakes? With enough time, I’m sure you can. But it will slow down your coding and may give you eyestrain as you look for missing quote characters and other mistakes.
但是写起来容易得多，事后更容易阅读:
But it’s so much easier to write this, and easier to read it afterwards:
$sql = "INSERT INTO mytable (col1, col2, col3, col4, col5, col6)
VALUES (?, ?, ?, ?, ?, ?)";
查询参数对于更多的数据类型是安全的，并且可以帮助您更快地编写代码，并且减少错误.那是一个很大的胜利.
Query parameters are safe for more data types, and they help you write code more quickly, with fewer mistakes. That's a big win.
与上面的其他评论一样，我不同意您关于MySQLi不支持查询参数的说法.在文档中清楚地显示了它: https://www.php.net/manual/en/mysqli.prepare.php
Like the other comments above, I disagree with your claim that MySQLi doesn't support query parameters. It's clearly shown in the documentation: https://www.php.net/manual/en/mysqli.prepare.php
如果您有一些使用MySQLi但不支持mysqli::prepare()的包装器代码，那么我建议您停止使用该包装器代码.要么为您的框架编写功能，以便它使用查询参数，要么获得另一个框架.
If you have some wrapper code that uses MySQLi but doesn't support mysqli::prepare(), then I recommend you stop using that wrapper code. Either write features for your framework so that it uses query parameters, or else get another framework.
还回答诸如更改"您的框架"或不足够！
Also answers like "change" your framework" or NOT adequate!
看，这是事实:如果您不能编写可安全处理用户输入的代码，那么您就不应该接受用户输入.
Look, here's the truth: if you can't write code that handles user input safely, then you shouldn't accept user input.
您假设有使用preg_replace()的方法，但没有.注入代码的方式太多(SQL或Javascript)，最终您也会过滤掉许多 legacymate 用户注释.如果您的Web应用程序从其评论中删除引号和撇号，您的用户将如何反应?您还需要删除哪些其他字符?
You suppose there are ways of using preg_replace() but there aren't. There are so many ways of injecting code (either SQL or Javascript), that you would end up filtering out many legitimate user comments as well. How would your users react if your web app stripped quotes and apostrophes from their comments? What other characters do you need to strip out?
查询参数是解决此问题的最有效方法.你对他们的抵抗是荒谬的.
Query parameters are the most effective solution for this problem. Your resistance to them is absurd.
这就像电工在问，如何防止受到电击?但是我不能使用绝缘的手套或工具，所以不要建议这样做."
It's like an electrician who asks, "How can I prevent getting shocked? But I can't use insulated gloves or tools, so don't suggest that."
这篇关于的PHP:SQL注入>当没有准备好的语句可以使用时，如何防止?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
