Dynamic SQL Queries with Python and mySQL

I have multiple tables that are updated after a value is changed in a grid. These tables don’t always have the same keys or columns so I cannot explicitly name the columns or formats. The only thing that is ever the same, is the column where the keys reside. I know the way I am currently doing this is not correct and leaves me open to injection attacks.

I also ran into an issue where some of the values contain keys that throw an error in the SQL statement. For example, updating WHERE email = t'[email protected].

I am not really sure of the proper way to write these statements. I did some research and see multiple methods for different purposes but am not sure which is proper. I am looking to do this as dynamically as possible. Can anyone point me in the right direction?

To connect:

import mysql.connector as sql
import MySQLdb

#Connect
self.db_name = 'database'
        self.server = 'server'
        self.user_id = 'user'
        self.pw = 'password'

        try:
            self.db_con = MySQLdb.connect(user=self.user_id,password=self.pw,database=self.db_name,
                                    host=self.server,charset='utf8',autocommit=True)
            self.cursor = self.db_con.cursor()
        except:
            print("Error connecting")

SQL Statements:

key_id =  str("'") + self.GetCellValue(event.GetRow(),1) +  str("'")    
        target_col = self.GetColLabelValue(event.GetCol())
        key_col = self.GetColLabelValue(1)                                     
        nVal = str("'") + self.GetCellValue(event.GetRow(),event.GetCol()) +  str("'")

#SQL STATEMENTS
        sql_update = "UPDATE " + tbl + " SET " +  target_col + " = " + nVal + " WHERE " + key_col  +  " = " + key_id + ""
        

#INSERT
        sql_update = ("INSERT INTO " + str(self.tbl) + "(" + self.key_col + ")" + "VALUES (" + str("'") + str(val) + str("'") + ")")

#DELETE
        sql_update = "DELETE FROM " + tbl + " WHERE " + self.key_col + " = " + self.key_id + ""

#SELECT
        sql_query = "SELECT * FROM " + self.tbl 

#Excecute
        try:
            self.cursor.execute(sql_update)
            
            
        except:
            print('Error')
            self.db_con.rollback()

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Databases have different notations for “quoting” identifiers (table and column names etc) and values (data).

MySQL uses backticks to quote identifiers. For values, it’s best to use the parameter substitution mechanism provided by the connector package: it’s more likely to handle tricky cases like embedded quotes correctly, and will reduce the risk of SQL injection.

Here’s an example for inserts; the same techniques can be used for the other types of query.

key_id =  str("'") + self.GetCellValue(event.GetRow(),1) +  str("'")    
target_col = self.GetColLabelValue(event.GetCol())
key_col = self.GetColLabelValue(1)                                     
nVal = str("'") + self.GetCellValue(event.GetRow(),event.GetCol()) +  str("'")
        

#INSERT (using f-strings for brevity)
sql_update = (f"INSERT INTO `{self.tbl}` (`{self.key_col}`) VALUES (%s)")

# Pass the statement and values to cursor.execute.  
# The values are assumed to be a sequence, so a single value should be 
# placed in a tuple or list.
self.cursor.execute(sql_update, (nVal,))

If you have more than one column / value pair you could do something like this:

cols = ['A', 'B', 'C']
vals = ['a', 'b', 'c']

col_names = ','.join([f'`{c}`' for c in cols])
values_placeholder = ','.join(['%s'] * len(cols))

sql_update = (f"INSERT INTO `{self.tbl}` (col_names) VALUES ({values_placeholder})")
self.cursor.execute(sql_update, vals)

Values are not only data for insertion, but also data that we are using for comparison, for example in WHERE clauses. So an update statement with a filter might be created like this:

sql_update = (f"UPDATE `{tbl}` SET (`{target_col}`) = (%s) WHERE (`{key_col}`) = %s")
self.cursor.execute(sql_update, (nVal, key_id))

However sometimes the target of a SET or WHERE clause may be a column, for example we want to do an update based on other values in the row. For example, this statement will set target_col to the value of other_col for all rows where key_col is equal to other_key_col:

sql_update = (f"UPDATE `{tbl}` SET (`{target_col}`) = `{other_col}` WHERE (`{key_col}`) = `{other_key_col}`")
self.cursor.execute(sql_update)


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x