我可以让Ecto记录原始SQL吗？ [英] Can I get Ecto to log raw SQL?

问题描述

我正在构建一个类似这样的Ecto查询：

I am building an Ecto query like this:

from item in query,
where:  like(item.description, ^"%#{text}%")

我担心这允许SQL 文本注入。在尝试解决此问题之前，我想看看查询是如何实际发送到数据库的。

I'm concerned that this allows SQL injection in text. Before trying to fix that, I want to see how the query is actually sent to the database.

如果我或查看记录的内容，我看到了一些SQL，但是无效。

If I inspect the query or look at what is logged, I see some SQL, but it's not valid.

例如，检查查询显示以下内容：

For instance, inspecting the query shows me this:

{"SELECT i0.\"id\", i0.\"store_id\", i0.\"title\", i0.\"description\" 
  FROM \"items\" AS i0 WHERE (i0.\"description\" LIKE $1)",
 ["%foo%"]}

当我将此查询传递给 Repo.all 时，它会记录以下内容：

When I pass this query to Repo.all, it logs this:

SELECT i0."id", i0."store_id", i0."title", i0."description"
  FROM "items" AS i0 WHERE (i0."description" LIKE $1) ["%foo%"]

但是如果我复制并将其粘贴到 psql 中，PostgreSQL给我一个错误：

But if I copy and paste that into psql, PostgreSQL gives me an error:

错误：42P02 ：没有参数$ 1

ERROR: 42P02: there is no parameter $1

似乎Ecto可能实际上正在执行参数化查询，如下所示：

It seems as though Ecto may actually be doing a parameterized query, like this:

PREPARE bydesc(text) AS SELECT i0."id", 
  i0."store_id", i0."title", i0."description" 
  FROM "items" AS i0 WHERE (i0."description" LIKE $1);
EXECUTE bydesc('foo');

如果是这样，我认为这样可以防止SQL注入。

If so, I think that would prevent SQL injection. But I'm just guessing that this is what Ecto does.

如何查看Ecto正在执行的实际SQL？

How can I see the actual SQL that Ecto is executing?

推荐答案

Ecto仅使用准备好的语句。使用ecto查询语法时，无法引入SQL注入。查询语法在编译时验证不可能进行SQL注入。

Ecto uses only prepared statements. When using ecto query syntax, introducing SQL injection is not possible. The query syntax verifies at compile-time that no SQL injection is possible.

由于以下几个原因，很难确切地显示执行的查询：

Showing exactly the queries executed might be difficult because of couple reasons:

• Postgrex（因此也称为Ecto）使用了postgresql二进制协议（而不是最常见但效率较低的文本协议），因此 PREPARE 查询实际上从未以字符串形式存在。


• 在大多数情况下，您会看到的只是一个初始 PREPARE 64237612638712636123（...）AS ... ，后来出现了很多 EXECUTE 64237612638712636123（...）的帮助不大。试图将彼此联系起来是可怕的。

• Postgrex (and hence Ecto) uses the postgresql binary protocol (instead of the most common, but less efficient, text protocol), so the PREPARE query never actually exists as a string.
• For most cases all you would see would be one initial PREPARE 64237612638712636123(...) AS ... and later a lot of EXECUTE 64237612638712636123(...) which isn't that helpful. Trying to relate one to another would be horrible.

根据我的经验，大多数此类软件都使用prepare语句并记录它们而不是原始查询，因为它对于理解系统的行为更加有帮助。

From my experience most software of that kind, use prepare statements and log them instead of raw queries, since it's much more helpful in understanding the behaviour of the system.

这篇关于我可以让Ecto记录原始SQL吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！