Parameterized query with query is wrong somewhere

Questions / Help
, ,
1

Hello, I’m trying to run a parameterized query using Ecto.Adapters.SQL.query against a Postgres database.

What I’m trying to do is this:

Ecto.Adapters.SQL.query(MyRepo, "DELETE FROM my_table WHERE ($1::varchar) IN ($2::varchar)", [
      Enum.join(keys, ","),
      attrs
    ])

It resolves to (gets converted into) this:

[debug] QUERY OK db=0.7ms decode=7.3ms queue=1.7ms idle=1290.2ms
DELETE FROM my_table WHERE ($1::varchar) IN ($2::varchar) ["user_id,car_id", "(132,'8647c8c2-abde-4bca-95bb-434cadacd1f1'),(133,'2ff2a187-d1d6-481a-aafd-3d8ed4c2c4c5')"]

But the result of that is:

{:ok,
 %Postgrex.Result{
   columns: nil,
   command: :delete,
   connection_id: 23417,
   messages: [],
   num_rows: 0,
   rows: nil
 }}

So it doesn’t delete anything at the end. I’m having no idea why. The records exist. And if I do it like this it works:

Ecto.Adapters.SQL.query(MyRepo, "DELETE FROM my_table WHERE #{Enum.join(keys, ",")} IN #{attrs]}")

But I’m not doing it like this because I’m trying to avoid SQL injection attacks by avoiding dynamic values.

Any idea what I’m doing wrong then? Thanks

2

I think the problem is, that your $1 is handled as a string and the resulting query would be something like:

DELETE FROM my_table 
WHERE ('user_id,char_id') IN (
  (132,'8647c8c2-abde-4bca-95bb-434cadacd1f1'),
  (133,'2ff2a187-d1d6-481a-aafd-3d8ed4c2c4c5')
)

and since the string 'user_id,char_id' doesn’t match, nothing gets deleted.

To use dynamic field you could take a look at: Dynamic Queries
I haven’t used them yet, but I think they could work.

1 Like
3

That’s a good principle. It’s also why your query doesn’t work - a bound parameter like $1 will always be a value not parsed by SQL - ($1::varchar) with $1 = "user_id,car_id" has one element which is that whole string.

This thread may be helpful:

1 Like