CODEWITHSUNDEEP
pythonsqlpostgresqlpsycopg2
Alex
Alex

Reputation: 2976

psycopg2 - how do I escape single quote when updating a Json column?

I am trying to update a json column of a table but got stuck because one of the value in the json has a single quote.

my json string looks like this

{"keyword": "women's shoes"}

Here is my code.

criteria_str = json.dumps(criteria)
sql_str = """update user_saved_search_backup set criteria = '%s'::json where id = %d;""" % (criteria_str, id)                    
write_cursor.execute(sql_str)

NOTE: I am using postgreSQL 9.2 which lacks json function support so I had to do this in python

Any help would be appreciated.

Upvotes: 1

Views: 5426

Answers (1)

sushain97
sushain97

Reputation: 2802

Use a parametrized query. Never directly insert user input into your SQL queries, it exposes you to SQL-injection attacks.

Like this:

sql_str = """update user_saved_search_backup set criteria = %s::json where id = %s;"""
write_cursor.execute(sql_str, (criteria_str, id))

Here are some helpful docs on this topic.

Upvotes: 7

Related Questions