Avoiding SQL-injection

Hi

I need to perform a semi-complex upsert on a table, which cannot be expressed through Ecto’s interface.

What I am doing is constructing the query as a string, and then sending it to the Ecto.Adapter.SQL.query function

 values =
  updates
  |> Enum.map(fn {message_id, changes} ->
    stats = ~w(sent delivered opened clicked replied unsubbed goal spam dropped)

    changes = stats |> Enum.map(fn stat -> Map.get(changes, stat, 0) end)
    [message_id] ++ changes |> Enum.join(", ")
  end)
  |> Enum.map(fn tuple -> "(#{tuple})" end)
  |> Enum.join(",")

query = "
  update messages am set
    sent = am.sent + changes.sent,
    delivered = am.delivered + changes.delivered,
    opened = am.opened + changes.opened,
    clicked = am.clicked + changes.clicked,
    replied = am.replied + changes.replied,
    unsubbed = am.unsubbed + changes.unsubbed,
    goal = am.goal + changes.goal,
    spam = am.spam + changes.spam,
    dropped = am.dropped + changes.dropped,
    last_sent = case when changes.sent > 0 then current_timestamp at time zone 'UTC' else am.last_sent end
  from (values $1) as changes(message_id, sent, delivered, opened, clicked, replied, unsubbed, goal, spam, dropped)
  where changes.message_id = id
  returning am.id, am.app_id, am.sent, am.delivered, am.opened, am.clicked, am.replied, am.unsubbed, am.goal, am.spam, am.dropped, am.last_sent
"

Ecto.Adapters.SQL.query!(Repo, query, [], [])

The above works, but I am concerned about manualy building the query like that. If I try to pass values in as a parameter in SQL.query (which would be the safe way, correct?), it fails with a syntax error.

Any ideas?

1 Like

I’d personally use fragments for the parts that ecto cannot show, and still use ecto for everything else. Fragments handle things like that properly.

I have similiar instances like the above where I use ON CONFLICT syntax (upsert), which Ecto does not support yet

In those cases, I cannot use fragments, and even if I could, I have trouble seeing how they could be applied to the example query above

Another thing, to my knowledge, Ecto is not able to say something like increment value in message with id = 2 by 2, but increment it by 3 in another message, within the same query

Ecto 2.1+ has that. ^.^
(Out soon)

You ‘should’ be able to, it uses the actual names that you could then use in a fragment?

Not sure what you mean, could you provide a small example?

Something like, for your above example, from(p in Post, update: [inc: [value: fragment("CASE WHEN ?=2 THEN 2 WHEN ?=3 THEN 3 ELSE 0", p.id, p.id)]]) or so, it could be simplified of course too.

1 Like