当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注入,所以他们不允许字符串插值作为保护开发人员的一种方式。
然后又来了一个人,他说:"别担心,use macros。"
我不是一个长生不老药Maven,但这似乎是一个变通办法,做使用字符串插值,逃避fragment
的保护。
有没有办法使用fragment并确保查询是参数化的?
1条答案
按热度按时间bkhjykvo1#
SQL injection, here, would result of string interpolation usage with an external data. Imagine
where: fragment("column = '#{value}'")
(instead of the correctwhere: fragment("column = ?", value)
), if value comes from yourparams
(usual name of the second argument of a Phoenix action which is the parameters extracted from the HTTP request), yes, this could result in a SQL injection.But, the problem with prepared statement, is that you can't substitute a paremeter (the
?
infragment/1
string) by some dynamic SQL part (for example, a thing as simple as an operator) so, you don't really have the choice. Let's say you would like to writefragment("column #{operator} ?", value)
because operator would be dynamic and depends on conditions, as long as operator didn't come from the user (harcoded somewhere in your code), it would be safe.I don't know if you are familiar with PHP (PDO in the following examples), but this is exactly the same with
$bdd->query("... WHERE column = '{$_POST['value']}'")
(inject a value by string interpolation) in opposite to$stmt = $bdd->prepare('... WHERE column = ?')
then$stmt->execute([$_POST['value']]);
(a correct prepared statement). But, if we come back to my previous story of dynamic operator, as stated earlier, you can't dynamically bind some random SQL fragment, the DBMS would interpret"WHERE column ? ?"
with>
as operator and'foo'
as value like (for the idea)WHERE column '>' 'foo'
which is not syntactically correct. So, the easiest way to turn this operator dynamic is to write"WHERE column {$operator} ?"
(inject it, but only it, by string interpolation or concatenation). If this variable$operator
is defined by your own code (eg:$operator = some_condition ? '>' : '=';
), it's fine but, in the opposite, if it involves some superglobal variable which comes from the client like$_POST
or$_GET
, this creates a security hole (SQL injection).TL;DR
Then comes another guy who says "don't worry, use macros ."
The answer of Aleksei Matiushkin, in the mentionned post, is just a workaround to the disabled/forbidden string interpolation by
fragment/1
to dynamically inject a known operator. If you reuse this trick (and can't really do otherwise), as long as you don't blindly "inject" any random value coming from the user, you'll be fine.UPDATE:
It seems, after all, that
fragment/1
(which I didn't inspect the source) doesn't imply a prepared statement (the?
are not placeholder of a true prepared statement). I tried some simple and stupid enough query like the following:At least with PostgreSQL/postgrex, the generated query in console appears to be in fact:
SELECT ... FROM "customers" AS c0 WHERE (lastname 'LIKE' '%') []
Note the
[]
(empty list) at the end for the parameters (and absence of$1
in the query) so it seems to act like the emulation of prepared statement in PHP/PDO meaning Ecto (or postgrex?) realizes proper escaping and injection of values directly in the query but, still, as said aboveLIKE
became a string (see the'
surrounding it), not an operator so the query fails with a syntax error.