SQLite dynamically bind column name from user input

Question

Little did I know at first. I thought I could just use an f string. After reading about insertion attacks (by reading I mean a stick-man comic strip) and about SQLite parameters with the dangling comma, I have this.

stat_field = input("Enter stat field: ") 
query = "SELECT player_name, (?) FROM stattable"
conn = sqlite3.connect('pitches.db')
df = pd.read_sql_query(query, conn, params=(stat_field,))
conn.close()

If I type 'pfx_x' at the prompt, a column in stattable, the dataframe looks like this.

    player_name   (?)
0   Mike Mayers  pfx_x
1   Mike Mayers  pfx_x
2   Mike Mayers  pfx_x

Any idea how to get the actual data for that last column?

Good idea, however, it just changes the column name from (?) to ?. I thought if that's the column name, I'll just put pfx_x in parentheses but then it's an incorrect number of bindings error. — Steven Newton
– Steven Newton, Commented Feb 28, 2021 at 0:21
This doesn't seem possible. Maybe look up the string in a list of columns to validate that it's a safe/valid choice, then run the appropriate query without a bound column name like this. — ggorlen
– ggorlen, Commented Feb 28, 2021 at 0:35
If I change the value in the script, it works and I get this. player_name pfx_x 0 Mike Mayers 0.37 1 Mike Mayers 0.30 2 Mike Mayers -0.77` (in column form) — Steven Newton
– Steven Newton, Commented Feb 28, 2021 at 0:47
Yes, if you hardcode pfx_x into the query string, it will work of course. But binding isn't the same as f-string interpolation. It only works on variables related to values in the table, not the query itself, if I understand correctly. So binding will work if you say WHERE player_name=? and pass in "Mike Mayers" as the bound variable but you can't say SELECT * FROM ?; and try to bind that with "stattable". See this even though it's not SQLite, it should still apply. — ggorlen
– ggorlen, Commented Feb 28, 2021 at 1:00

Steven Newton · Accepted Answer · 2021-02-28 05:53:42Z

0

I was slow to understand this but ggorlen pointed me in the right direction. Basically, binding variables, or placeholders, cannot be in the SELECT/FROM statements because placeholders are designed to alter a pre-existing statement. The user can, however, choose player_name because that field is always available. I had to put all the stats I might use in the original query but I could speed up the processing by adding "WHERE player_name = ?" and then changing params to params=(player_name,).

answered Feb 28, 2021 at 5:53

Steven Newton

17111 bronze badges

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

Collectives™ on Stack Overflow

SQLite dynamically bind column name from user input

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related