0

i have an error when i'am stored data from json response to postgresql. How to store data with a single quote in python?

my json response :

{
"status": 2,
"records": 1,
"message": "OK",
"id": "131",
"name": "SUPA'AT",
"address": "RUNGKUT SA'GIRI"
}

my python code :

sql = "UPDATE my_data SET name='"+str(response["name"])+"', address='"+str(response["address"])+"' WHERE id='"+id+"'"
cursor.execute(sql)
conn.commit()
Arif hidayah
  • 47
  • 1
  • 6
  • 2
    Before even going further. You need to stop creating your SQL queries like this. This is prime SQL injection material. You NEED to use parametrized queries. https://owasp.org/www-community/attacks/SQL_Injection – PacketLoss Mar 22 '21 at 03:47
  • 1
    If you're using `psycopg` the `docs` cover your exact issue and how to correctly parametrize your queries. https://www.psycopg.org/docs/usage.html#the-problem-with-the-query-parameters – PacketLoss Mar 22 '21 at 03:52
  • @PacketLoss thanks sir, i am newbie in python and will be to use parameterized queries – Arif hidayah Mar 22 '21 at 03:57
  • @NirAlfasi mean to replace single quote to double first? – Arif hidayah Mar 22 '21 at 03:58

1 Answers1

2

Do this instead:

sql = "UPDATE my_data SET name = %s, address = %s WHERE id = %s"
cursor.execute(sql, response["name"], response["address"], id)
conn.commit()

Assuming you assigned id somewhere previously.

The code you shared is a SQL code injection nightmare, as indicated by @PacketLoss in the comments and explained on https://owasp.org/www-community/attacks/SQL_Injection

Grismar
  • 27,561
  • 4
  • 31
  • 54
  • 1
    You'll find that this is safer, easier to read and as a bonus, also solves you single quote problem, since `cursor.execute()` will appropriately escape it for you. – Grismar Mar 22 '21 at 03:59