Sqlite3 search by column with user input

Question

I have this following query: result: list = queryDatabase('''SELECT * FROM clients WHERE :col = :keyword''', {"col":column, "keyword":keyword})

I want to have the column and the keyword to search for in the database be given as input by the user. The keyword works fine, but I can't seem to get the column name to translate into SQL, so :col should take the value given in the tuple as the second parameter, and search by that column, with the keyword.

I'm using python 3 and sqlite3.

I don't believe you can bind table or column names. Only value parameters can be bound that way. That's true in Java and the other languages I know that interact with SQL. — duffymo, Jun 06 '21 at 15:47
Does this answer your question? [How do you escape strings for SQLite table/column names in Python?](https://stackoverflow.com/questions/6514274/how-do-you-escape-strings-for-sqlite-table-column-names-in-python) — Jonathan Feenstra, Jun 06 '21 at 15:51

score 0 · Answer 1 · answered Jun 06 '21 at 16:42

0

Maybe you can try this:

def bind_columns(sql:str, *args):
    for item in args:
        # try to check if item is not SQL command for security
        sql = sql.replace(":col", item, 1)
    return sql

result: list = queryDatabase(bind_columns('''SELECT * FROM clients WHERE :col = :keyword''', column), {"keyword":keyword})

answered Jun 06 '21 at 16:42

AbdulelahAGR

86
1
8

I ended up using this, letting the user pick a number corresponding to the available column strings (server generated input), so there is no risk of SQL injection, thanks – daanneek Jun 06 '21 at 19:06

Sqlite3 search by column with user input

1 Answers1