CODEWITHSUNDEEP
pythonsqldjangosql-injectiondjango-2.2
Agent Lu
Agent Lu

Reputation: 104

How to protect SELECT * FROM var1 WHERE var2 statements from SQLInjection

I am making a website in django where I want the user to put in a table id and group id and then return the table and group that the put in. However, I have only found statements that are prone to SQL injection. Does anybody know how to fix this?

mycursor = mydb.cursor()
qry = "SELECT * from %s WHERE group_id = %i;" % (assembly_name, group_id)

mycursor.execute(qry)

return mycursor.fetchall()

Or do something that achieves the same thing?

I have tried doing something like this:

assembly_id = 'peptides_proteins_000005'
group_id = 5

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM %s WHERE group_id = %s", [assembly_id, group_id])

myresult = mycursor.fetchall()

but I get this error:

1064 (42000): 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 ''peptides_proteins_000005' WHERE group_id = 5' at line 1

Upvotes: 0

Views: 82

Answers (1)

Leonard
Leonard

Reputation: 126

It's typically not possible to bind table names. For SELECT statements, the easiest way is to sanitize table name candidates by whitelisting.

Check whether the overhead of using abstraction or some way of constraining user input to the finite set of valid names as part of the user interface may be justified.

Upvotes: 1

Related Questions