CSC/ECE 517 Spring 2022 - E2227: SQL Injection Fix

From Expertiza_Wiki
Revision as of 19:21, 27 March 2022 by Lpobrien (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

This page details project documentation for the CSC/ECE 517 Spring 2022, "E2227 SQL Injection Fix" project.


Background

Expertiza is an open source software system that is used in order to assign projects to students as well as provide a place to submit and review peers.

Many software projects such as expertiza rely on user interactions in order to accomplish the tasks given by their users. With this information input by the users, the software system often must access points in the database. Because of this, it is possible to access parts of the database even if you are not given direct access by inputting SQL language queries to unvalidated inputs. The reason in which this is possible is because many SQL queries rely on Boolean logic. This is a serious security risk for the software system and causes privacy concerns for any and all involved users. This SQL injection risk is present in the controller class sign_up_sheet_controller.rb, which is a class that is responsible for controlling actions surrounding signing up for specific topic for a project.

Mentor

Nicholas Himes

Team Members

  • Lawrence O'Brien
  • David Glymph
  • Jacob Anderson

Code Modifications

Described below are the code refactoring tasks and issues addressed by this team. We have also included any pertinent details, reasonings, comments, warnings, etc., corresponding to each task.

Dynamic Finders

The SQL injection issue present in the expertiza source code is caused by an unvalidated SQL query when when searching for sign up topics for assignments requiring topics. The system used the static "find" method which does not validate or in any protect against malicious input. In order to solve this issue we used a find_by method, which forces all input to be used in searching for one specific key in the database rather than allowing additional keys to be accessed using string terminating characters. This solves the issue by preventing unauthorized database access through SQL injection.

Modified Files

  • sign_up_sheet_controller.rb
  • sign_up_sheet_controller_spec.rb

Test Plan

We plan to rely on the existing GitHub bots to test for functionality and style, but will add tests that correlate to inputting the SQL injections into the system and ensure they are dealth with.

Github Bots

Github bots were used to facilitate the majority of the checks that were used for expertiza outside of our unit testing, as well as the style guidelines. Brakeman was used as the functionality checking bot for this repository.

Running Tests

Tests were altered in order to accomodate the change in implementation. We tested for a command that previously would have invoked a data breach via SQL injection to ensure it was no longer present. Tests are needed in order to verify that the SQL injection risk has been effectively dealt with. 

  rspec spec/controllers/sign_up_sheet_controller_spec.rb

GitHub links and Pull Request

Link to Expertiza repository: here

Link to the forked repository: here

Link to Pull Request: here

Images

Retrieved from "https://wiki.expertiza.ncsu.edu/index.php?title=CSC/ECE_517_Spring_2022_-_E2227:_SQL_Injection_Fix&oldid=143597"