erlang - Ecto 的片段允许 SQL 注入(inject)

Question

当 Ecto 查询变得更加复杂并且需要像 CASE...WHEN...ELSE...END 这样的子句时，我们倾向于依赖 Ecto 的 fragment来解决它。
例如query = from t in <Model>, select: fragment("SUM(CASE WHEN status = ? THEN 1 ELSE 0 END)", 2)事实上the most popular Stack Overflow post about this topic建议创建一个这样的宏:

defmacro case_when(condition, do: then_expr, else: else_expr) do
  quote do
    fragment(
      "CASE WHEN ? THEN ? ELSE ? END",
      unquote(condition),
      unquote(then_expr),
      unquote(else_expr)
    )
  end
end

因此您可以在 Ecto 查询中以这种方式使用它:

query = from t in <Model>,
  select: case_when t.status == 2
    do 1
    else 0
  end

同时，在另一个 post ， 我找到了这个:

(Ecto.Query.CompileError) to prevent SQL injection attacks, fragment(...) does not allow strings to be interpolated as the first argument via the `^` operator, got: `"exists (\n        SELECT 1\n        FROM #{other_table} o\n        WHERE o.column_name = ?)"

好吧，似乎 Ecto 的团队发现人们正在使用 fragment来解决复杂的查询，但他们没有意识到这会导致 SQL 注入(inject)，因此他们不允许在那里进行字符串插值以保护开发人员。
然后另一个人说“别担心， use macros”。
我不是 Elixir 专家，但这似乎是一种使用字符串插值的解决方法，可以避开 fragment保护。
有没有办法使用片段并确保查询已参数化？

Answer 1

此处的 SQL 注入(inject)将导致使用外部数据进行字符串插值。想象 where: fragment("column = '#{value}'") (而不是正确的 where: fragment("column = ?", value) )，如果值来自您的 params (Phoenix 操作的第二个参数的通常名称，即从 HTTP 请求中提取的参数)，是的，这可能会导致 SQL 注入(inject)。
但是，准备好的语句的问题是你不能用一些动态 SQL 部分(例如，像运算符一样简单的东西)替换参数(? 字符串中的 fragment/1)，所以，你不真的没有选择。假设您想写 fragment("column #{operator} ?", value)因为操作符是动态的并且取决于条件，只要操作符不是来自用户(在代码中的某处硬编码)，它就是安全的。
不知道大家是否熟悉PHP(以下示例中的PDO)，但这与$bdd->query("... WHERE column = '{$_POST['value']}'")完全相同。 (通过字符串插值注入(inject)一个值)与 $stmt = $bdd->prepare('... WHERE column = ?') 相反然后 $stmt->execute([$_POST['value']]); (正确的准备语句)。但是，如果我们回到我之前关于动态运算符的故事，如前所述，您不能动态绑定(bind)一些随机 SQL 片段，DBMS 会解释 "WHERE column ? ?"。与 >作为运算符和 'foo'作为值(value)(对于想法)WHERE column '>' 'foo'这在语法上是不正确的。因此，使这个运算符动态化的最简单方法是编写 "WHERE column {$operator} ?" (通过字符串插值或连接注入(inject)它，但只能注入(inject)它)。如果这个变量 $operator由您自己的代码定义(例如: $operator = some_condition ? '>' : '='; )，这很好，但相反，如果它涉及一些来自客户端的超全局变量，如 $_POST或 $_GET ，这会造成安全漏洞(SQL 注入(inject))。
TL; 博士

Then comes another guy who says "don't worry, use macros."

在提到的帖子中，Aleksei Matiushkin 的回答只是 fragment/1 对禁用/禁止字符串插值的一种解决方法。动态注入(inject) 已知 运算符(operator)。如果你重复使用这个技巧(并且不能真的这样做)，只要你不盲目地“注入(inject)”来自用户的任何随机值，你会没事的。
更新:
毕竟，似乎 fragment/1 (我没有检查源代码)并不意味着准备好的语句( ? 不是真正的准备好的语句的占位符)。我尝试了一些简单而愚蠢的查询，如下所示:

from(
  Customer,
  where: fragment("lastname ? ?", "LIKE", "%")
)
|> Repo.all()

至少对于 PostgreSQL/postgrex，控制台中生成的查询实际上是:
SELECT ... FROM "customers"AS c0 WHERE (lastname 'LIKE' '%') []
请注意 [] (空列表)在参数的末尾(并且查询中没有 $1)所以它似乎就像 PHP/PDO 中准备好的语句的模拟，意思是 Ecto(或 postgrex？)实现了正确的转义和值注入(inject)直接在查询中，但仍然如上所述 LIKE变成了一个字符串(请参阅围绕它的 ')，而不是一个运算符，因此查询失败并出现语法错误。