How to make SQLite query where both parameter and value are variables?

Question

I'm trying to allow the user to search a SQL table, by first taking their input on which PARAMETER they want to search by, and after that taking their input on which VALUE they want to search for inside that parameter.

I take both inputs, assign them to two different variables, and then pass those variables into a SQL execute command. This works if I'm only passing the value variable and hardcoding the parameter, but not the other way around. This would suggest that the problem is processing the parameter variable, but I don't know what the problem is.

This is going to be for a Django site, but for the moment I'm just testing it as a command line app. It's running Python 3.6.8 and sqlite3.

I've tried hardcoding only the parameter and dynamically inputting the value, which worked, and viceversa, which didn't. I also tried it with different values which hold different value types (ints, varchar, etc.), to make sure it wasn't a problem with the variable type I was passing to the execute command, and they all worked when hardcoding the parameter.

This suggests to me that the problem isn't the variables themselves, but passing a variable as a parameter in an SQL query. I tried looking up people with similar problems, but not only did I not find any, I also didn't find any documentation (official or otherwise) that suggests that it's possible (or desired) to pass a variable as a parameter.

import sqlite3

conn = sqlite3.connect('[project directory]/db.sqlite3')
c = conn.cursor()

def search_entry():
    '''Searches database based on user input, returns rows
    '''

    # Search parameters dictionary
    search_par = {
        '1': 'id',
        '2': 'date',
        '3': 'account',
        '4': 'trans_type',
        '5': 'main_cat',
        '6': 'sub_cat',
        '7': 'name',
        '8': 'amount',
        '9': 'all',
    }
    print('\n')
    for key in search_par:
        print(key, ': ', search_par[key])
    print('\n')

    # Select parameter number to search by
    while True:
        try:
            par_select = str(int(input('Select parameter number to search by: ')))
            if (int(par_select) < 10 and int(par_select) > 0):
                break
            else:
                pass
        except:
            pass
    sel_par = search_par[par_select]  # Select corresponding parameter

    # Select value to search
    while True:
        try:
            id_search = input('Select value to search: ')
            break
        except:
            pass

    c.execute('SELECT * FROM form_entry WHERE (?)=(?)', (sel_par, id_search,))
    rows = c.fetchall()
    for row in rows:
        for col in row:
            print('%s,' % col)
        print('\n')

search_entry()

I expect this to return the results from the query, for example, if the user chose to search by id, and then search for the id 19, I'd expect those 2 variables to be passed to the execute command, and the search conducted. Instead, I get nothing back. No errors, just no output.

I can't speak for SQLite, but in general, you can't pass table or column names as parameters unless you are using "dynamic SQL". Search for how do do dynamic SQL in SQLite and that might help. — daShier
– daShier, Commented Sep 8, 2019 at 19:12
Not really much point in using dynamic sql when the front end programming language can dynamically create an sql- could be a confusing level of indirection, having python that generates a dynamic sql that runs a query; I'd just have the python generate the query — Caius Jard
– Caius Jard, Commented Sep 8, 2019 at 19:18

Caius Jard · Accepted Answer · 2019-09-08 19:16:22Z

2

In 2 words: you can't

SQL only accepts parametization of values, not identifiers, column names, table names etc. The why is a longer conversation but it's something akin to why you can't have variables in a programming language that get their name from another variable

The only thing you can do is build your sql in your programming language; concatenate in the column name and append the parameter value to the collection that the command object uses

Here's a pseudo code (I don't write python, sorry):

c.execute('SELECT * FROM form_entry WHERE '+colnamesarray[i]=?', colvaluesarray[i])

answered Sep 8, 2019 at 19:16

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

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

3 Comments

Nicolas Over a year ago

Ah, that explains it. Thank you. Do you know if it'd be safe and/or desired to do what you suggested?

Arnav Borborah Over a year ago

@Nicolas if it's user input, it's definitely unsafe. But if you can 100% make sure the column name will be valid, then it shouldn't be a problem.

Caius Jard Over a year ago

It shouldn't be user input; it's safe to concatenate column names because you, the developer, know the column names and do not put hacks in there. Do NOT allow your user to specify the column names. The user ONLY provides the input value but this isn't hackable because you put the value in a parameter. It's perfectly fine to build sql by string concat SO LONG AS you concatenate parameter placeholders in and then put the values into the parameters

Arya · Accepted Answer · 2019-09-08 19:23:54Z

0

You Can try to use below format as well

c.execute("SELECT * FROM form_entry WHERE {}" .format(Variable))

answered Sep 8, 2019 at 19:23

Arya

5419 silver badges30 bronze badges

Collectives™ on Stack Overflow

How to make SQLite query where both parameter and value are variables?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related