sql server - Avoid an SQL injection attack in my Python SQL API -


i have designed python sqlite api interfaces gui. gui allows user select given column data summed each month. have learned https://docs.python.org/2/library/sqlite3.html know way i’ve written makes code vulnerable sql injection attack; i’ve assembled query using python’s string operations. however, unable make module work doing “right” way; using db-api’s parameter substitution put “?” placeholder wherever want use value. i’m guessing issue want make table column variable , not value. please me restructure module more secure , less vulnerable sql injection attack.

the code below works (it functions to) know not correct/most secure way this.

    def queryentirecategoryallemployees(self, column):          table_column = 'name_data_alldaysums.%s' % column          cursor = self.conn.execute("select \             substr(data_date,1,7), \             sum(%s) \         ( \             select \                 ss_installations.data_date '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 \             \                 ss_installations \             inner join ss_pm_site_visits on \                 ss_installations.employee_clk_no = ss_pm_site_visits.employee_clk_no , \                 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 , \                 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 , \                 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 , \                 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 , \                 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 , \                 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 , \                 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 , \                 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 , \                 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 , \                 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 , \                 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 , \                 ss_in_house_training.data_date = validation_duties.data_date \             \                 (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 substr(data_date,1,7) \         order substr(data_date,1,7) asc" % table_column)          datalist = cursor.fetchall()          return datalist

to start, read on incredibly informative post on preventing sql injection in php, many of principles apply: how can prevent sql injection in php?

additionally, because working sql server, consider creating stored procedure , running exec command in t-sql , passing column name parameter (since query seems dynamically change based on column), similar mssql docs example execute stored procedure , using thread dynamically changing query based on parameter can pass column name input...

doing way obscure code prying eyes , secure injection attacks able validate input matches expect.

finally, consider using drop-down list of columns choose end user can pick pre-defined set of inputs , make application more secure. approach obscuring code in stored procedure make easier push out updates on time.


Comments

Post a Comment

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

i've set bento/ubuntu-16.04 vagrant box using php 5.6, , getting following error quite few times when try , run shell script: "uncaught reflection exception: class domdocument not exist". i've tried adding following top of script (not together), each no success: "sudo apt-get install php5.6-xml", "sudo apt-get install php5-xml", "sudo apt-get install php-dom" i've tried resetting server "sudo service apache2 restart". does have suggestions?
Read more

vue.js - Create hooks for automated testing -

Image
qa guys complained can't automate frontend testing. because our html looks same outside. lazy hack did this <template> <div :role="$options.name"> ... </div> </template> <script> export default { name: 'vmcomponentname' } </script> which takes name script , applies html. in browser get: <div role="vmcomponentname"> ... </div> line :role="$options.name" goes in every component. is there dry er solutions? please share. additional details i'll explain in details looking for. imagine have vmusercreate form creates user. test case can create user . to automatically test without role, have use following selector: .wrapper > .wrapper > .wrapper > .submit-button the test extremely brittle (will break lot). if use roles can use [role="vmusercreateform"] .submit-button selector. many folds less brittle. so basically, i'm ...
Read more

Add new key value to json node in java -

i have json file : { "id":1, "name":"abc", "addressdetails": { "city":"newyork" } } i wanted add 1 more key-value ("pincode" : "414141") node 'addressdetails'. i tried using : objectmapper mapper = new objectmapper(); jsonnode root = mapper.readtree(new file("d://test.json")); objectnode node = mapper.createobjectnode(); node.with("addressdetails").put("pincode", "414141"); but it's not getting added, there way can ? you can try you should modify file path objectmapper mapper = new objectmapper(); objectnode nodes = mapper.readvalue(new file("d:\\test.txt"), objectnode.class); nodes.with("addressdetails").put("pincode", "414141"); mapper.writer().writevalue(new file("d:\\test.txt"), nodes);
Read more