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, Dec 14 '19 at 09:48
first because I need to add external parameters to my query, second maybe it be helpful to avoid injection attacks — Braiano, Dec 14 '19 at 09:50
Did you try this? https://stackoverflow.com/a/37186271/4626254 — Underoos, Dec 14 '19 at 09:54
@Braiano use `%(id)s` instead of `:id`, i.e. `cursor.execute("INSERT INTO api.mytable(id) VALUES (%(id)s);", {"id": 1})` — Alasdair, Dec 14 '19 at 09:58
@SukumarRdjf that’s a dangerous suggestion, it’s vulnerable to SQL injection. — Alasdair, Dec 14 '19 at 09:59

score 1 · Answer 1 · answered Dec 14 '19 at 10:49

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 '19 at 10:49

McLovin

555
8
20

Thanks for your answer, however since I have multiple inputs this solution is not quite fit. I just fixed it. – Braiano Dec 14 '19 at 11:11
It should be `[id_in]` or `(id_in,)` (add a comma to make it a tuple). – Alasdair Dec 14 '19 at 12:26

Inserting error in mysql query using python

1 Answers1