Avoid an SQL injection attack in my Python SQL API

Question

I have designed a Python SQLite API which interfaces with a GUI. The GUI allows the user to select a given column whose data will be summed for each month. From what I have learned from https://docs.python.org/2/library/sqlite3.html I know that the way I’ve written this makes my code vulnerable to an SQL injection attack; I’ve assembled my query using Python’s string operations. However, I am unable to make this module work doing it the “right” way; using the DB-API’s parameter substitution to put a “?” as a placeholder wherever you want to use a value. I’m guessing the issue is that I want to make a table column the variable and not a value. Please help me to restructure this module so that it is more secure and less vulnerable to an SQL injection attack.

The code below works (it functions as I would like it to) I just know that it is not the correct/most secure way to do this.

    def queryEntireCategoryAllEmployees(self, column):

        table_column = 'Name_Data_AllDaySums.%s' % column

        cursor = self.conn.execute("SELECT \
            SUBSTR(data_date,1,7), \
            SUM(%s) \
        FROM ( \
            SELECT \
                SS_Installations.data_date AS 'data_date', \
                SS_Installations.Installations_day_sum, \
                SS_PM_Site_Visits.PM_Site_Visits_day_sum, \
                SS_Rpr_Maint_Site_Visits.Inst_Repair_or_Maintenance_on_Site_day_sum, \
                SS_Rmt_Hrdwr_Spt.Rmt_Hardware_Support_day_sum, \
                SS_Rmt_Sftwr_Spt.Rmt_Software_Support_day_sum, \
                SS_Rpr_Mant_RFB_in_House.Inst_Repair_Maint_Rfb_In_House_day_sum, \
                Miscellaneous.Miscellaneous_day_sum, \
                SS_Doc_Gen.Document_Generation_day_sum, \
                SS_Inter_Dep_Spt.Inter_Dep_Spt_day_sum, \
                SS_Online_Training.Online_Training_day_sum, \
                SS_Onsite_Training.Onsite_Training_day_sum, \
                SS_In_House_Training.In_House_Training_day_sum, \
                Validation_Duties.Validation_Duties_day_sum \
            FROM \
                SS_Installations \
            INNER JOIN SS_PM_Site_Visits ON \
                SS_Installations.employee_clk_no = SS_PM_Site_Visits.employee_clk_no AND \
                SS_Installations.data_date = SS_PM_Site_Visits.data_date \
            INNER JOIN SS_Rpr_Maint_Site_Visits ON \
                SS_Installations.employee_clk_no = SS_Rpr_Maint_Site_Visits.employee_clk_no AND \
                SS_PM_Site_Visits.data_date = SS_Rpr_Maint_Site_Visits.data_date \
            INNER JOIN SS_Rmt_Hrdwr_Spt ON \
                SS_Installations.employee_clk_no = SS_Rmt_Hrdwr_Spt.employee_clk_no AND \
                SS_Rpr_Maint_Site_Visits.data_date = SS_Rmt_Hrdwr_Spt.data_date \
            INNER JOIN SS_Rmt_Sftwr_Spt ON \
                SS_Installations.employee_clk_no = SS_Rmt_Sftwr_Spt.employee_clk_no AND \
                SS_Rmt_Hrdwr_Spt.data_date = SS_Rmt_Sftwr_Spt.data_date \
            INNER JOIN SS_Rpr_Mant_RFB_in_House ON \
                SS_Installations.employee_clk_no = SS_Rpr_Mant_RFB_in_House.employee_clk_no AND \
                SS_Rmt_Sftwr_Spt.data_date = SS_Rpr_Mant_RFB_in_House.data_date \
            INNER JOIN Miscellaneous ON \
                SS_Installations.employee_clk_no = Miscellaneous.employee_clk_no AND \
                SS_Rpr_Mant_RFB_in_House.data_date = Miscellaneous.data_date \
            INNER JOIN SS_Doc_Gen ON \
                SS_Installations.employee_clk_no = SS_Doc_Gen.employee_clk_no AND \
                Miscellaneous.data_date = SS_Doc_Gen.data_date \
            INNER JOIN SS_Inter_Dep_Spt ON \
                SS_Installations.employee_clk_no = SS_Inter_Dep_Spt.employee_clk_no AND \
                SS_Doc_Gen.data_date = SS_Inter_Dep_Spt.data_date \
            INNER JOIN SS_Online_Training ON \
                SS_Installations.employee_clk_no = SS_Online_Training.employee_clk_no AND \
                SS_Inter_Dep_Spt.data_date = SS_Online_Training.data_date \
            INNER JOIN SS_Onsite_Training ON \
                SS_Installations.employee_clk_no = SS_Onsite_Training.employee_clk_no AND \
                SS_Online_Training.data_date = SS_Onsite_Training.data_date \
            INNER JOIN SS_In_House_Training ON \
                SS_Installations.employee_clk_no = SS_In_House_Training.employee_clk_no AND \
                SS_Onsite_Training.data_date = SS_In_House_Training.data_date \
            INNER JOIN Validation_Duties ON \
                SS_Installations.employee_clk_no = Validation_Duties.employee_clk_no AND \
                SS_In_House_Training.data_date = Validation_Duties.data_date \
            WHERE \
                (SS_Installations.Installations_day_sum != 0 OR \
                SS_PM_Site_Visits.PM_Site_Visits_day_sum !=0 OR \
                SS_Rpr_Maint_Site_Visits.Inst_Repair_or_Maintenance_on_Site_day_sum != 0 OR \
                SS_Rmt_Hrdwr_Spt.Rmt_Hardware_Support_day_sum != 0 OR \
                SS_Rmt_Sftwr_Spt.Rmt_Software_Support_day_sum != 0 OR \
                SS_Rpr_Mant_RFB_in_House.Inst_Repair_Maint_Rfb_In_House_day_sum != 0 OR \
                Miscellaneous.Miscellaneous_day_sum != 0 OR \
                SS_Doc_Gen.Document_Generation_day_sum != 0 OR \
                SS_Inter_Dep_Spt.Inter_Dep_Spt_day_sum != 0 OR \
                SS_Online_Training.Online_Training_day_sum != 0 OR \
                SS_Onsite_Training.Onsite_Training_day_sum != 0 OR \
                SS_In_House_Training.In_House_Training_day_sum != 0 OR \
                Validation_Duties.Validation_Duties_day_sum != 0)) Name_Data_AllDaySums \
        GROUP BY SUBSTR(data_date,1,7) \
        ORDER BY SUBSTR(data_date,1,7) ASC" % table_column)

        dataList = cursor.fetchall()

        return dataList

I guess one way would be to compare the selected column against a list of legitimate inputs. As you have found, you cannot parameterize the column name with `?`. — roganjosh, Jul 28 '17 at 12:49

score 0 · Accepted Answer · answered Jul 28 '17 at 14:33

To start, I would read up on this incredibly informative SO post on preventing SQL injection in PHP, as many of the principles apply: How can I prevent SQL Injection in PHP?

Additionally, because you are working with SQL Server, I would consider creating a stored procedure and running it with the EXEC command in T-SQL and passing your column name as a parameter (since your query seems to only dynamically change based on the column), similar to this MSSQL Docs example Execute a Stored Procedure and using this SO thread for dynamically changing a query based on a parameter Can I Pass Column Name As Input...

Doing so this way will help you to obscure your code from prying eyes and also secure it from injection attacks as you will be able to validate that the input matches what you expect.

Finally, consider using a drop-down list of columns to choose from so that the end user can only pick a pre-defined set of inputs and thus make your application even more secure. This approach as well as obscuring the code in a stored procedure will help also make it much easier to push out updates over time.

Avoid an SQL injection attack in my Python SQL API

1 Answers1