Any docs/info for cases where Ecto will interpolate parameters into a raw SQL query?

Is there documentation for what cases Ecto will interpolate parameters into a raw SQL query?

Basically, I found online that Ecto will not interpolate parameters into CREATE queries, however i’m starting to find several queries of mine are passing $n directly to the database, instead of the value I have specified in params. It doesn’t appear to matter if it is SELECT or INSERT or UPDATE.

For example Repo1.query("SELECT [Field] FROM [Database].[dbo].[Table] WHERE [Field] > $1", [value]) is not interpolating and sending $1 as the value. This seems to be hit or miss, and i’m having this issue with both the Postgrex and Tds SQL backends.

This fails in Postgres as it’s not valid syntax, however MSSQL simply returns incorrect results, even testing the raw query in SQL Server Studio, which match the results i’m getting back through Ecto, so it’s definitely not interpolating the value at all.

What causes this, and how can I avoid it? Interpolation works with some queries but not others, seemingly arbitrarily.

Edit: I should note that using #{} to directly place the values works as expected for some of these queries, but not when I need a dynamic field interpolation for example. I had to convert that to use Repo.aggregate…

Something else is definitely going on here. Queries that have worked hundreds of times before are now failing randomly, saying the transaction has been aborted.

It’s the oddest thing… there is absolutely nothing wrong with this query. In fact I run the exact same query successfully in a very similar function!

** (Postgrex.Error) ERROR 25P02 (in_failed_sql_transaction) current transaction is aborted, commands ignored until end of transaction block

    query: SELECT max(c0."name") FROM "customer_contacts" AS c0
    (ecto_sql 3.5.4) lib/ecto/adapters/sql.ex:751: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql 3.5.4) lib/ecto/adapters/sql.ex:684: Ecto.Adapters.SQL.execute/5
    (ecto 3.5.6) lib/ecto/repo/queryable.ex:229: Ecto.Repo.Queryable.execute/4
    (ecto 3.5.6) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3
    (ecto 3.5.6) lib/ecto/repo/queryable.ex:157: Ecto.Repo.Queryable.one!/3
    (ship_import 0.1.0) lib/ship_import.ex:73: ShipImport.save_last/2
    (ecto_sql 3.5.4) lib/ecto/adapters/sql.ex:1027: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
    (db_connection 2.3.1) lib/db_connection.ex:1444: DBConnection.run_transaction/4

Copying that query verbatim directly into psql returns the expected result.

Slight update, still can’t interpolate my queries, however the transaction issue was caused by a type in one of the queries.

Interestingly Postgrex was completely off in which query had failed

Interpolating creates a very high risk for SQL injection, so Ecto only “interpolates” when it can see the value at compile time and guarantee safety. eg:

User |> where(id: 1) |> Repo.one

becomes

FROM "users" AS u0 WHERE (u0."id" = 1)

However if you pass in a variable it can’t ensure it’s safe at compile time and uses a parameter.

This feels like an XY problem. What are you trying to do overall?

1 Like

My use case is importing data from another system. I’m mostly using raw sql queries because the source dbms is MSSQL whereas the target is Postgres, between that, and the lack of schema and the complexity of some of the queries i’ve been unable to use Ecto’s ORM features.

The data i’m interpolating is system generated so i’m not too concerned about that.

Gotcha. I guess the bit I’m not super clear on still is what values in your Repo.query you are finding impossible to input with normal ecto queries. Schemaless queries (Schemaless queries — Ecto v3.5.7) should allow you to query arbitrary tables. If the issue is that you need to be able to query random database then that does get trickier, since Ecto expects that you’ve got a connection pool configured per database.

This $1 syntax is what is used for prepared queries, which is used by default with Ecto and Postgrex. What does your postgrex configuration look like? Are you connecting directly to Postgrex (and not through something like pgbouncer)?

I’m definitely not a T-SQL expert, but it looks like the TDS adapter prepares the statement with variables named @1 @2 etc:

which aligns with usage in the Microsoft documentation with queries like:

SELECT *
FROM Sales.SalesOrderDetail AS sod
INNER JOIN Production.Product AS p ON sod.ProductID = p.ProductID
WHERE SalesOrderID = @Param
ORDER BY Style DESC;