英文:
Escaping % in django sql query gives list out of range
问题
我尝试在pgadmin中运行以下SQL查询，它成功运行：
SELECT <columns>
FROM <tables>
WHERE
date_visited >= '2023-05-26 07:05:00'::timestamp
AND date_visited <= '2023-05-26 07:07:00'::timestamp
AND url LIKE '%/mymodule/api/myurl/%';
我想在Django REST端点中调用相同的URL。因此，我编写了以下代码：
with connection.cursor() as cursor:
cursor.execute('''
SELECT <columns>
FROM <tables>
WHERE
date_visited >= %s::timestamp
AND date_visited <= %s::timestamp
AND url LIKE %s;
''', [from_date, to_date, url])
但是它给我报了一个列表索引超出范围的错误。我猜我在 %%s%% 部分犯了一个错误。我尝试用 %% 转义原始查询中的 %，但似乎不起作用。这里出了什么问题？
英文:
I tried running following SQL query in pgadmin and it worked:
SELECT <columns>
FROM <tables>
WHERE
date_visited >= '2023-05-26 07:05:00'::timestamp
AND date_visited <= '2023-05-26 07:07:00'::timestamp
AND url LIKE '%/mymodule/api/myurl/%';
I wanted to call the same url in django rest endpoint. So, I wrote code as follows:
with connection.cursor() as cursor:
cursor.execute('''
SELECT <columns>
FROM <tables>
WHERE
date_visited >= '%s'::timestamp
AND date_visited <= '%s'::timestamp
AND url LIKE '%%%s%%';
''', [from_date, to_date, url])
But it is giving me list index out of range error. I guess I have made mistake with '%%%s%%'. I tried to escale % in original query with %%. But it does not seem to work. Whats going wrong here?
答案1
得分: 2
你可以使用以下方式进行筛选：
MyModel.objects.filter(
date_visited__between=('2023-05-26 07:05:00', '2023-05-26 07:07:00'),
url__contains='/mymodule/api/myurl/',
)
在这里，我们可以使用 __contains 查询来进行区分大小写的匹配。
英文:
You can <code>.filter(…)</code> <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 <sup>[Django-doc]</sup> for a case-sensitive match.
答案2
得分: 2
准备好的语句会自动添加引号，以防止 SQL 注入。
因此，您的代码看起来像这样：
with connection.cursor() as cursor:
cursor.execute('''
SELECT <columns>
FROM <tables>
WHERE
date_visited >= %s::timestamp
AND date_visited <= %s::timestamp
AND url LIKE %s;
''', [from_date, to_date, "%" + url + "%"])
英文:
Prepared statements add quotes auto matically to prevent SQL injection
So your code would look like
with connection.cursor() as cursor:
cursor.execute('''
SELECT <columns>
FROM <tables>
WHERE
date_visited >= %s::timestamp
AND date_visited <= %s::timestamp
AND url LIKE %s;
''', [from_date, to_date,"%" + url + "%"])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论