How to store single quote to postgres in python [duplicate]

Question

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

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. owasp.org/www-community/attacks/SQL_Injection — PacketLoss
– PacketLoss, Commented Mar 22, 2021 at 3:47
If you're using psycopg the docs cover your exact issue and how to correctly parametrize your queries. psycopg.org/docs/… — PacketLoss
– PacketLoss, Commented Mar 22, 2021 at 3:52
@PacketLoss thanks sir, i am newbie in python and will be to use parameterized queries — Arif hidayah
– Arif hidayah, Commented Mar 22, 2021 at 3:57

Grismar · Accepted Answer · 2021-03-22 03:47:35Z

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

answered Mar 22, 2021 at 3:47

Grismar

32.4k6 gold badges42 silver badges68 bronze badges

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

2 Comments

Grismar Over a year ago

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.

ThunderWiring Over a year ago

when there is more than 1 argument to assign (in this case 3 for each %s), then the 2nd arg to execute should be a sequence (list) i.e. cursor.execute(sql, [arg1, arg2,...etc.])

Collectives™ on Stack Overflow

How to store single quote to postgres in python [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related