How to write parametrized query in phoenix using ecto

how to write parametrized query in phoenix using ecto?

What are you trying to achieve and what have you tried? There are plenty of resources on using Ecto with Phoenix. If you are trying to build dynamic filters, you can use the dynamic keyword. For example, something like:

  def get_users(filter_criteria) when is_map(filter_criteria) do
    # I haven't figured out how to start with an empty starting point, 
    # so use a dummy initial filter
    criteria = dynamic([u], not is_nil(u.id))

    criteria = case filter_criteria["min_age"] do
      nil -> criteria
      age -> dynamic([u], u.age > ^age and ^criteria)
    end

    criteria = case filter_criteria["max_age"] do
      nil -> criteria
      age -> dynamic([u], u.age > ^age and ^criteria)
    end

    query = from u in Users,
      where: ^criteria

    Repo.all(query)
  end

There’s a post here that explains it further: https://medium.com/@feymartynov/dynamic-filters-in-ecto-68c5f1bed732

Here’s another similar discussion: Making queries in ecto with filters based on a map

Thanks mindok for the reply.

I want to write code which will prevent from sql injection attack.

You should be ok with this approach as Ecto handles all the proper escaping etc, but you should check the Ecto docs for yourself to verify.

If you use Ecto.Query then you should be safe, even with fragment it will use prepared statements and will prevent SQLi attacks.

1 Like