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

Question

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!

From my understanding you should wrap the placeholder %s in "": ..{"street": "%s" }.. — user2390182
– user2390182, Commented Nov 21, 2017 at 7:32
@schwobaseggl The whole point of using placeholders is that you should not need to manually quote anything. — Ilja Everilä
– Ilja Everilä, Commented Nov 21, 2017 at 7:35

Ilja Everilä · Accepted Answer · 2017-11-21 11:41:24Z

5

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

edited Nov 21, 2017 at 11:41

answered Nov 21, 2017 at 7:35

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JohnJ Over a year ago

@llja@ absolute genius - yes, I was using a placeholder within a string literal and hence the problems. I am now using the json dumps as you suggested and it works beautifully. Just as a side note, could you post an example of how psycopg2.extras.Json() could be used? I tried to get through the documentation, but it is not clear what I need to import to use psycopg..

Ilja Everilä Over a year ago

Just put the psycopg part there in case you happen to be using it underneath and Django's raw() simply passes through params. If that's the case, from psycopg2.extras import Json and instead of json.dumps wrap the desired value with Json(): [Json([{'street': term}])]. In the end they both work pretty much the same.

JohnJ Over a year ago

@llja@ thanks a tonne again for your super detailed answer and the explanation. Accepted your answer.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related