0

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
Agent Lu
  • 104
  • 13
  • you could try to use the Django ORM, its protected against these kind of security risks. – hansTheFranz Jun 27 '19 at 21:52
  • You add the arguments as a list or tuple as the second argument to the `execute` call instead of using `%` formatting. – Klaus D. Jun 27 '19 at 21:54
  • The second argument to `cursor.execute` is for parameters, as seen in [the documentation](https://docs.djangoproject.com/en/2.2/topics/db/sql/#executing-custom-sql-directly) – manveti Jun 27 '19 at 21:54
  • @KlausD. That does not work when you have a variable in the table spot – Agent Lu Jun 27 '19 at 21:54
  • @CraigMeier See above – Agent Lu Jun 27 '19 at 21:55
  • As Ivan mentioned, you should use the ORM. Put the model names in a dictionary for example. – Daniel Roseman Jun 27 '19 at 22:13
  • @DanielRoseman My database is too big to put put in models. It would take around a day of loading for all the tables to be in the models, and I only need to do 1 or 2 queries on the database. – Agent Lu Jul 01 '19 at 15:44

1 Answers1

1

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.

Leonard
  • 106
  • 4