实施“可配置的"配置.安全地加入系统 [英] Implementing a "configurable" joining system, safely

问题描述

背景

您好，我正在用PHP和MySQL开发实验/教育工具.我是SQL的新手，但是我想从一开始就以正确的方式做事.我正在使用PDO准备好的语句来进行所有变量替换，并在所有可能的地方回引(因此，据我所知，它将不能移植到非MySQL数据库中).关于我的问题，我对下一步有一个想法，但是要花上几个小时才能实现(即使是SQL语法我也很陌生)，因此我想我首先要创建一个问题以防有人大喊:这不是这样做的方法！"并节省了我的时间.

Hello, I'm developing an experimental/educational tool in PHP and MySQL. I'm new to SQL, but I want to do things the right way from the start. I'm using PDO prepared statements for all variable substitutions, and backticking everywhere possible (thus, as I understand, it won't be portable to non-MySQL databases). Regarding my problem, I have an idea as to how to go forth, but it's going to take me several hours to implement (I'm new even to the syntax of SQL), so meanwhile I thought I'd create a question first just in case someone can yell, "This is not the way to do it!" and save me hours of effort.

问题

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

I would like to create an interface where a user would select from dropdown menus:

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

1. a table A,
2. one or more fields on that table, e.g. A.x and A.y,
3. a table B,
4. one or more fields on that table, e.g. B.z and B.y,

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

and upon submission the code would perform an inner join, matching each field respectively, e.g. A.x = B.z, A.y = B.y, etc. and return all matched rows.

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

My plan is simply to generate an INNER JOIN SQL statement, looping through the fields and inserting placeholders (?), binding the respective parameters, and finally executing the statement.

有没有更简单的方法?有更好的方法吗?这会以某种方式被利用吗?

Is there an easier way of doing this? Is there a better way of doing this? Will this be somehow exploitable?

非常感谢您.如果在我完成时(有疑问)没有人回应，我将发布解决方案.

Thank you very much, in advance. If no one responds by the time I finish (doubtful), I will post my solution.

其他

假设我会验证

1. 用户在A和B之间选择相等数量的字段，
2. 字段和表存在
3. 等

1. that the user selects an equal number of fields between A and B,
2. that the fields and tables exist,
3. etc.

，并且字段名称不必相同:它们将按顺序匹配. (请指出其他我可能不知道的细节！)

and that the field names need not be identical: they will be matched in order. (Do point out any other details I might not be aware of!)

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

Eventually, the goal is for these selections to be saved in a "settings" table themselves. In effect, users create "views" they would like to see each time they come back.

推荐答案

您做的太正确了，以至于我真的很内pointing地指出您做错了什么！ :)

You're doing so much right that I actually feel guilty pointing out that you're doing something wrong! :)

您只能使用准备好的语句来参数化字段值，而不能使用SQL标识符(例如列名或表名)进行参数化.因此，您将无法通过准备好的语句参数将A.x，B.z等传递到您的JOIN标准中:您必须做那些感觉很不对劲的事情，然后直接将它们串联起来到您的SQL字符串中.

You can only use prepared statements to parameterise field values—not SQL identifiers such as column or table names. Therefore you won't be able to pass A.x, B.z etc. into your JOIN criteria by way of prepared statement parameters: you must instead do what feels terribly wrong and directly concatenate them into your SQL string.

但是，一切并没有丢失.按照某种模糊的优先顺序，您可以:

However, all is not lost. In some vague order of preference, you can:

1. 为用户提供一个选项列表，您可以从中重新组装SQL:

1. Present the user with an option list, from which you subsequently reassemble the 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>

然后您的表单处理程序:

Then your form handler:

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.

This approach has the advantage that, short of compromising PHP (in which case you'll have far bigger concerns than SQL injection), arbitrary SQL absolutely cannot find its way into your RDBMS.

确保用户输入匹配期望值之一:

Ensure the user input matches one of the expected values:

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

然后您的表单处理程序:

Then your form handler:

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函数(并且还向用户公开了您的基础列名，但考虑到您的应用程序，我怀疑这是一个问题).

This approach relies on PHP's in_array function for safety (and also exposes to the user your underlying column names, but given your application I doubt that's a concern).

执行一些输入清理，例如:

Perform some input cleansing, such as:

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的特制字符串处理中)在带引号的标识符中).

Whilst we here quote the user input and replace any attempt by the user to escape from that quoting, this approach could be full of all sorts of flaws and vulnerabilities (in either PHP's mb_ereg_replace function or MySQL's handling of specially crafted strings within a quoted identifier).

如果可能的话，使用以上任何一种方法来避免将用户定义的字符串完全插入到自己的SQL中，是远.

It is far better if at all possible to use one of the above methods to avoid inserting user-defined strings into one's SQL altogether.

这篇关于实施“可配置的"配置.安全地加入系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！