Python Forum

I am creating a query on the fly based on user inputs. I have separated my code into building the SELECT statements, the JOIN caluses and finally the WHERE clauses. To prevent SQL injection I am trying to use the qmark formatting and passing the variable for the user inputs rather than using Python string operations to create the query. I have the actual query snippets in a separate class so it's easier to debug them.

Thisis a subset of the code that builds the query

def search_contact(self):
	checkboxvalues = self.get_checkbox_values()
	cmd = AnimalTrakker_Query_Code.display_start_select_contacts_table  
	cmdjoinclause = ""  
	cmdwhereclause = ""  
	if checkboxvalues[0] :  
		cmd = cmd + AnimalTrakker_Query_Code.display_member_name_true_select  
	if checkboxvalues[1]:  
		cmd = cmd +AnimalTrakker_Query_Code.display_farm_name_true_select  
	if checkboxvalues[2]:  
		cmd = cmd + AnimalTrakker_Query_Code.display_flock_prefix_true_select 
		cmdjoinclause = AnimalTrakker_Query_Code.join_flock_prefix_table
	cmd = cmd + "FROM contacts_table "  
	cmd = cmd + cmdjoinclause
	cmdvariables = ""
	search_name_data_entry = self.contact_name.get()
	if search_name_data_entry:  
		cmdwhereclause = 'WHERE contacts_table.contact_last_name LIKE (?)'  
	    cmdvariables = '"%'+search_name_data_entry+'%",'  
	     print("cmdvariables contains "+cmdvariables)  
	search_prefix_data_entry = self.farm_prefix.get()  
	print(search_prefix_data_entry)  
	if search_prefix_data_entry:  
		if search_name_data_entry:  
			cmdwhereclause = cmdwhereclause + 'AND flock_prefix_table.flock_prefix LIKE (?)'  
	        cmdvariables = cmdvariables + ' "%' + search_prefix_data_entry + '%",'  
	        print("cmdvariables contains "+cmdvariables)  
		else:  
			cmdwhereclause = cmdwhereclause + 'WHERE flock_prefix_table.flock_prefix LIKE (?)'  
	        cmdvariables = cmdvariables + '"%' + search_prefix_data_entry + '%",'  
	        print("cmdvariables contains "+cmdvariables)  
	if cmdvariables:  
		cmdvariables = " (" + cmdvariables + ")"
		cmd = cmd + cmdwhereclause  
	print("command contains "+cmd) 
	if cmdvariables:  
		cmdvariables = " (" + cmdvariables + ")"
		cmd = cmd + cmdwhereclause  
	print("command contains "+cmd)  
	connection = sqlite3.connect(config.currentdatabase)  
	resultcursor = connection.cursor()  
	if cmdvariables:  
		resultcursor.execute(cmd, cmdvariables)  
	else:  
		resultcursor.execute(cmd)  
	results = resultcursor.fetchall()

 

The query snippets in the query class are

display_start_select_contacts_table ="""
	SELECT
		contacts_table.id_contactsid
"""
display_member_name_true_select ="""
    , contacts_table.contact_first_name
    """
display_farm_name_true_select = """
    , contacts_table.contact_company
    """
display_flock_prefix_true_select = """
    , flock_prefix_table.flock_prefix
    """
join_flock_prefix_table = """
	LEFT JOIN owner_registration_table
    	ON contacts_table.id_contactsid = owner_registration_table.id_contactsid
    LEFT JOIN flock_prefix_table 
    	ON flock_prefix_table.id_flockprefixid = owner_registration_table.id_flockprefixid
	"""

When I run the program after entering in McG in the contact last name text entry box I get the following output.
cmdvariables contains ("%McG%",)
command contains
SELECT
contacts_table.id_contactsid

, contacts_table.contact_first_name

, contacts_table.contact_company

, flock_prefix_table.flock_prefix
FROM contacts_table
LEFT JOIN owner_registration_table
ON contacts_table.id_contactsid = owner_registration_table.id_contactsid
LEFT JOIN flock_prefix_table
ON flock_prefix_table.id_flockprefixid = owner_registration_table.id_flockprefixid
WHERE contacts_table.contact_last_name LIKE (?)

