Prepared statements with Postgrex & Ecto

ecto, elixir

Solution

You're looking for the documentation for `Ecto.Adapters.SQL.query/4`. The SQL statement can have numerical variables such as `$1`, `$2`, ..., and then those parameters are passed in a list using the third argument of the function call.

You can achieve what you want with:

defmodule BoardApi.Board do
  use Ecto.Model

  def json_by_id(id) do
    sql = "SELECT row_to_json(json) AS result FROM (SELECT array_agg(users) AS users FROM users WHERE id=$1) json;"
    result = Ecto.Adapters.SQL.query(BoardApi.Repo, sql, [id])

    # `return` is not valid Elixir
    result.rows |> hd |> elem(0)
  end
end

Problem

I'm trying to make use of postgres feature of returning pure json, so i have added a method to my Ecto model which executes a query, that returns the wanted json. ``` defmodule BoardApi.Board do use Ecto.Model def json_by_id(id) do sql = "SELECT row_to_json(json) AS result FROM (SELECT array_agg(users) AS users FROM users WHERE id=#{id}) json;" result = Ecto.Adapters.SQL.query(BoardApi.Repo, sql, []) return List.first(result.rows) |> Tuple.to_list |> List.first end ``` My problem, is that this has sql injection potential, is it possible to use something like prepared statements?

Original source