Passing a column name in a SELECT statement in Python

Question

    if count == 1:
        cursor.execute("SELECT * FROM PacketManager WHERE ? = ?", filters[0], parameters[0])
        all_rows = cursor.fetchall()

    elif count == 2:
        cursor.execute("SELECT * FROM PacketManager WHERE ? = ? AND ? = ?", filters[0], parameters[0], filters[1], parameters[1])
        all_rows = cursor.fetchall()

    elif count == 3 :
        cursor.execute("SELECT * FROM PacketManager WHERE ? = ? AND ? = ? AND ? = ?", filters[0], parameters[0], filters[1], parameters[1], filters[2], parameters[2])
        all_rows = cursor.fetchall()

This is a code snippet in my program. What I'm planning to do is pass the column name and the parameter in the query.

The filters array contains the columnnames, the parameter array contains the parameters. The count is the number of filters set by the user. The filters and paramters array are already ready and have no problem. I just need to pass it to the query for it to execute. This give me an error of "TypeError: function takes at most 2 arguments"

Answer 1

You cannot use SQL parameters to interpolate column names. You'll have to use classic string formatting for those parts. That's the point of SQL parameters; they quote values so they cannot possibly be interpreted as SQL statements or object names.

The following, using string formatting for the column name works, but be 100% certain that the filters[0] value doesn't come from user input:

cursor.execute("SELECT * FROM PacketManager WHERE {} = ?".format(filters[0]), (parameters[0],))

You probably want to validate the column name against a set of permissible column names, to ensure no injection can take place.