sql注入python编程_避免pythonsqlapi中的SQL注入攻击

我设计了一个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