and it generates this error on the line resultcursor.execute(cmd, cmdvariables)

Exception in Tkinter callback

Error:
Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/tkinter/__init__.py", line 1883, in __call__
    return self.func(*args)
  File "/Users/eugeniemmcguire/PycharmProjects/AnimalTrakkerSystem/AnimalTrakkerUI/BullTestContactScreens.py", line 279, in search_contact
    resultcursor.execute(cmd, cmdvariables)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 11 supplied.

If I do not do any WHERE selections the execution works as expected.

I am pretty sure that the problem is that the cmd, cmdvariables format is incorrect with missing or extra parentheses or commas. I thought that by creating the single item tuple for the variable that was correct per the documentation but I am clearly not understanding it or not building my query correctly.

In the larger app I will have to have several WHERE statements that will be joined using AND operators so I have to buuild the query on the fly.

I have tested the query against my database by entering it in by hand and it works but I can't enter it in exactly in the structure that Python expects so I can't test the generated query outside running the program.

I'm really hoping it's a simple stupid error that is totally obvious to everyone else.

Thanks in advance for you assistance and patience.

update, tried to fix the indentation errors

Was this working and you broke it when features were added, or did it never work?

I see some indentation errors. What should this really look like?

    if search_name_data_entry:  
        cmdwhereclause = 'WHERE contacts_table.contact_last_name LIKE (?)'  
     cmdvariables = '"%'+search_name_data_entry+'%",'  
     print("cmdvariables contains "+cmdvariables)  

Is it supposed to look like this?

    if search_name_data_entry:  
        cmdwhereclause = 'WHERE contacts_table.contact_last_name LIKE (?)'  
        cmdvariables = '"%'+search_name_data_entry+'%",'  
        print("cmdvariables contains "+cmdvariables) 

Should this:

    if search_prefix_data_entry:  
        if search_name_data_entry:  
            cmdwhereclause = cmdwhereclause + 'AND flock_prefix_table.flock_prefix LIKE (?)'  
     cmdvariables = cmdvariables + ' "%' + search_prefix_data_entry + '%",'  
     print("cmdvariables contains "+cmdvariables)  
        else:  
            cmdwhereclause = cmdwhereclause + 'WHERE flock_prefix_table.flock_prefix LIKE (?)'  
     cmdvariables = cmdvariables + '"%' + search_prefix_data_entry + '%",'  
     print("cmdvariables contains "+cmdvariables)  

Look like this?

    if search_prefix_data_entry:  
        if search_name_data_entry:  
            cmdwhereclause = cmdwhereclause + 'AND flock_prefix_table.flock_prefix LIKE (?)'  
            cmdvariables = cmdvariables + ' "%' + search_prefix_data_entry + '%",'  
            print("cmdvariables contains", cmdvariables)  
        else:  
            cmdwhereclause = cmdwhereclause + 'WHERE flock_prefix_table.flock_prefix LIKE (?)'  
            cmdvariables = cmdvariables + '"%' + search_prefix_data_entry + '%",'  
            print("cmdvariables contains", cmdvariables)  

Should this be doubled up?

    if cmdvariables:  
        cmdvariables = " (" + cmdvariables + ")"
        cmd = cmd + cmdwhereclause  
    print("command contains "+cmd) 
    if cmdvariables:  
        cmdvariables = " (" + cmdvariables + ")"
        cmd = cmd + cmdwhereclause  

In the output I see where this happens:

    if search_name_data_entry:  
        cmdwhereclause = 'WHERE contacts_table.contact_last_name LIKE (?)'

But when I look for this output I see nothing:

print("cmdvariables contains "+cmdvariables)

I would get rid of all the prints and just have two right here:

    print(cmd)
    print(cmdvariables)
    if cmdvariables:  
        resultcursor.execute(cmd, cmdvariables)  
    else:  
        resultcursor.execute(cmd)

If you know the cmd and cmdvariables you can probably track backward and see where mistakes are made.

(Mar-10-2022, 10:57 PM)deanhystad Wrote: [ -> ]Was this working and you broke it when features were added, or did it never work?

