CODEWITHSUNDEEP
pythonsqlite
OrdinaryProgrammer
OrdinaryProgrammer

Reputation: 223

Passing a column name in a SELECT statement in Python

    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"

Upvotes: 4

Views: 4391

Answers (2)

user1907906
user1907906

Reputation:

You can only set parameters using ?, not table or column names.

You could build a dict with predefined queries.

queries = {
    "foo": "SELECT * FROM PacketManager WHERE foo = ?",
    "bar": "SELECT * FROM PacketManager WHERE bar = ?",
    "foo_bar": "SELECT * FROM PacketManager WHERE foo = ? AND bar = ?",
}

# count == 1
cursor.execute(queries[filters[0], parameters[0])

# count == 2
cursor.execute(queries[filters[0] + "_" + queries[filters[1], parameters[0])

This approach will make you save from SQL injection in filters[0].

Upvotes: 6

Martijn Pieters
Martijn Pieters

Reputation: 1123400

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.

Upvotes: 10

Related Questions