我有一个单页应用程序，浏览器在其中完成所有逻辑工作。除了初始加载之外，服务器几乎是数据库的一个奇特接口。
例如，浏览器发送数据字典键、列名称/值对以及 SELECT 的 where 子句。服务器将各部分组装成 SQL、执行查询并回复。新：例如，在 SELECT 中，提取的表名和列来自数据字典 - 浏览器提供数据字典键和 SELECT where 子句。
这种非常开放的环境非常容易受到 SQL 注入攻击。目标是防止上述攻击造成的损害。
首先要克服的问题
，正如讨论的，不可能参数化随机 SELECT where 子句 - SELECT 无法使用准备好的语句。
其次，mysqli 是 MySQL 参数化语句的库，不支持 NULL 也不支持 MySQL 函数，例如 CURRENT_DATE 或 NOW()，如 讨论。
建议的解决方案
首先，如果 SELECT 无法参数化，则由没有 DML 或 DDL 权限的用户执行 SELECT。这将防止 SQL 注入攻击更改数据库。
其次，为 mysqli 编写一个包装函数，该函数将允许 NULL 和 MySQL 函数作为参数传递。这将使参数能够轻松地用于所有 DML。
第三，在正常查询或普通用户无法看到或接触的地方隐藏高度敏感的数据。这将使密码等敏感数据脱离攻击范围。
第四，编写一个包装器命令来强制执行用户/查询类型关系。这将确保 SELECT 由 select 用户执行，例如，
该工作的结果是 此处。从逻辑上讲，问题是这种方法能否成功防范 SQL 注入攻击？
无答案
我之前提出了同样的问题。由于我的演示工作不佳，收到的答案和评论最终集中在虚假问题上，而不是解决（诚然）困难的问题 - 这真的有效吗？
作为参考，以下是其中一些评论和答案。
使用 PDO 的预准备语句
首先，预准备语句不能用于所有 SELECT - PDO 有什么帮助？其次，mysqli 不接受 NULL 或 MySQL 函数 - PDO 有什么帮助？
为什么要重新发明轮子
如果你知道克服这些问题的方法，我真的很想知道 - 这是一个难题。
看不到 mysql_real_escape_string()
值应该在传递给数据库查询函数之前进行清理。 mysql_real_escape_string() 是一组可用的清理函数之一，例如，可以对日期使用不同的清理函数。
太多的工作
请与我分享您对克服这些问题的任何方法的了解 - 我真的希望有更好的见解。也就是说，从我重新设置整个过程开始，按照我的笔记，花了 30 到 45 分钟。此后不会产生任何时间成本。
我对 mysqli 很满意
当您无法参数化 SELECT 时，您将如何防止 SQL 注入攻击？您是否希望在更新列时永远不使用 NULL？每个人都有自己的毒害，但这些是我希望解决的问题——而不是忍受。
@Konerack 指出参数数量有限
对。更改为使用 eval() 的代码（不寒而栗），这解决了问题。需要安全审查。
再次的问题
这种方法能否防止 SQL 注入攻击，同时克服无参数化 SELECT 和 mysqli 的参数限制问题？
I have a Single Page Application in which the browser does all the logic work. Except for initial loading, the server is pretty much a fancy interface to the database.
The browser sends data dictionary keys, column name / value pairs, and where clauses for SELECT, for example. The server assembles the parts into SQL, executes the queries, and replies. NEW: In a SELECT, for example, the table name and columns pulled are from the data dictionary - the browser supplies the data dictionary key and the SELECT where clause.
This very open environment is very susceptible to SQL Injection attacks. The goal is to prevent damage from said attacks.
Problems to be Overcome
First, as discussed, it is impossible to paramaterize a random SELECT where clause - SELECT cannot use a prepared statements.
Second, mysqli, the library for paramaterized statements with MySQL, does not support NULL nor MySQL functions, eg, CURRENT_DATE or NOW(), as discussed.
Proposed Solution
First, if SELECT cannot be paramterized, then execute SELECT by a user who has no DML or DDL rights. This will prevent SQL Injection Attacks from changing the database.
Second, write a wrapper function for mysqli that will allow NULL and MySQL functions to be passed as parameters. This will allow parameters to be easily used for all DML.
Third, shadow highly sensitive data where it cannot be seen or touched by normal queries or normal users. This will put sensitive data, such as passwords, out of the range of attacks.
Forth, write a wrapper order to enforce the user / query type relationship. This will ensure SELECT are executed by the select user, for example
The result of that effort is here. The question is, logically, will this approach successfully protect against SQL Injection Attacks?
Non-Answer Answers
I proposed this same question before. Due to my poor job of presentation, received answers and comments ended up focusing on spurious issues instead of addressing the (admittedly) difficult question - does this actually work?
As a reference, here are some of those comments and answers.
Use PDO's prepared statements
First, prepared statements cannot be used for all SELECTs - how does PDO help? Second, mysqli does not accept NULL or MySQL functions - how does PDO help?
Why re-invent the wheel
If you know of an approach that overcomes these problems, I would really really like to know - this is a difficult problem.
no mysql_real_escape_string() in sight
Values should be sanitized before being passed to the database query functions. mysql_real_escape_string() is one of a set of sanitizing functions available, for example, one would use a different sanitizer for dates.
too much work
Please share with me your know of any approach that overcomes these problems - I would really like better insight. That said, from my setting up the whole thing again, following my notes it took between 30 and 45 minutes. No time costs incurred thereafter.
I am happy with mysqli
How are you going to prevent SQL Injection Attacks when you cannot parameterize your SELECT? Do you expect to never use NULL when updating a column? Every man to his own poison, but these are issues I hope to solve - not live with.
@Konerack pointing out limited number of parameters
Right. Changed to code to use eval() (shudder) which solved the problem. Needs security review.
The Question Again
Will this approach protect against SQL Injection Attacks while overcoming the problems of no parameterized SELECT and mysqli's parameter limitations?
发布评论
评论（1）
答案很简单：
对于参数，请使用 PDO 或 mysql_real_escape_string() 。
对于其他动态 SQL（表名、列名、语法元素），请使用白名单。
参阅：如何防止使用动态表名进行 SQL 注入？
请 无法相信浏览器会保持在您在 Javascript 中设置的范围内。这很容易被操纵。因此，您必须将来自该端的所有数据视为不可信。确保根据允许的关键字列表检查 where 子句中的所有元素。
为此，您可以使用：
=、<>、>、LIKE、NULL、IS。AND、OR、XOR、NOT'引号中，并且您需要通过提供这些值mysql_real_escape_string()以确保没有恶作剧。The answer is simple:
For parameters use PDO or
mysql_real_escape_string().For other dynamic SQL (table names, column names, syntax elements) use whitelisting.
See: How to prevent SQL injection with dynamic tablenames?
You cannot trust the browser to keep within the bounds you set in Javascript. This can be easily manipulated. So you must treat all data from that end as untrusted. Make sure you check all elements in the where clause against a list of allowed keywords.
For this purpose you'd use:
=, <>, >, LIKE, NULL, IS.AND, OR, XOR, NOT'quotes and you will need to feed these throughmysql_real_escape_string()to make sure there are no shenanigans.