CODEWITHSUNDEEP
pythonmysqlsqlmysql-python
cmccall95
cmccall95

Reputation: 149

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'est@email.com.

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()

Upvotes: 0

Views: 5430

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55933

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)

Upvotes: 2

Related Questions