避免pythonsqlapi中的SQL注入攻击

Question

我设计了一个pythonsqliteapi，它与GUI接口。GUI允许用户选择一个给定的列，每个月的数据将被汇总。从https://docs.python.org/2/library/sqlite3.html学到的，我知道我写这篇文章的方式使我的代码容易受到SQL注入攻击；我使用Python的字符串操作组装了我的查询。但是，我无法使这个模块以“正确”的方式工作；使用DB-API的参数替换来放置一个“？“作为占位符，您可以在任何地方使用值。我猜问题是我想让表列成为变量而不是值。请帮助我重新构造此模块，使其更安全，更不易受到SQL注入攻击的攻击。在

下面的代码可以工作（它的功能与我希望的一样），我只知道这不是正确/最安全的方法。在

    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

Answer 1

首先，我将阅读这篇关于在PHP中防止SQL注入的非常有信息量的帖子，因为很多原则都适用：How can I prevent SQL Injection in PHP?

另外，由于您使用的是SQL Server，所以我会考虑创建一个存储过程，并在T-SQL中使用EXEC命令运行它，并将列名作为参数传递（因为您的查询似乎只会根据列动态更改），与此MSSQL Docs示例Execute a Stored Procedure类似，并使用此SO线程根据参数Can I Pass Column Name As Input...动态更改查询

这样做将有助于您使代码不受窥探者的注意，并使其免受注入攻击，因为您将能够验证输入是否与预期相符。在

最后，考虑使用一个列的下拉列表进行选择，以便最终用户只能选择一组预定义的输入，从而使应用程序更加安全。这种方法以及隐藏存储过程中的代码也将有助于随着时间推移推出更新。在