Can I use python to simplify an SQLite query involving all columns?-CodePudding

I am working on a function which takes an string input, and then returns the name of the column in a database which contains that string.

con =sqlite3.connect('library.db')
cur = con.cursor()
data=cur.execute('''SELECT * FROM table1''')
def search_word():
word = input("Input search word")
for column in data.description:
    cur.execute('''SELECT ? FROM table1 WHERE ?=?''',(str(column[0]),str(column[0]),str(word),))
    output = cur.fetchone()
    print(output)

search_word()

In the above function, inputting print(column[0]) successfully prints the name of every column in the table, so I know that part of the function works. However, when I run the function with a a search word I know is in the database, every iteration produces "None" as an output. What am I doing wrong?

CodePudding user response：

There are a couple of stack overflow issues that address this already. See here and here. In short, column names are not a security issue, so if you want to do anything dynamic with them, you need to use regular python string operations for that part and use parameter binding for just the values. Something like this should work.

for column in data.description:
    query = f'''SELECT {column[0]} FROM table1 WHERE {column[0]} = ?'''
    cur.execute(query,(str(word),))
    output = cur.fetchone()
    print(output)