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

Dogbert · Accepted Answer · 2018-02-13 17:09:16Z

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, 2018 at 17:09

Dogbert

224k43 gold badges419 silver badges417 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cesar Gutierrez Over a year ago

It's a string, i validate this value. But, are there a way to sanitize it?

Dogbert Over a year ago

I'd create a whitelist of table names and check against that: if table in ["posts", "comments"] do .... query ... else ... show error ... end.

Collectives™ on Stack Overflow

How use Ecto.Adapters.SQL.query with MySQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related