django raw sql-json参数未按预期执行-语法错误 [英] django raw sql - json params not being executed as expected - syntax error

问题描述

我在django应用（pgsql 9.6后端）中执行rawsql，如下所示：

I execute rawsql within a django app (pgsql 9.6 backend) like so:

obj=AddModel.objects.raw("""
SELECT * FROM "codeaddmodel" \
WHERE ("codeaddmodel"."data" -> 'CodeData') \
@> '[{"street": "New Street" }]'
""")

，它的运行效果非常出色。

and it works brilliantly.

现在，根据Django文档，我执行以下操作，并使用params：

Now, I do the following, as per the django doc, and use params:

term="New Street"

obj=AddModel.objects.raw("""
SELECT * FROM "codeaddmodel" \
WHERE ("codeaddmodel"."data" -> 'CodeData') \
@> '[{"street": %s }]'
""",[term])

这会引发错误：

django.db.utils.ProgrammingError: syntax error at or near "New"

我已经尝试了大约两个小时，而google却使我失败了！

I have tried for around two hours and google has failed me!

推荐答案

您正在尝试在文字中使用占位符：

You're trying to use a placeholder inside a literal:

In [12]: cur.execute("""select ('[{"street": %s}]')::json""", ('New Street',))
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-12-dd23cc772072> in <module>()
----> 1 cur.execute("""select ('[{"street": %s}]')::json""", ('New Street',))

ProgrammingError: syntax error at or near "New"
LINE 1: select ('[{"street": 'New Street'}]')::json

结果是一个字面值，后跟关键字 New 和 Street 和另一个文字。请注意，这如何为SQL注入打开场所：

The result is a literal, followed by the key words New and Street, and another literal. Note how this could open up the venue for SQL injections:

In [42]: cur.execute("""select ('[{"street": %s}]')""",
    ...:             (""") = \' OR true OR \' = (""",))

所以不要在占位符周围手动引用。相反，您应该使用占位符替换整个值并传递一个合适的值，在这种情况下为JSON字符串：

so don't manually quote around placeholders. Instead you should replace the whole value with a placeholder and pass a suitable value, in this case a JSON string:

obj=AddModel.objects.raw("""
SELECT * FROM "codeaddmodel"
WHERE ("codeaddmodel"."data" -> 'CodeData')
@> %s
""", [json.dumps([{'street': term}])])

或者如果使用psycopg2和Django的raw只是通过传递值，则 psycopg2.extras.Json（）

or if using psycopg2 and Django's raw simply passes values through, psycopg2.extras.Json()

这篇关于django raw sql-json参数未按预期执行-语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！