CODEWITHSUNDEEP
elixirphoenix-frameworkecto
Денис Корх
Денис Корх

Reputation: 353

Elixir/Phoenix - how to add variables value to fragment

I have query to database:

Repo.all(from d in ScannerData,
          where: d.unixdate >= ^dates.start_date,
          order_by: [desc: fragment("?", ^order_by)],
          group_by: d.host,
          limit: 10,
          select: %{
            recipient: d.host,
            email_count: fragment("COUNT(crc)"),
            duration_sum: fragment("SUM(duration)"),
            ham_duration_sum: fragment("SUM(CASE WHEN scanner = 'Clear' THEN duration ELSE 0 END)"),
            spam_duration_sum: fragment("SUM(CASE WHEN scanner != 'Clear' THEN duration ELSE 0 END)")
          }
      )

The request contains a string that is responsible for sorting:

order_by: [desc: fragment("?", ^order_by)]

Depending on the situation, sorting can be done in different ways, so I use a variable order_by.

order_by = case params["sort"] do
  "email_count" -> "COUNT(crc)"
  "size_sum" -> "SUM(size)"
  "duration_sum" -> "SUM(duration)"
  "ham_duration_sum" -> "SUM(CASE WHEN scanner = 'Clear' THEN duration ELSE 0 END)"
  "spam_duration_sum" -> "SUM(CASE WHEN scanner != 'Clear' THEN duration ELSE 0 END)"
  _ -> "COUNT(crc)"
end

But sorting not work. If I use:

order_by: [desc: fragment(^order_by)]

I have an error:

to prevent SQL injection attacks, fragment(...) does not allow strings to be interpolated as the first argument via the ^ operator, got: "SUM(size)"

I will be grateful for the help.

Upvotes: 2

Views: 656

Answers (1)

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 121000

To be able to order by dynamic values, one needs to explicitly declare Ecto.Query.dynamic/2. That said, the following would work:

order_by: ^[desc: dynamic([d], fragment("?", order_by))]

Upvotes: 1

Related Questions