Frogglet

Ecto not allowing string interpolation in fragments?

I am trying to use where exists (), which requires a fragment in ecto. I need to use a different table name for the subquery in different situations, so I thought a variable that gets interpolated would be ideal. No, the value does not come from an unsafe source. I am aware of the dangers of SQL injection attacks and how to avoid them. I am selecting the value of the variable from another ecto schema inside a case expression. Surely there must be some way of doing this, without having to entirely disconnect from the Ecto abstractions we already depend on?

Here is an example:

  def thing(schema) do
    other_table = Other.Schema.__schema__(:source)
    from(a in My.Schema,
      where: fragment("exists (
        SELECT 1
        FROM #{other_table} o
        WHERE o.column_name = ?)", ^a.my_field)
    )
    |> Repo.all()
  end

And here is the error:

(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 = ?)"

We are trying to use exists because it is significantly more performant than the alternatives that ecto provides for our use-case. We are reaching the scale where inefficient queries that can’t use indexes properly are becoming critical problems.

22 comments

#ecto

2 11452 22

2024-08-13 20:30:00 UTC

Marked As Solved

NobbZ

Which can luckily be macro’ed away… Roughly:

[
  {"omg", "o"}
]
|> Enum.each(fn {tbl, t} ->
  defp add_exists(qry, unquote(tbl)) do
    from q in qry,
      where: fragment(unquote("exists (
        SELECT 1
        FROM #{tlb} #{t}
        WHERE #{t}.column_name = ?)"), ^q.my_field)
      )
  end
end

This is a very rough draft though… And I’m not exactly sure about unquoteing the string passed to fragment. But this way it should be evaluated at compiletime and “look like” a static string to fragment.

Post #13

Also Liked

josevalim

Creator of Elixir

Yeah, unfortunately then I think you will have to hard code the table name. If you have multiple table names, you will need a case and a clause for each:

  def thing(schema) do
    other_table = Other.Schema.__schema__(:source)
    from(a in My.Schema) |> add_exists(other_table) |> Repo.all()
  end

  defp add_exists(query, "omg")
    from q in query,
      where: fragment("exists (
        SELECT 1
        FROM omg o
        WHERE o.column_name = ?)", ^q.my_field)
      )
  end

Sorry.

Post #12

josevalim

Creator of Elixir

Hi @Frogglet! We don’t allow interpolation because it can be unsafe and lead to SQL injection attacks.

Have you tried passing the table name as fragment interpolation: fragment(“... FROM ? ....”, ^table_name)?

Post #9

tfwright

I had a similar issue trying to dynamically build fragment strings (from a trusted source) and it seems like a general escape hatch for these kind of issues, when trying to use third party macros with dynamic inputs where not explicitly supported is to use Code.eval_quoted. So, for example, you can wrap an entire select statement and fool Elixir/Ecto into thinking the variables are in fact literals:

Code.eval_quoted(
      quote do
        select(unquote(escaped_q), [table], %{
          dynamic_select: fragment(unquote(some_var), table.field))
        })
      end
    )

I am pretty positive this is the last tool you should reach for and maybe it would be better to use postgrex directly for these cases. I haven’t tried that so I can’t speak to the pros/cons but I’d love to hear people’s thoughts about the practice, and maybe whether Ecto could adopt some “blessed” way of circumventing protections like this one because I certainly feel dirty doing this.

Post #21

Last Post!

elvanja

FWIW: table names can be interpolated with Ecto.Query.API — Ecto v3.14.0, e.g.:

      ["join_table_1", "join_table_2"]
      |> Enum.reduce(Tag, fn assoc, q ->
        or_where(q, [t], fragment("exists (select 1 from ? where tag_id = ?)", literal(^assoc), t.id))
      end)
      |> select([t], %{id: t.id})

Post #23