Dynamic mySQL Statement

Question

I am trying to make a dynamic mySQL update statement. My update fails if certain characters are in the string.

import mysql.connector as sql
import MySQLdb

#Values are taken from a wxGrid.
key_id =  str("'") + str(self.GetCellValue(event.GetRow(),1)) + str("'") #Cell column with unique ID
target_col = str(self.GetColLabelValue(event.GetCol())) #Column being updated
key_col = str(self.GetColLabelValue(1)) #Unique ID column                               
nVal =  str("'")+self.GetCellValue(event.GetRow(),event.GetCol()) + str("'") #Updated value

sql_update = f"""Update {tbl} set {target_col} = {nVal} where {key_col} = {key_id}"""

self.cursor.execute(sql_update)

My Key column always contains Email addresses or integers. So if key_id = test@email.com, the update is successful, but if key_id = t'est@email.com, it fails. How do I get around this?

Building an entire SQL string with literals coming from user input poses very high risk of [data loss](https://bobby-tables.com/). You need to use prepared statement and bind variables, that are not just text substitution, but the data. — astentx, Jul 12 '21 at 23:24
Agreed. Maybe you can evaluate the string prior to submitting the value and do clean up or exception handling.. — alexherm, Jul 12 '21 at 23:27

score 2 · Accepted Answer · answered Jul 12 '21 at 23:27

You can fix this by using query parameters. Stop concatenating strings into your SQL query. Use placeholders and then pass the values in a separate list argument to execute().

sql_update = f"""Update {tbl} set {target_col} = %s where {key_col} = %s"""

self.cursor.execute(sql_update, (nVal, key_id,))

Query parameters only work where you would use a literal value in your query, like a quoted string literal or a numeric literal.

You can't use query parameters for identifiers like the table name or column names. But I hope your identifiers are less likely to contain ' characters!

Likewise you cannot use query parameters for expressions or SQL keywords or lists of values e.g. for an IN() predicate. One query parameter = one scalar value.

Dynamic mySQL Statement

2 Answers2