Create a dynamic sql query by removing a value from string type

Question

I've got a python GUI framework that allows for the use of dropdown lists and returns the key/value list from the dropdown and any form submission boxes in the window. The problem that I am trying to solve is that I need to pass one of these values removed from the string type, specifically the 'FirstName' value from the dropdown list as it passes to the MySQL statement as 'FirstName' rather than FirstName.

I attempted to solve this on the MySQL side by use of a PREPARE statement but it didn't seem like that was a solution.

SQL_queries file

upi_update = '''UPDATE supplements_test.people
                SET %(selection)s = %(first)s
                WHERE IndividualID = CAST(%(id)s AS UNSIGNED)'''

usi_update = '''UPDATE supplements_test.supplements
                SET %(selection)s = %(SuppName)s
                WHERE SuppID = CAST(%(id)s AS UNSIGNED)'''

Main program file

import mysql.connector as mysql
# local file holding several dynamic query sets with validations/updates/inserts
# small sample above
import SQL_queries as sq


# MySQL connection code
cnx = mysql.connect(connection_info_here)
cur = cnx.cursor()

# key generated based on what functionality the user chooses from menu.
q_key = 'upi'

# Key value pairs returned from the GUI drop down selection/form input fields. 
keys = {'first': 'John',
        'last': 'Smith',
        'email': 'j.smith@gmail.com',
        'id': '1',
        'selection': 'FirstName'}



cur.execute(eval(f'sq.{q_key}_update'), params=keys)
conn.commit()

UPDATE

After realizing I poorly described the complexities of my question, by giving a code example that attempted to simplify my code, I have edited the original with more information and found a solution which is posted as a self answer.

Answer 1

SQL query parameters don't work for identifiers such as column names. Every query parameter behaves as a string value in the query, so in your example, it executes as if you had written this query:

UPDATE supplements_test.people
SET 'FirstName' = 'John'
WHERE IndividualID = CAST('1' AS UNSIGNED)

This is not a legal UPDATE statement, because it's you can't use a literal values as the left side of an assignment (this is similar in most programming languages, i.e. it makes no sense to assign 4 = 12).

So you can't use query parameters for identifiers. All query syntax and identifiers must be fixed in the query string before you pass it to MySQL to be parsed. Only the elements of the query you would otherwise make constant values may be parameters.

Also I'm pretty sure passing the params to cur.execute() requires that you don't have extra keys in the dict besides those that will be used for the parameters.

---

Re your comment: Here's an example of interpolating a Python variable into a string using Python's f-strings.

# Key value pairs returned from the GUI. 
keys = {'first': 'John',
        'last': 'Smith',
        'email': 'j.smith@gmail.com',
        'id': '1',
        'selection': 'FirstName'}

stmt = f'''UPDATE supplements_test.people
          SET `{keys['selection']}` = %(first)s
          WHERE IndividualID = CAST(%(id)s AS UNSIGNED)'''

I put the expression inside back-ticks in the query because in MySQL, back-ticks protect the identifier in case it is a reserved SQL keyword or contains a space or some special character. The f-string expression itself is just {keys['selection']}.

The f-string has interpolated the variable into the query itself using string substitution.

F-strings don't care about the %s placeholders. These pass through unchanged. I can show the resulting value in the python interactive shell:

>>> stmt
'UPDATE supplements_test.people\n              SET `FirstName` = %(first)s\n              WHERE IndividualID = CAST(%(id)s AS UNSIGNED)'

Now one can use this string as a query, and pass a dict with just the parameters needed:

p = {'first': keys['first'], 'id': keys['id']}
cur.execute(stmt, p)

Answer 2

NOTE: This isn't a pretty solution and it is an injection attack risk, but it does work. Will need to find another way of protecting against malicious input.

Formed this answer as a derivation from How to postpone/defer the evaluation of f-strings?

SQL_queries file

upi_update = '''UPDATE supplements_test.people
                SET {keys["selection"]} = %(first)s
                WHERE IndividualID = CAST(%(id)s AS UNSIGNED)'''

usi_update = '''UPDATE supplements_test.supplements
                SET %(selection)s = %(SuppName)s
                WHERE SuppID = CAST(%(id)s AS UNSIGNED)'''

Removed the eval statement for the dynamic sql read from the execute method into a variable to get it into the same file as the keys variable. Eval on an fstring nested inside an fstring to remove FirstName from rendering as string and pass other retaining the string.

Main program file

import mysql.connector as mysql
# local file holding several dynamic query sets with validations/updates/inserts
# small sample above
import SQL_queries as sq


# MySQL connection code
cnx = mysql.connect(connection_info_here)
cur = cnx.cursor()

# key generated based on what functionality the user chooses from menu.
q_key = 'upi'

# Key value pairs returned from the GUI drop down selection/form input fields. 
keys = {'first': 'John',
        'last': 'Smith',
        'email': 'j.smith@gmail.com',
        'id': '1',
        'selection': 'FirstName'}



query = eval(f'sq.{q_key}_update')
cur.execute(eval(f"f'{query}'"), params=keys)
conn.commit()