Unit 5 Assignment 1 Directions: Exercise: Database Design for Secure Software Applications
Overview
In this exercise, you will:
- demonstrate the significance of Structured Query Language (SQL) injection attacks;
- explain how to identify database code that is susceptible to SQL injection (discussed in Chapters 4 and 7); and
- recommend ways to fix the code.
SQL allows access to a database to find or edit specific information. An SQL injection occurs when an outsider or insider threat inserts malicious code into statements that can be sought in a database. This allows the threat access to information that should not be displayed, to tamper with data to which the threat should not have access, to interfere with queries, and more.
For this exercise, you will consider applications of data sensitivity and privacy violations related to SQL, and you will make recommendations for improvement. To complete this exercise, you will need to research sources to assist your understanding. Watch What are SQL Injections? to help you get started.
Existing Application Description
An existing PHP-based application connects to a database that provides functionality for retrieving table information for a specific employee. For example, a new university employee just moved and needs to change her address. When employees want to update their information, the application needs their employee identification number to do so. The application also can update specific user fields for an employee. In this situation, a web-based form is used allowing a user to search for an employee and then either display the employee’s information or update it.
The web-form display functionality asks the user to enter a specific employee ID and then retrieves the following fields:
- Employee_id,
- firstname,
- lastname,
- salary,
- birthdate,
- SSN,
- phonenumber,
- address,
- email,
- nickname,
- Password
The update form allows the user to enter a specific employee ID and then update most of the fields in the list above.
Exercise Requirement Part 1
Answer all parts of each question.
1. (30 points) As you review the web-form display functionality information, what concerns do you have with the data and display of the data? Remember that only an employee number is needed to change the listed information.
a. What are the potential privacy concerns?
b. What are current best practices for protecting private data such as employee information?
c. What are best practices for changing a password? (Note: you can ignore the SQL Injection issues in this discussion as you will be addressing that later in the assignment.)
2. (30 points) Now that your concerns about the application have been documented, what specific recommendations do you have that will address your concerns from question #1? Be specific with your recommendations. You should consider such factors as using roles to restrict access, limiting access to the form, encrypting fields, assessing data integrity, applying security controls, assessing the sensitivity of data, among others.
a. Discuss if the recommended changes will impact the functionality of the application and, if so, why that is acceptable.
3. (30 Points in two separate 15-point questions)
- A. (15 points) Define the mechanism by which parameterized queries prevent SQL Injection attacks. Your answer should include:
- A definition of parameterized queries
- Why is this method effective against injection attacks
- B. (15 points) Explain the difference between prepared statements and stored procedures in preventing SQL Injection
- Definitions of both concepts
- One example of each in practice
4. (30 points) Use the textbook and online resources to explain three types of SQL injection vulnerabilities. Include what the hacker inputs into the field, what happens with the input, and what can result.
5. (30 points) In general, how can a company determine the vulnerabilities that exist with SQL injection?
a. Describe what is needed to fix those vulnerabilities. You do not need to write code, but you do need to provide specific mitigation strategies. Explain why your strategies should work.
Requirements:
- Deliverables: One document (in Word or PDF) should be submitted. Include your name, date, course information, and professor name on a title page.
- Your responses should address all parts of each question. Your submission should be well-organized and well written using full sentences and paragraphs. Follow APA 7 writing style, when appropriate.
Submission:
Review the Grading Rubric to understand how you will be assessed on this assignment.