Inserting error in mysql query using python

Question

I am trying to insert values into a row of mysql database in a way that not be vulnerable to injection, but gives my syntax error. This is a piece of my code which causes the error:

cursor.execute("INSERT INTO api.mytable(id) VALUES (:id);", {"id": 1})

and error:

ERROR in connection: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':id)' at line 1")

code you please tell me what's the wrong with my code?

Why not just pass the value directly instead of using dynamic parameters? like format the string and executing it. — Underoos
– Underoos, Commented Dec 14, 2019 at 9:48
first because I need to add external parameters to my query, second maybe it be helpful to avoid injection attacks — Braiano
– Braiano, Commented Dec 14, 2019 at 9:50
@Braiano use %(id)s instead of :id, i.e. cursor.execute("INSERT INTO api.mytable(id) VALUES (%(id)s);", {"id": 1}) — Alasdair
– Alasdair, Commented Dec 14, 2019 at 9:58
@SukumarRdjf that’s a dangerous suggestion, it’s vulnerable to SQL injection. — Alasdair
– Alasdair, Commented Dec 14, 2019 at 9:59

user5658788 · Accepted Answer · 2019-12-14 10:49:13Z

1

I am assuming id is given as some kind if input! Hence you can always check for the required format and allow only required ones! This is to avoid SQL injection!. Hence the natural formatting as shown below should do the job! And this is very basic level checking!

id_in = input("Here is the id taken " )  ## can be through any source . It is just an example 

if isinstance(id_in,int): ##Please mention the required format here I am assuming it as integer
    cursor.execute("INSERT INTO api.mytable(id) VALUES (%s);", (id_in))
else:
    ##do some  stuff here

answered Dec 14, 2019 at 10:49

user5658788

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

2 Comments

Braiano Over a year ago

Thanks for your answer, however since I have multiple inputs this solution is not quite fit. I just fixed it.

Alasdair Over a year ago

It should be [id_in] or (id_in,) (add a comma to make it a tuple).

Collectives™ on Stack Overflow

Inserting error in mysql query using python

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related