CODEWITHSUNDEEP
pythondjangopostgresql
JohnJ
JohnJ

Reputation: 7056

django raw sql - json params not being executed as expected - syntax error

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.

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])

and this throws the error:

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

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

Upvotes: 0

Views: 437

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

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

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 \' = (""",))

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}])])

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

Upvotes: 5

Related Questions