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"

Martijn Pieters · Accepted Answer · 2015-04-15 09:20:50Z

10

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.

answered Apr 15, 2015 at 9:20

Martijn Pieters

1.1m325 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

OrdinaryProgrammer Over a year ago

Thanks for the explanation, now I understand why it doesnt allow it :)

score 6 · Accepted Answer · 2015-04-15 09:29:41Z

6

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].

edited Apr 15, 2015 at 9:29

answered Apr 15, 2015 at 9:20

user1907906

3 Comments

OrdinaryProgrammer Over a year ago

Thank you so much! I have read this dictionary function and i didnt know how to put it in my code. Thanks a lot! :D

OrdinaryProgrammer Over a year ago

Hello, I keep on having this error and i dont know what causes it. Whenever it runs the double filter i keep on getting this error TypeError: function takes at most 2 arguments (3 given) The cpode is show below: cursor.execute(queries[filters[0] + '_' + filters[1]], (parameters[0],), (parameters[1],))

OrdinaryProgrammer Over a year ago

Okay, i just found out my stupid error.lol parameter should be: , (parameters[0], parameters[1],)

Collectives™ on Stack Overflow

Passing a column name in a SELECT statement in Python

2 Answers 2

1 Comment

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Linked

Related