How use Ecto.Adapters.SQL.query with MySQL

Question

I'm using Ecto.Adapters.SQL.query

query = " select id from $1 where id = $2 "
Ecto.Adapters.SQL.query!(Repo, query, [table, id])

But the adapter not replace my table as parameter

[debug] QUERY ERROR db=0.5ms
 select id from $1 where sysid = $2  ["activities", "469601326"]
[info] Application dgtidx exited: Docomo.Application.start(:normal, []) returned an error: shutdown: failed to start child: Docomo.Consumer
    ** (EXIT) an exception was raised:
        ** (Mariaex.Error) (1146): Table 'test.$1' doesn't exist

score 2 · Answer 1 · answered Feb 13 '18 at 17:09

2

You can't use a parameter to specify the table name like that as explained here.

If you're absolutely sure table is a valid table name and not arbitrary user input, you can use string interpolation:

query = "select id from #{table} where id = $1"
Ecto.Adapters.SQL.query!(Repo, query, [id])

You must ensure table is not arbitrary user input if you do this or you'll open yourself up to SQL injection attacks.

answered Feb 13 '18 at 17:09

Dogbert

212,659
41
396
397

It's a string, i validate this value. But, are there a way to sanitize it? – Cesar Gutierrez Feb 13 '18 at 17:46
I'd create a whitelist of table names and check against that: `if table in ["posts", "comments"] do .... query ... else ... show error ... end`. – Dogbert Feb 13 '18 at 17:52

How use Ecto.Adapters.SQL.query with MySQL

1 Answers1