在Django SQL查询中转义%会导致列表索引超出范围错误。

huangapple go评论97阅读模式
英文:

Escaping % in django sql query gives list out of range

问题

我尝试在pgadmin中运行以下SQL查询,它成功运行:

  1. SELECT <columns>
  2. FROM <tables>
  3. WHERE
  4. date_visited >= '2023-05-26 07:05:00'::timestamp
  5. AND date_visited <= '2023-05-26 07:07:00'::timestamp
  6. AND url LIKE '%/mymodule/api/myurl/%';

我想在Django REST端点中调用相同的URL。因此,我编写了以下代码:

  1. with connection.cursor() as cursor:
  2. cursor.execute('''
  3. SELECT <columns>
  4. FROM <tables>
  5. WHERE
  6. date_visited >= %s::timestamp
  7. AND date_visited <= %s::timestamp
  8. AND url LIKE %s;
  9. ''', [from_date, to_date, url])

但是它给我报了一个列表索引超出范围的错误。我猜我在 %%s%% 部分犯了一个错误。我尝试用 %% 转义原始查询中的 %,但似乎不起作用。这里出了什么问题?

英文:

I tried running following SQL query in pgadmin and it worked:

  1. SELECT &lt;columns&gt;
  2. FROM &lt;tables&gt;
  3. WHERE
  4. date_visited &gt;= &#39;2023-05-26 07:05:00&#39;::timestamp
  5. AND date_visited &lt;= &#39;2023-05-26 07:07:00&#39;::timestamp
  6. AND url LIKE &#39;%/mymodule/api/myurl/%&#39;;

I wanted to call the same url in django rest endpoint. So, I wrote code as follows:

  1. with connection.cursor() as cursor:
  2. cursor.execute(&#39;&#39;&#39;
  3. SELECT &lt;columns&gt;
  4. FROM &lt;tables&gt;
  5. WHERE
  6. date_visited &gt;= &#39;%s&#39;::timestamp
  7. AND date_visited &lt;= &#39;%s&#39;::timestamp
  8. AND url LIKE &#39;%%%s%%&#39;;
  9. &#39;&#39;&#39;, [from_date, to_date, url])

But it is giving me list index out of range error. I guess I have made mistake with &#39;%%%s%%&#39;. I tried to escale % in original query with %%. But it does not seem to work. Whats going wrong here?

答案1

得分: 2

你可以使用以下方式进行筛选:

  1. MyModel.objects.filter(
  2. date_visited__between=('2023-05-26 07:05:00', '2023-05-26 07:07:00'),
  3. url__contains='/mymodule/api/myurl/',
  4. )

在这里,我们可以使用 __contains 查询来进行区分大小写的匹配。

英文:

You can <code>.filter(&hellip;)</code>&nbsp;<sup>[Django-doc]</sup> with:

<pre><code>MyModel.objects.filter(
date_visited__between=('2023-05-26 07:05:00', '2023-05-26 07:07:00'),
<b>url__contains='/mymodule/api/myurl/'</b>,
)</code></pre>

Here we can use the __contains lookup&nbsp;<sup>[Django-doc]</sup> for a case-sensitive match.

答案2

得分: 2

准备好的语句会自动添加引号,以防止 SQL 注入。

因此,您的代码看起来像这样:

  1. with connection.cursor() as cursor:
  2. cursor.execute('''
  3. SELECT <columns>
  4. FROM <tables>
  5. WHERE
  6. date_visited >= %s::timestamp
  7. AND date_visited <= %s::timestamp
  8. AND url LIKE %s;
  9. ''', [from_date, to_date, "%" + url + "%"])
英文:

Prepared statements add quotes auto matically to prevent SQL injection

So your code would look like

  1. with connection.cursor() as cursor:
  2. cursor.execute(&#39;&#39;&#39;
  3. SELECT &lt;columns&gt;
  4. FROM &lt;tables&gt;
  5. WHERE
  6. date_visited &gt;= %s::timestamp
  7. AND date_visited &lt;= %s::timestamp
  8. AND url LIKE %s;
  9. &#39;&#39;&#39;, [from_date, to_date,&quot;%&quot; + url + &quot;%&quot;])

huangapple
  • 本文由 发表于 2023年5月28日 22:40:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/76352024.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定