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') \ @>

Solution 1:

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