The case of having cmdvariables has never worked. The case where there are no WHERE causes in the query does work. cmdvariables are to collect he user data and create the Where X like Y with appropriate wildcard items to locate that string anywhere in the field. Iv'e actually got 3 search fields that can all be independently set. If nothing is entered in those fields then the system is supposed to spit out the properly columns based on what the user selected to see for all records in the database in that table. And that is working.

(Mar-10-2022, 10:57 PM)deanhystad Wrote: [ -> ]I see some indentation errors. What should this really look like?

On my end when I did a preview they were all as you have shown corrected. So yes there are indentatin errors.

(Mar-10-2022, 10:57 PM)deanhystad Wrote: [ -> ]If you know the cmd and cmdvariables you can probably track backward and see where mistakes are made.

That's why all the various print statemtns to see it as it's getting built. I have also run it in the debugger with breakpoints at every statement and looked at the variables at each point and I can't see any errors. I can see the command being built out of the pieces as they are selected. The final cmd and cmdvariables before the error messge is what the final set is and that is correct as far as I know. But the error happens at the actual execute line.

The error indicates that the variable used to fill the qmark in the query I've built is being interpreted not as a single item tuple as required by SQLite but as a set of individual text strings to be put into the query replacing the qmark. Since I only have a single placeholder qmark that throws the error when there are too many things to stuff into the single place for them. But I've added the quotes and the extra comma at the end to make it a tuple so I am still missing something.

Now I can could try using {} and text substitution in the query but that leaves it open to SQL injection and is not good practice. I did that in a similar situation elsewherein my code and it works but I need to go change that to not allow that since it's not secure. If i can figure out my problem in this one I thnk I can change the risky code in other places to handle qmark notation properly.

Ok, the arguments should be a list, not a str. Building a list will be easier. Happy days!

This should have been obvious. I don't know how I missed that.

(Mar-11-2022, 03:00 AM)deanhystad Wrote: [ -> ]Ok, the arguments should be a list, not a str

A little correction on this: Sqlite (and all other databases i know) require the arguments to be a tuple.
Still I agree it is better to use a list because one cannot append() values to a tuple. But then in the end you have to make a tuple of it:

resultcursor.execute(cmd, tuple(cmdvariables))

What an odd limitation. Do you know why?

(Mar-11-2022, 02:32 PM)deanhystad Wrote: [ -> ]What an odd limitation. Do you know why?

No I'm sorry. I just follow the directions faithfully. From the sqlite3 manual:

Quote:To insert a variable into a query string, use a placeholder in the string, and substitute the actual values into the query by providing them as a tuple of values to the second argument of the cursor’s execute() method.

But I have to apologize, I just learned the cx_Oracle module uses a list for this parametersubstitution. So I was not right in stating it always has to be a tuple.
I hope @OogieM lets us know if it works with a list or a tuple.

When playing around with sqlite3 in VSCODE, it says Parameters is an iterable for the execute command. I tried a list and it worked fine. I tried making an iterator such as "iterator = iter((value1, value2, value3))". and got an error.

Error:
ValueError: parameters are of unsupported type

I tried a generator and got the same problem. Maybe it needs to be indexable. I made a class that supports indexing:

class Indexable:
    def __init__(self, items):
        self.items = items

    def __len__(self):
        return len(self.items)

    def __getitem__(self, index):
        return self.items[index]

def append(*args):
    con = sql.connect("mydatabase.db")
    cursor = con.cursor()
    cursor.execute("INSERT INTO employees VALUES(?, ?, ?)", Indexable(args))
    con.commit()

This works fine too.

It appears that the parameters for an sqlite3 query must be an indexable container. No need to convert from list to tuple.

I am impressed. Thanks for investigating.
I am also a bit disappointed because the sqlite3 module documentation does not mention this. I always make an issue of it to follow the official documentation as close as possible.

I wanted to thank everyoneon this thread for ideas and help. I've had to do farm tasks yesterday and again today and won't get back to programming until tomorrow but I'll incorporate lessons from here nd post back when I have it all working.