[Proposal] Add Repo.query/1 and Repo.query!/1

[benjamin...@gmail.com]

Hey, I would like to propose introducing Repo.query/1 and Repo.query!/1 which would be similiar to Repo.query/2 and Repo.query!/2 althought only accepting a SQL sigil, and being able to leverage prepared queries.

There is already community interest https://elixirforum.com/t/sql-brings-an-extensible-sql-parser-and-sigil-to-elixir-confidently-write-sql-with-automatic-parameterized-queries/69807/22?u=schultzer

[José Valim]

Given it depends on another package, I think the simplest path forward is to either have an integration package (like phoenix_ecto that integrates both phoenix and ecto) or have facilities in the SQL package that augments the repo, such as "use SQL.EctoRepo", that will bring the desired functions.

José Valim

https://dashbit.co/

On Sat, May 10, 2025 at 7:30 PM benjamin...@gmail.com <benjamin...@gmail.com> wrote:

Hey, I would like to propose introducing Repo.query/1 and Repo.query!/1 which would be similiar to Repo.query/2 and Repo.query!/2 althought only accepting a SQL sigil, and being able to leverage prepared queries.

There is already community interest https://elixirforum.com/t/sql-brings-an-extensible-sql-parser-and-sigil-to-elixir-confidently-write-sql-with-automatic-parameterized-queries/69807/22?u=schultzer

--
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 visit https://groups.google.com/d/msgid/elixir-ecto/adca12e4-619a-40ec-89eb-323c33aec003n%40googlegroups.com.

[benjamin...@gmail.com]

Awesome, are you working to address the SQL injection vulnerability that is currently present in Ecto.Adapters.SQL API?

I don't see anything mention in the docs: https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#query/4 and new developers could be of the believe that Ecto protects them by default.

[José Valim]

It is a low-level API designed specifically for receiving the string. As far as I know, every database tool out there provides such an API for the case you want or need to directly emit a SQL command.

José Valim

https://dashbit.co/

To view this discussion visit https://groups.google.com/d/msgid/elixir-ecto/32252828-a4a4-48cf-a704-1867ef9982aan%40googlegroups.com.

[José Valim]

As far as I understand, it is also the API that SQL would use to talk to Ecto? If we removed this API, someone building another library for composing queries either at compile-time or runtime, would be unable to achieve their goals.

José Valim

https://dashbit.co/

[benjamin...@gmail.com]

> It is a low-level API designed specifically for receiving the string. As far as I know, every database tool out there provides such an API for the case you want or need to directly emit a SQL command.

True, but that doesn't mean that we can't do better.

> As far as I understand, it is also the API that SQL would use to talk to Ecto? If we removed this API, someone building another library for composing queries either at compile-time or runtime, would be unable to achieve their goals.

SQL is indenpendent from Ecto, you can think of it as the fundamental building block for SQL, it is a lexer and parser, infact you could potentially make postgrex use it.

[benjamin...@gmail.com]

As SQL generate prepared queries, then it would not use Repo.query/2 it would use prepare execute.

[José Valim]

Repo.query uses prepare execute, IIRC. In any case, the point is that there may be other tools, also agnostic to Ecto, and we must keep an execution point for them.

José Valim

https://dashbit.co/

To view this discussion visit https://groups.google.com/d/msgid/elixir-ecto/4932c1ab-ecf6-44d1-9651-ddde85d0a4ean%40googlegroups.com.

[benjamin...@gmail.com]

Not true, https://github.com/elixir-ecto/ecto_sql/blob/ad5e31c13b034564ff8bf3783bfb2e8ab7c0e6d0/lib/ecto/adapters/sql.ex#L596z

And query/2 could still exist for backwards compatibility. But this not something I’ll pursue, if you can’t see the benefits SQL can bring to Elixir’s adoption story, there are ton of projects out there with ton of SQL already written.

[José Valim]

Not true, https://github.com/elixir-ecto/ecto_sql/blob/ad5e31c13b034564ff8bf3783bfb2e8ab7c0e6d0/lib/ecto/adapters/sql.ex#L596z

Which becomes a prepare_execute for PG and I believe other adapters too: https://github.com/elixir-ecto/postgrex/blob/257daa773a7558d574df3aa3b558664275787ff8/lib/postgrex.ex#L292-L298

But this not something I’ll pursue, if you can’t see the benefits SQL can bring to Elixir’s adoption story, there are ton of projects out there with ton of SQL already written.

Please don't conflate things. The fact I propose it as an extension to Ecto does not imply it is not important to Elixir's adoption story. There are projects that I also maintain, which I think are important, and I build them as extensions rather than bundling everything as a default inside Ecto, Phoenix, and Elixir itself.

[benjamin...@gmail.com]

Awesome, I’m glad thats not the case!

 Although I don’t think a wrapper library is sufficient, as it’s not only the adapter api that would benefit from SQL but also migration.

[Christopher Keele]

> Awesome, are you working to address the SQL injection vulnerability that is currently present in Ecto.Adapters.SQL API?

Were this actually a security vulnerability rather than an intentional API design decision, or were you unsure if it were or not, I would remind you that posting about it on a public web-scrapable mailing list is not what responsible disclosure looks like. I suspect you well understand the context around this API working the way it does today and are merely trying to further the case for broader adoption of your hard work and excellent library, but let's not lose sight of security protocols in the pursuit of advocacy ;)

[benjamin...@gmail.com]

Hey Chris,

Thanks for your concern regarding proper disclosure.

As this is more of a foot gun rather than explicitly a vulnerability in Ecto.SQL. Which has been publicly known since the introduction of the API, then I’m having a hard time seeing how this would fall into that category. I would love if you could expand on this.

As Ecto.SQL does not sanitize the iodata in any way, the user is left to do that job. Not something that I would expect for a high level library, or any ORM. Although it is quite common, probably due to the non trivial task of building a SQL lexer/parser.

Regardless of all of this, we can do better. And I believe we should!