安全地实施“可配置”连接系统 - Implementing a "configurable" joining system, safely

Background

您好，我正在开发一个PHP和MySQL的实验/教育工具。我是SQL的新手，但我想从一开始就以正确的方式做事。我对所有变量替换使用 PDO 准备语句，并在所有可能的地方进行反引号（因此，据我了解，它不会移植到非 MySQL 数据库）。关于我的问题，我有一个关于如何继续的想法，但它需要几个小时来实现（我甚至对SQL的语法都是新手），所以同时我想我应该先创建一个问题，以防万一有人可以大喊，"这不是这样做的方法！"并节省我数小时的精力。

问题

我想创建一个界面，用户可以在其中从下拉菜单中进行选择：

一张表A，
该表上的一个或多个字段，例如 A.x和A.y ，
一张表B，
该表上的一个或多个字段，例如 B.z和B.y ，

提交后，代码将执行内部连接，分别匹配每个字段，例如 A.x = B.z、A.y = B.y等并返回所有匹配的行。

我的计划只是生成一个INNER JOIN SQL语句，遍历字段并插入占位符（?），绑定相应的参数，最后执行该语句。

有没有更简单的方法可以做到这一点？有没有更好的方法？这会以某种方式被利用吗？

非常感谢，提前。如果在我完成时没有人回复（可疑），我将发布我的解决方案。

杂项。

假设我将验证

用户在 A 和 B 之间选择相同数量的字段，
字段和表存在，
等。

并且字段名称不必相同：它们将按顺序匹配。（请指出我可能不知道的任何其他细节！

最终，目标是将这些选择本身保存在"设置"表中。实际上，用户创建他们每次返回时都希望看到的"视图"。

你做对了这么多，以至于我实际上感到内疚，指出你做错了什么！ :)

只能使用预准备语句来参数化字段值，而不能参数化列名或表名等 SQL 标识符。因此，您将无法通过预准备语句参数将A.x、B.z等传递到JOIN标准中：您必须做感觉非常错误的事情，并直接将它们连接到您的SQL字符串中。

然而，一切都没有丢失。按照某种模糊的优先顺序，您可以：

向用户显示一个选项列表，随后您可以从中重新组合 SQL：

<select name="join_a">
  <option value="1">x</option>
  <option value="2">y</option>
</select>
<select name="join_b">
  <option value="1">z</option>
  <option value="2">y</option>
</select>

然后，表单处理程序：

switch ($_POST['join_a']) {
  case 1:  $acol = 'x'; break;
  case 2:  $acol = 'y'; break;
  default: die('Invalid input');
}
switch ($_POST['join_b']) {
  case 1:  $bcol = 'z'; break;
  case 2:  $bcol = 'y'; break;
  default: die('Invalid input');
}
$sql .= "FROM A JOIN B ON A.$acol = B.$bcol";

这种方法的优点是，除了损害PHP（在这种情况下，你会有比SQL注入更大的问题），任意SQL绝对无法进入你的RDBMS。

确保用户输入与预期值之一匹配：

<select name="join_a">
  <option>x</option>
  <option>y</option>
</select>
<select name="join_b">
  <option>z</option>
  <option>y</option>
</select>

然后，表单处理程序：

if (!in_array($_POST['join_a'], ['x', 'y'])
 or !in_array($_POST['join_b'], ['z', 'y']))
   die('Invalid input');
$sql .= "FROM A JOIN B ON A.$_POST[join_a] = B.$_POST[join_b]";

这种方法依赖于 PHP 的 in_array 函数以确保安全（并且还向用户公开您的底层列名，但考虑到您的应用程序，我怀疑这是一个问题）。

执行一些输入清理，例如：
```
mb_regex_encoding($charset); // charset of database connection
$sql .= 'FROM A JOIN B ON A.`' . mb_ereg_replace('`', '``', $_POST['join_a']) . '`'
                    . ' = B.`' . mb_ereg_replace('`', '``', $_POST['join_b']) . '`'
```
虽然我们在这里引用用户输入并替换用户逃避引用的任何尝试，但这种方法可能充满各种缺陷和漏洞（在 PHP 的 mb_ereg_replace 函数或 MySQL 处理引用标识符中特制字符串）。
如果可能的话，最好使用上述方法之一来避免将用户定义的字符串完全插入到一个人的SQL中。

假设用户输入仅限于选择表和字段（即没有其他条件），您应该可以采用您的方法; 听起来很有趣:)

我想补充的一件事是，某些联接比其他联接更好。例如，使用主键（或其他索引）联接两个表将比需要全表扫描的两个不相关的列执行得更好。

这完全取决于桌子首先有多大;对于少于几千条记录，你应该没问题;任何超出认真思考的东西都到位:)