Build ecto query based on dynamic input

[Damir Gaynetdinov]

Hi all. 

I'm trying to build a query based on a map which I get from a request. I might have several keys in the map, like key1, key2, key3 with their appropriate values and I'd like to join a table using these keys and values. For example if I've got this map %{ 'key1' => 'value1', 'key2' => 'value2' }, the desirable request would look like this:

select * from table_1
join table_2 on
table_1.some_id = table_2.some_id AND

table_2.type = key1 AND table_2.value = value1 OR
table_2.type = key2 AND table_2.value = value2

To build such query I thought that I can use fragment, so first I created a sql chunk 

"table_2.type = ? AND table_2.value = ? OR table_2.type = ? AND table_2.value = ?"

and a list of args

[key1, value1, key2, value2]

Then I tried to use it like this

query |> where([], fragment(^[sql_chunk | args])) |> Repo.one

But this doesn't work, I get "** (Ecto.QueryError) MySQL adapter does not support keyword or interpolated fragments in query".

Is there any way to build such query besides using `Ecto.Adapters.SQL.query/4` function? 

Thanks! 

[Alex Shneyderman]

what version of ecto are you using?

I just tried this on master:

    key1 = "key1"

    val1 = "val1"

    key2 = "key2"

    val2 = "val2"

    q0 = from table_1 in Model, join: table_2 in Model2, on: table_1.x == table_2.id, select: table_1

    q2 = from q1 in q0, where: fragment("(m0.x = ? and m0.y = ?) or (m1.id = ? and m1.id = ?)", ^key1, ^val1, ^key2, ^val2) 

    query = q2 |> normalize

    IO.puts "#{inspect SQL.all(query)}"

and it produces:

"SELECT m0.`id`, m0.`x`, m0.`y`, m0.`z` FROM `model` AS m0 INNER JOIN `model2` AS m1 ON m0.`x` = m1.`id` WHERE ((m0.x = ? and m0.y = ?) or (m1.id = ? and m1.id = ?))"

so all seems to work as expected. In ecto source tree there is plenty of tests to cover fragments. I do not see how your case differs from some of the existing tests in there. I based my trials on 

https://github.com/elixir-lang/ecto/blob/master/test/ecto/adapters/mysql_test.exs#L369

Cheers,

Alex.

[Damir Gaynetdinov]

Thanks for the answer. I'm using ecto version 1.0.1.

My point is that my input map can contain one pair or four or three or five, so it could be %{ "key1" => "value1" } or %{ "key17" => "foo", "key4" => "value4", "key55" => "value55" }. So I cannot pass just string to fragment like fragment("table_2.type = ? and table_2.value = ? OR table_2.type = ? and table_2.value = ?") because I don't know how many ORs will be in this sql. And also I don't know number of arguments beforehand. 

So first I parse conn.query_params to extract necessary pairs and then iterate through them to build sql string and list of args. And when I'm trying to pin sql string and list of args in the fragment I get errors. Even trivial example gives me this: 

iex(14)> query = from e in Model, where: fragment("1 = 1")
#Ecto.Query<from e in Model, where: fragment("1 = 1")>

All good. Now with variable:

iex(15)> sql = "1 = 1"
"1 = 1"
iex(16)> query = from e in Model, where: fragment(^sql)
#Ecto.Query<from e in Model, where: fragment(^"1 = 1")>
iex(17)> Repo.one(query)
** (Ecto.QueryError) MySQL adapter does not support keyword or interpolated fragments in query:


from e in Model,
  where: fragment(^...),
  select: e

It seems like there is no way to pass sql chunk as variable to the fragment. 

So I don't know how to build such kind of sql queries based in dynamic input using ecto.

For example in rails there is ActiveRecord::Base.connection.quote which I can use to inject variable in the sql chunk and then safely use it like this `base_scope.joins(sql_chunk)`. Or I can use sanitize_sql_array for example. 

[José Valim]

Ecto does not support it. We do need a way to dynamically add "OR"s to queries. Your best option is to define clauses that takes N number of arguments. Like:

def or([key1, value1]) do

  fragment(...)

end

def or([key1, value1, key2, value2]) do

  fragment(...)

end

... and so on.

I know it sucks but it is the only option if you want to use fragments. We won't support dynamic strings in fragment because that's asking for a SQL injection attack.

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/5c96536e-3156-481a-a294-df4805b7b97d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Alex Shneyderman]

oh, you might want to try the concatenation trick - it is a hack and might cause performance issues (depending on DB)

    vals = ["key1" <> "val1", "key2" <> "val2"]

    q0 = from table_1 in Model, select: table_1

    q2 = from q1 in q0, where: fragment("(m0.x || m0.y) in ?", ^vals) 

    query = q2 |> normalize

    IO.puts "#{inspect SQL.all(query)}"

produces - 

SELECT m0.`id`, m0.`x`, m0.`y`, m0.`z` FROM `model` AS m0 WHERE ((m0.x || m0.y) in ?)

I do not work with MySQL, so you will need to verify that concatenation operator is || in mysql and that in ? param works.

The real solution would be - as Jose mentioned - to allow ORs while compositing queries. Something like this would be really nice:

base = from t in Table, select: t

query = params |> Enum.reduce(fun({key,val}, q) ->  (from q0 in q, where: q0.type == ^key and q0.value == ^val) end, base)

but that currently produces ANDs between reduce iterations. It would be nice to have something like:

query = params |> Enum.reduce(fun({key,val}, q) ->  (from q0 in q, where_or: q0.type == ^key and q0.value == ^val) end, base)

Cheers,

Alex.

[Steve Pallen]

One hack I've used in the past is to build the query in a string, the do a eval_string on it. Not pretty, but worked for me on earlier versions of Ecto.

[Ben Wilson]

Is there any way that doesn't open you up 100% to remote code execution?

[Steve Pallen]

That's why it's a hack. Of course, a simple regex over the params fields used in the query would derisk it.