Ecto `fragment` with variable column

Questions / Help
,
1

We have two tables with fields of type :map, which are copied from one to the other. I’d like a generic method that can do a query on one subfield, e.g. something like:

  defp with_hierarchical_data(model, field) do
    from(pa in model,
      select: [:id, ^field],
      where: fragment("#{^field}->>'selected_plan' IS NOT NULL")
    )
  end

I get "to prevent SQL injection attacks, fragment(…) does not allow strings to be interpolated as the first argument via the ^ operator, got: "#{^field}->>'selected_plan' IS NOT NULL"".

In my case, field is a trusted atom. How can I write this (other than writing two nearly identical functions) / bypass this prevention?

I looked for answers in a similar thread but didn’t see a solution

1 Like
2

Try moving the interpolation outside of from.

defp with_hierarchical_data(model, field) do
  fd = "#{field}"

  from(pa in model,
    select: [:id, ^field],
    where: fragment("?->>'selected_plan' IS NOT NULL", ^fd)
  )
end

Edit: After thinking about this you may not even need to convert field to a string. Just use the atom and I believe it will convert it for you. The key is that you need to use ? and pass ^field as the 2nd argument to fragment()

defp with_hierarchical_data(model, field) do
  from(pa in model,
    select: [:id, ^field],
    where: fragment("?->>'selected_plan' IS NOT NULL", ^field)
  )
end
1 Like
3

@l00ker I thought it was a good idea, so I had tried it actually, but I think it can not work. From the doc:

SQL function arguments can only be used as data values, not as identifiers. Thus for example this is reasonable:

INSERT INTO mytable VALUES ($1);

but this will not work:

INSERT INTO $1 VALUES (42);

If I try it I get the following error:

** (Postgrex.Error) ERROR 42725 (ambiguous_function) operator is not unique: unknown ->> unknown

    query: SELECT $1 FROM "policy_applications" AS p0 WHERE ($2->>'selected_plan' IS NOT NULL)
4

you want to use field/2:
https://hexdocs.pm/ecto/Ecto.Query.API.html#field/2

4 Likes
5

Thank you @cevado :heart:. Exactly what I was looking for.

Anyone having the same issue, what I was looking for was:

from(pa in model,
  select: ^[:id, field],
  where: fragment("?->>'selected_plan' IS NOT NULL", field(pa, ^field))
)
4 Likes