Python placing a varible into a sql FROM statement

Question

Hi am trying to make a sql statement that I able to give the table and a where in python

SQL = "SELECT * FROM (%s) WHERE team_number=(%s) AND match_number=(%s);"
DATA = ('data', 2, 3)
cur.execute(SQL, DATA)
query = cur.fetchall()

The error that I get is psycopg2.ProgrammingError: syntax error at or near "'data'"

and SELECT * FROM ('data') WHERE team_number=(2) and match_number=(3)

Note: I am using (%s) to stop sql injections

Answer 1

Most prepared statements APIs, including Python's, do not allow the table or column names to be parameters. Instead, you would have to hard code the table name:

SQL = "SELECT * FROM data WHERE team_number=(%s) AND match_number=(%s);"
DATA = (2, 3)
cur.execute(SQL, DATA)
query = cur.fetchall()

The reason is that allowing the table name to be a parameter might expose you to a security risk. Imagine if someone were to hack your UI drop down and then choose the user table. Then, they might get access to data they should not be seeing.