How do you pass json to a parameterized sql statement using psycopg2?

Question

What's the correct syntax to pass json into a parameterized SQL statement?
How do you safely pass a variable into the json along with it?

I have a table with a field called properties that is typed as a jsonb. Something like this:

CREATE TABLE mytable
(
  id integer NOT NULL,
  ...snip...
  properties jsonb
)

I can query it in pgAdmin as follows:

SELECT id from mytable where properties@>'{"favorites": [{"icecream": "vanilla"}]}';

Now I would like to do it with psycopg2, but I would like to parameterize it.

Normally, this can be done like so:

cursor.execute("SELECT id from mytable where something = (%s)", (123456))

How do you do it with a jsonb? And then how do you parameterize the flavor of ice cream?

Here's how far I've gotten so far.

import psycopg2
from psycopg2.extras import register_default_jsonb, register_default_json, Json

...snip...

register_default_jsonb()
register_default_json()

def who_likes_this_icecream(flavor):
    #where_clause = '{"favorites": [{"icecream": "vanilla"}]}'
    where_clause = Json({"favorites": [{"icecream": "vanilla"}]})
    q = "SELECT id from mytable where properties @> (%s)"

    with get_a_cursor() as cursor:
        cursor.execute(q, (where_clause))
        data = cursor.fetchone()
        r = cursor.fetchone()
        return r[0]['id']


id = who_likes_this_icecream('vanilla')
print (id)

I tried using the Json wrapper in psycopg2 and received:

TypeError: 'Json' object does not support indexing

I tried sending the JSON in as a formatted string and received:

TypeError: not all arguments converted during string formatting

Also, I am not sure how to safely send in the variable flavor without introducing an injection issue.

I don't think the parentheses in your SQL are necessary; `"SELECT id from ... where properties @> %s"`. — chepner, Sep 17 '17 at 14:37
`cursor.execute(q, (where_clause,))` - comma after `where_clause`. — Abelisto, Sep 17 '17 at 15:24
Doh. I see it. The comma after, makes it a tuple. Thanks @Abelisto — 101010, Sep 18 '17 at 12:34
How about parameterizing the flavor of icecream so as to avoid some kind of injection attack. Is it possible with JSON? Any examples? — 101010, Sep 18 '17 at 12:35
Using parametrized `cursor.execute()` protects you against SQL injection. — Abelisto, Sep 18 '17 at 16:29
So just doing a string replacement on the JSON where clause is enough? — 101010, Sep 18 '17 at 16:38

How do you pass json to a parameterized sql statement using psycopg2?

0 Answers0