CS6035 PROJECT: DATABASE SECURITY FALL2025 Solved

[SOLVED] CS6035 PROJECT: DATABASE SECURITY FALL2025

130.00 $

Category:
Click Category Button to View Your Next Assignment | Homework

You will receive the following solution file(s) instantly after successful payment:

zip file icon SCRIPTSSTEPS_CS6035-DBSEC-a32wmy.zip (3.4 KB)
Assignment Instructions Updated Recently? Submit Below and we will provide new Solution!
Submit New Instructions
🔒 Securely Powered by:
Secure Checkout
5/5 - (2 votes)

Welcome to the Database Security project. This hands-on project is designed to help you understand how real-world data breaches occur and the strategies you can use to protect against them.

Learning Goals

In this project, you’ll explore two significant types of database vulnerabilities and how attackers exploit them:

  • Inference Attacks: Learn how sensitive information can be uncovered by analyzing and correlating seemingly harmless data. Data mining often uses this technique to “connect the dots” and extract private details without direct access.
  • SQL Injection (SQLi): Understand how attackers inject malicious SQL code through input fields to manipulate databases, access unauthorized data, or execute harmful actions.

Topics Covered

  • Basic T-SQL queries
  • Basic JavaScript
  • Database inference techniques
  • SQL injection attacks

Final deliverables

You will submit a single file named project_dbsec.json to Gradescope. A template is available at: /home/dbsec/Desktop/project_dbsec.json

See the Submission Details page for formatting and content guidelines.

Important Reference Material

Valuable Information – Database Security Prerequisites

NOTE: Vital information is on the prerequisite page and not elsewhere!

Submission

Virtual Machine

Supported

Not Supported

  • Apple M-series (ARM-based) devices are not supported.
  • Instructions are provided on how to attempt to get the VM to run on a MAC, but again, this is not supported. *Note: You will need to SSH into the VM if you choose to attempt this.
  • Extensions will not be granted for related issues.

This project uses Microsoft SQL Server Developer Edition, which runs on Linux inside the provided VM. The DBSec project is on the master VM, and the credentials for the Linux user will be provided in the project release on Canvas.

Table of contents

 

 

From Canvas – Database Security

The Database Security Project Release ED Discussion has all the information to get started!

You will use the same VM you used in the earlier projects. If you need to download it again:

For the DB Security project, this is the username and password:

  • Username: dbsec
  • Password: EiffelTower_1887

There is a limit of sixteen (16) submissions for the Database Security Project!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

FREQUENTLY ASKED QUESTIONS (FAQ)

  1. Q) Chrome is crashing
  • Do this to fix it:
    1. Disable/turn off the screen saver or lock.
    2. Ensure the VM Application is started; if it is not, follow the steps below:
      • Open a Terminal window and run the command: ./StartContainer.sh.
      • Close the Terminal after seeing the output: “Container Started, current datetime.”
    3. Wait a few seconds after opening Chrome to allow outside network resources to load correctly.
  1. Q) I accidentally deleted or modified files and can’t get them back
  • Do this to fix it:
    • Restore the VM from the OVA file.
  1. Q) How do I find my GTID?
  • Do this to find it:
  1. Q) I’m confused about how to start
  • Do this to start:
    1. Start the Container:
      • Open a Terminal window and run: ./StartContainer.sh.
      • Close the Terminal after seeing the output: “Container Started, current datetime.”
    2. Double-click the Chrome Web Browser icon on the Desktop to open Chrome.
  1. Q) Menu links do not load / Website(s) are not loading (We’re having trouble finding that site)
  • Do this to fix it:
    1. Wait a few seconds for the VM to catch up (don’t rush after starting the container).
    2. Stop the Container:
      • Open a Terminal and run: ./StopContainer.sh.
      • Close the Terminal after the message: “Container Stopped, current datetime.”
    3. Start the Container:
      • Open a Terminal and run: ./StartContainer.sh.
      • Close the Terminal after the message: “Container Started, current datetime.”
    4. Open Chrome and access the site: http://www.gt-cs6035.com.
    5. If issues persist, try restarting your VM and repeat Steps 2-4.
  1. Q) Task A submission fails
  • Do this to fix it:
    1. Ensure your GTID is correct (no extra spaces at the beginning or end).
    2. Validate your JSON file.
    3. Re-complete Task A.
  1. Q) Task A states, “You have reached the maximum number of entries; the VM will need to be restored.”
  • Do this to fix it:
    • Restore the VM from the OVA file.
  1. Q) Do I need to redo Task A every time I return to the VM to complete another task?
  • Answer:
    • No, Task A only needs to be completed once unless:
      • You didn’t enter the correct GTID, and your Gradescope submission for flag0 failed.
      • The VM was in an unstable state and not saved correctly.
  1. Q) Task B-I states, “Task A needs to be completed before this Task is unlocked.”
  • Do this to fix it:
    1. Complete Task A.
    2. If the system was reset, compare the hash you received from your previous successful Gradescope submission with the hash from re-completing Task A.
  1. Q) I’m confused about how to solve Task(s) A-I
  • Do this to solve it:
    1. Read the write-up and pay attention to each task’s section.
      • The Write-up is available on GitHub.
      • Note: You must log in to GitHub with your GaTech credentials.
    2. Check for similar questions asked and answered in the corresponding DBSec Task on ED.
    3. If not answered, post your question in the corresponding DBSec Task on ED.
      • NOTE: Posts outside the preconfigured DBSec Task posts, including private posts, will be redirected.
    4. Bonus hints for you because you are reading the FAQs:
      • Report Columns for Tasks B, C, and F are sortable.
      • Use DbGate for Task D
      • Use JavaScript debugging using Web Developer Tools for Tasks E, G, and H.
      • The lessons from Task B help with Tasks C, D, and F; Task E helps with Tasks F, G , and H; Task B-H will help with Task I.
      • What does “remembering that they often use the same logic on the client and server side” mean?
        • Once you access the client-side code via the browser’s Developer Tools and find out how to bypass its security implementation, you will need to do this again to bypass the database’s security implementation. The database security logic is identical to the client-side security logic.
  1. Q) Can I use Firefox instead of Chrome?
  • Answer:
    • Firefox is pre-installed, and the project works with Chrome and Firefox.
  1. Q) Should my hashes be of different lengths?
  • Answer:
    • Flag0 will have the longest hash.
    • Flags 7 and 12 will have the shortest hashes (same length).
    • Flags 4-6 and 18-11 will have a medium hash length (same length)).
    • The hash portion of flags 1 and 2 will be the same length as 7 and 12, but will have appended data after the hash value and will also be an array of flags.
    • Flags 3 will not contain a hash.
    • Flags 1, 2, 7, and 12 are arrays.
    • See Submission Details for more information.
  1. Q) Gradescope will not allow any more submissions
  1. There is a limit of 16 submissions for this project.
  2. If you have submitted your JSON file 16 times, you may no longer submit for this project.
  3. If you believe there is an error, contact a TA via private ED discussion post, but note that any decision will be based on the data provided by Gradescope audit logs.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

BACKGROUND AND SETUP:

Project Scenario: Your First Penetration Test

“Good News, Everyone!”

You’ve just stepped into your new role at a penetration testing firm, and your first mission awaits. A portfolio of client websites has been placed before you, each resembling a digital fortress with hidden doors and potential cracks. It’s your responsibility to probe their defenses and uncover vulnerabilities before anyone else does.

As you begin your review, familiar patterns start to emerge. The layouts, naming conventions, and user interfaces share striking similarities. A deeper look reveals the reason — the same software vendor developed all of these websites. This discovery is critical: any weakness found in one site could very likely exist across the entire portfolio, putting multiple clients at risk.

Your objective in this mission is to identify and report those vulnerabilities. You’ll search for sensitive information that was never meant to be exposed, analyze how data is processed and protected, and test the applications against user input designed to bypass their safeguards. In doing so, you’ll step fully into the role of a penetration tester — wearing many hats and sharpening the skills essential to protecting databases and application security.

Setup Instructions

This project focuses on two common yet critical categories of database attacks: inference attacks and SQL injection. You will take on the role of a security analyst, tasked with identifying, exploiting, and reporting these vulnerabilities, all within a controlled environment designed for learning and exploration.

To get started, follow the setup steps carefully. Precision matters here — even a slight misstep could cause confusion or errors later in the process.

  • Log in to the Virtual Machine
    • You will use a pre-configured virtual machine (VM) provided with the project. Log in using the details shared in Canvas’s official Database Security Project release announcements.

Username: Provided on project release in Database Security Project Canvas post

Password: Provided on project release in Database Security Project Canvas post

  • Locate the Submission JSON File
    • On the desktop of your VM, you will find a file named project_dbsec.json. This file will serve as your central submission file.
    • If the file is missing, recreate it using the template and formatting instructions provided in the project’s Submission Details section.
    • Edit the file only with a plain text editor, such as Text Editor on the VM (in plain text mode) or Vim. Do not use word processors such as LibreOffice ** or ** Microsoft Word, as they may introduce special characters that will cause the autograder to fail.
    • project_dbsec.json file is where you will enter your final hashes and flag values as you complete each task. You will then submit this file to Gradescope for grading.
  • Access the project:
    • After logging into the virtual machine, you may see a pop-up message prompting you to upgrade the system.
      • This is not required for the project. If it appears, minimize the window. While you can click Cancel, this notice will continue to pop up sporadically, so minimizing it is recommended.
    • To initialize the project environment, Start the container:
      • Open the terminal and execute the command: ./StartContainer.sh
      • Close the Terminal after the message: “Container started, current datetime.”
    • Next, open a web browser inside the virtual machine.
      • Use either Google Chrome or Firefox.
      • In the browser’s address bar, enter the URL: http://www.gt-cs6035.com/intro.html (You may want to bookmark this for future ease of access)
      • This will bring you to the main interface, where you can begin working on the tasks.
  • Starting the Tasks
    • You must begin with Task A. This task validates your GTID and generates a unique identifier required to complete the project. None of the other functions will work correctly until Task A has been completed and the corresponding hash is submitted. IT IS HIGHLY RECOMMENDED TO USE A SUBMISSION TO VALIDATE FLAG 0!
    • After completing Task A, the remaining tasks (Tasks B through I) can be completed in any order. However, for clarity and ease of progress, it is recommended that you follow them in alphabetical order (with flags completed in numerical order).
    • Each task is designed to teach you a new concept or technique in database security. As you progress, you’ll begin to think like a penetration tester, evaluating the strengths and weaknesses of database protections.

DbGate

This is required for Task D and I (extra credit). Any testing can be done online with SQLFiddle; however, if you want a more realistic setup for testing, 3 blank databases are provided and can be accessed via DbGate.

Setup

DbGate is a cross-platform database tool designed for ease of use and the simultaneous management of multiple databases. It offers powerful features like schema comparison, a visual query builder, data charts, and tools for importing and exporting large amounts of data.

Location in the VM

  • Click on the Linux Mint Launcher Icon
  • Click on the Development Menu
  • Click on DbGate

DbGate Connection Procedure

  • Under File, choose Add Connection
  • In Connection type, choose Microsoft SQL Server
  • Enter this information in the General Tab
    • Server -> www.gt-cs6035.com
    • User -> CS6035Student
    • Password -> CS6035DBSec
  • Check Trust server certificate
  • Click Test
    • If you see Connected next to save then Click Save
  • Click Connect

 

 

GATECH ID – GTID

  • NOTE: This is not your Georgia Tech Username; it is your 9-digit Student ID, which you can find on your Buzzcard or by following the steps below:
  • Please see the Finding Your GTID section in Submissions & Gradescope page for instructions on retrieving your GTID.
  • Be very careful! When you copy and paste, strip off all leading spaces or special characters.

Project Time Expectations

Based on feedback from students in previous semesters, the time required to complete this project is roughly divided as follows:

  • Approximately one-third of your time will be spent on inference-related tasks.
  • Approximately two-thirds will be dedicated to SQL injection tasks.

This estimate does not include time spent on extra credit opportunities (such as the Defender Task). Because some tasks may require thoughtful analysis and trial-and-error, it is strongly recommended that you start early to avoid last-minute difficulties.

Everyone works at their own pace, and skill sets vary—don’t be hard on yourself if you find you’re taking more time than estimated.

Feel free to post in the appropriate threads if you have questions about the project that aren’t addressed in the Write-Ups or have already been asked and answered on Ed. The TA team will be more than happy to help.

 

 

 

 

 

 

 

 

 

 

 

TASK A: GTID VERIFICATION

(flag0 – 0 pts)

NOTE:** Task A is the setup task and contains **one flag**: flag0. **Please review the Submission Details for specific instructions regarding the format.

This task sets up the project and generates hashes for the other flags, so verifying that the hashes are correct in Gradescope is critical. (USE A SUBMISSION)

Important

  • You only have 5 attempts to enter your correct GTID.
  • If you exceed this limit, you’ll be locked out and must restore your VM.

Steps to earn flag0

  1. Click on the Task A menu:
    • The page will open in a new tab.
  1. Enter your nine-digit GTID and click Submit:

Where can I find my GTID?

 

Submission Details

  • After receiving your hash, add it to the JSON file and submit it to Gradescope to verify its accuracy.
    • NOTE:** This step will “use up” one of your submissions (16 possible), but it’s highly recommended to ensure everything is set up correctly before moving on. An incorrect setup will cause all subsequent flags to fail! **Submissions can not be returned.
  • Once you’ve verified your correct hash, proceed to Task B!

Check the validity of your JSON before submitting to Gradescope.

See Submission Details for more information.

 

 

 

 

 

 

 

 

 

 

 

 

 

TASK B: INFERENCE ATTACK #1

(flag1 – 20 pts)

NOTE:** Task B is an array flag and contains **one flag**: flag1. **Please review the Submission Details for specific instructions regarding the format.

What Is an Inference Attack?

An inference attack isn’t a hack in the traditional sense—there’s no breaking into systems. Instead, it’s a method of deducing confidential information by analyzing seemingly harmless data that is available to many people either globally or within a company. Security regarding an inference attack deals with data confidentiality and providing data access/restriction on a need-to-know basis.

In most organizations, data security is enforced by access controls, which might restrict:

  • Entire tables (e.g., the employee table, which contains the complete employee list)
  • Individual rows (e.g., one specific row of an employee’s record)
  • Individual columns (e.g., a piece of information about an employee, like salary, gender, or position at work)

However, even when data access controls are in place, attackers (or curious employees) can sometimes combine multiple safe reports to uncover private information.

This is called an inference attack. You don’t have direct access to sensitive data, but you can infer it from the data you do have. Such attacks often exploit standard reports available to many employees. For example, a company might restrict certain columns to enforce access control, creating an employee roster report that everyone can view but excluding sensitive details like salary. However, if other reports are not carefully designed with the same protections, they may unintentionally reveal that information.

Scenario Overview

You’ve been given four internal reports from a single company:

  1. Employee Roster – Names and general information, no salary information.
  2. Tenure Report – Shows how long each employee has worked at the company.
  3. Average Salary by State – Aggregates the average salary of employees within each state where they live.
  4. Average Salary by Tenure – Aggregates the average salary based on years worked at the company.

Individually, none of these reports violates the company’s access controls on sensitive HR data. However, with some clever analysis, you realize the reports can be combined in a way that exposes a serious gap, allowing someone to pinpoint individual salaries.

Your Goal

By analyzing and cross-referencing the reports, you must cleverly combine the data to identify specific employees whose salaries you can infer with 100% certainty.

To complete Task B, for each exposed employee:

  • Record their hash ID (unique to your VM and generated when you completed Task A).
  • Record the exact salary (to two decimal places, without $ or commas).
  • Repeat this process for all relevant employees.

Save this information in the JSON file as part of your submission for flag1.

Success Criteria

  • At minimum, you should be able to infer the salary of one employee.
  • There may be multiple; continue analyzing to find as many as possible.
  • You can only uncover one employee’s salary at a time.
  • Submit employees whose salaries are definitively exposed.
  • Do not guess! Submissions are limited, and incorrect or guessed hashes will cost you.

Tips for Success

  • Look for uniqueness – Groups with only one person often provide the clearest clues.
  • Start with small groups – Examine non-employee reports for data that can be linked back to the employee list to reveal salaries.
  • Be methodical – Identify overlaps that isolate individual employees.
  • Double-check your logic – Only report salaries you are 100% certain of.

Steps to earn flag1

  1. Hover over the Task B Menu to reveal four links:
    • Employee Report
    • Duration Report
    • Salary by State Report
    • Salary by Duration Report

(Each page opens in a new tab.)

  1. Review the Reports:
    • Open each report by clicking its name; it will open in a new tab.
    • Look for data that can help you infer at least one employee’s salary.
    • Focus on combining data points across multiple reports.
  2. Identify an Employee:
    • Once you determine an employee’s exact salary, find their hash in the Employee Report (ID column) and record it.
  3. Record the Hash and Salary:
    • After finding and eliminating one employee, check if there is another whose salary can be determined precisely.
    • Use the known employee’s salary to calculate the next employee’s exact wage. Repeat the process until no further deductions are possible.
    • Add each employee found to the next position in the flag array (i.e., the order in which employees are found corresponds to array[order – 1]).

Hints

  • Look for unique patterns in the data across the four reports.
    • For example, a specific employee’s data might stand out when comparing one of the other reports in the Task Menu with the Employee report.
  • Combine Reports:
    • The key to an inference attack is combining data from multiple reports to isolate an individual’s information. Think about how different reports might intersect or overlap.
  • Track Data:
    • If you’re having trouble keeping track of the data, consider copying it into a spreadsheet or other application to manipulate and search more easily.
  • Logic Helps:
    • If you’ve already figured out one employee’s salary, the remaining employees might share a common trait with the ones you’ve found (you will need to “eliminate” the previously found employee’s salary to obtain the newly found employee’s salary).
  • Example:
    • A little bit of math is involved in figuring out the salary information of the following employee.
    • An example that has helped students in the past is: Imagine two people, A and B, buy eight apples together. If we know that A bought only six apples, we can “calculate” and “infer” that B bought only two apples.
  • General Hints about all Inference flags:
    • Only look at the data relevant to the task – Don’t get tied up in data that provides no value to the task.
    • If it looks like a table and acts like a table, it is probably a table. While this is unnecessary to complete the task, you can copy data from the report(s) into a spreadsheet program to manipulate it. This may help you track down the hole that exposes salaries.
    • If you use an external application to troubleshoot the data and try to sort, ensure the entire dataset is sorted.
  • General Hints about all Array flags:
    • You will receive credit for each element in the correct array.
      • For flag arrays with more than the hash (salary/cpt), there is no partial credit for the variety if the hash is correct but the other data is not.
    • You will not be penalized for not filling in the whole array.
      • i.e., if there are six elements in the array, and you have only two, you will not be penalized for the missing four.
    • You will be penalized if you overfill the expected array.
      • i.e., if there are six elements in the array, and you have seven, you will be penalized for the extra one.
    • You are reporting a salary in US currency, hence, two decimal places with no $.

Submission Details

  • The flag1 array should contain hashes paired with salaries, formatted as hash_salary (e.g., abc123_50000.00).
  • Salary of the found employee should be two decimal places.
  • Order matters —the employee you identify first should go in array[0] , the second employee in array[1] , and so on.

Good luck with Task B! After completing this task, move on to Task C.

Check the validity of your JSON before submitting to Gradescope.

See Submission Details for more information.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

TASK C: INFERENCE ATTACK #2

(flag2 – 20 pts)

NOTE:** Task C is an **array flag** and contains **one flag**: flag2. **Please review the Submission Details for specific instructions regarding the format.

Overview

Now that you’ve seen how an inference attack can expose sensitive information within a single company, it’s time to explore how the same technique can compromise data by linking together unrelated datasets.

In this task, you will carry out another inference attack — but on a broader scale. Instead of one source, you’ll work with four independent datasets that are publicly available online. Each comes from a different domain: hospital visits, voter registration, insurance claims, and medical codes.

Your mission is to analyze these datasets to identify specific individuals who have undergone certain medical procedures, while respecting the deidentification rules designed to protect privacy. Your task is to show how separate anonymized datasets can be combined to reveal sensitive information. Specifically, you will link medical data, particularly procedure records, to voter registration data to identify individuals and their associated medical history. This exercise highlights how incomplete deidentification, when paired with publicly available data, can still expose private information. This is, of course, a significant breach of HIPAA regulations (in fact, this exercise was inspired by the incident where the medical history of the governor of Massachusetts was made public in a similar fashion). Read more.

What you need to do

  1. Review the four reports: There are four reports you need to examine:
    1. Medical History – A sample report from a local hospital detailing the types of procedures performed over the last few years.
    2. Voter Registration – A report containing standard voter fields. Reports like this are often available from a local board of elections for all registered voters in a district.
    3. Insurance Claims – A partially de-identified dataset from an insurance company, created for public use by the same developers you’ve encountered earlier. “Partially de-identified” means that while some information was removed to obscure patient identities, enough remains that a determined analyst could still link data back to real individuals.
    4. Medical Codes – A reference report listing medical procedure and diagnosis codes.
  2. Find the compromised patients: The objective is to determine which specific patient in the Medical History report has been exposed and can be linked to a Voter Registration record.
  3. Identify the exposed individuals:
    1. After you’ve figured out which patient(s) have had procedures performed, you’ll need to look up their hash in the voter registration report.
    2. The hash will be listed next to the patient’s name, which is specific to you based on your GTID from Task A.
  4. Record the information:
    1. For each identified individual, record their **hash followed by the CPT code (procedure code). The format will be:** hash_cptcode (e.g., abc123_67890).
    2. What is a CPT Code?
      • Current Procedural Terminology (CPT) codes provide a standardized language for documenting medical services and procedures on a patient’s record. They are also used for billing purposes.
    3. Unlike the previous task, this is not an elimination-style inference attack. This means the order of the data does not matter; you can add entries in any sequence as you identify individuals. However, make sure each entry is accurate.

Steps to earn flag2

  1. Hover over the Task C Menu to reveal four links:
    • Medical History
    • Voter Registration
    • Insurance Claims
    • Medical Codes

(Each page opens in a new tab.)

  1. Examine the Reports:
    1. Open each of the reports and scrutinize the data.
    2. Pay special attention to identifying procedure data that can be traced back to specific individuals.
  1. Link the Data:
    1. You may want to look up the work done (what the procedure was) on the patient.
    2. Match that information from the other reports with the Voter Registration report to identify the person linked.
  1. Record the Hash and Procedure Code:
    1. For each identified individual, locate their hash in theVoter Registration report and note their CPT procedure code .
    2. Format the result as hash_cptcode (e.g., abc123_67890).
    3. Once you isolate a voter, continue to see if you can isolate another voter. Rinse and repeat until you can no longer isolate a voter.

Hints

  • Use Spreadsheet Tools:
    • Like in Task B, copying the data into a spreadsheet can help you organize and analyze the information.
    • Sorting and filtering the data might help identify links between the reports.
  • Look for Unique Identifiers:
    • In this task, the procedure data from the Insurance Claims and Medical History reports may help you find specific individuals.
    • To narrow your search, use the medical codes and cross-reference them with other data.
  • Multiple Data Sources:
    • Unlike Task B, where you narrowed down the search to a single record, in this task, you will need to use logic and combine multiple data sources to link individuals.

Submission Details

  • The flag2 array should contain hash_procedure pairs, formatted as hash_cptcode (e.g., abc123_67890).
  • Order does not matter —you can enter the hashes in any order.

Good luck with Task C! After completing this task, move on to Task D.

Check the validity of your JSON before submitting to Gradescope.

See Submission Details for more information.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

TASK D: INFERENCE ATTACK #3 – using DbGate

(flag3 – 7.5 pts)

NOTE:** Task D contains *one flag: flag3.* **Please review the Submission Details for specific instructions regarding the format.

Overview

Task: Inference Attack on Encoded Patient Data

You are tasked with investigating a potential security vulnerability in your company’s patient database. This requires performing an inference attack to determine whether sensitive data can still be uncovered despite the use of encoding measures.

Background

You’ve learned that the database administrator attempted to secure patient data by:

  • Encoding sensitive fields in the Patient table, including date of birth (DOB).
  • Encoding the social security number (SSN) using the patient’s original (unencoded) DOB as part of the encryption or masking scheme.

This database supports a Pediatric Office within the system.

During a recent visit with your child, you noticed a sign-in sheet openly displayed at the front desk. The sheet contained the following information:

  • Appointment Date
  • Patient first and last names
  • Provider’s last name
  • Appointment time
  • Arrival time

You discreetly photographed the sheet, which can be accessed through the Task D Menu link.

Result

Despite encoding efforts, you can derive the following sensitive information for at least one patient that is not on the sign-in sheet:

  • First name
  • Last name
  • Date of birth
  • Social Security number

Steps to earn flag3

  1. Click on the Task D menu
  2. Review the picture you took to see what data is provided that can be relevant in your hunt.
  3. Review the tables in the database using DbGate.
    • Review the Background and Setup page for instructions on how to set up/use DbGate.
    • You should not run INSERT, DELETE, or UPDATE queries on the tables — doing so may require a restore of the database!
  4. When you find the date of birth of the patient, you will need it to decrypt the SSN. Keep the following in mind: – Assume the DOB format for decryption is YYYYMMDD. – You’ve heard that the database administrator is using a built-in SQL Server function that encrypts data using a passphrase.

Hints

  • Use the data you have: Remember that in an inference attack, all you can use is the data you have available. Do not make “assumptions”. *Note: everything you need to find the social security number is provided!
  • Brute Force: Brute force techniques could be applied here, but it is not required, may be time-consuming, and may lead to false positives and waste submissions.
  • There is only a single patient that is correct; if you are finding more than one patient, review your logic/code.
  • Hash: Unlike all other tasks, this task has no hash.

Submission Details

  • The flag submission should be FirstNameLastName_DOB_SSN where the first initial of the first/last is Uppercase, dob is in the format YYYYMMDD, and ssn is only the numeric value (e.g., JaneDoe_19010101_123456789).

Good luck with Task D! After completing this task, move on to Task E.

Check the validity of your JSON before submitting to Gradescope.

See – Da for more information.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

TASK E: SQL INJECTION #1

(flag4 – 3 pts | flag5 – 5 pts | flag6 – 7 pts)

NOTE:** Task E contains **three flags**: flag4, flag5, and flag6. **Please review the Submission Details for specific instructions regarding the format.

Introduction: What Is SQL Injection?

This task highlights one of the most common yet preventable database vulnerabilities: SQL Injection (SQLi). SQL injection occurs when user input is not properly validated, allowing attackers to inject or modify SQL statements. The consequences can be severe, ranging from unauthorized access and data exposure to complete system compromise.

The root cause of SQL injection is often careless coding and weak input validation. Developers must ensure that all user-provided data is carefully inspected and that potentially harmful input is sanitized or blocked before reaching a SQL query. This process, known as input sanitization, prevents malicious commands from being executed and protects the database from manipulation

To help you get started, we recommend reviewing online resources like the SQL Injection Cheat Sheet for an overview of techniques and examples.

To complete this task, you will need to craft basic SQL code that can be entered into a form’s input field and executed by the website as part of a valid — but unintended — SQL statement. The goal is not to write complex queries, but to bypass weak SQL injection defenses. This could involve evading security checks, retrieving unauthorized information, or, in severe cases, altering the site’s data. Once a vulnerability is present, successful exploitation depends on your ability to understand or map the database’s structure.

Task Description

In this task, you will work with a sample web application that contains intentional SQL injection vulnerabilities in three different login implementations. Your objective is to exploit these flaws to bypass authentication and capture the corresponding hash flags.

You’ll log in using your GTID (as entered in Task A). You won’t know the actual password — instead, you’ll attempt SQL injections via the password field to force a successful login.

Objective Breakdown

Task E consists of three parts, each increasing in complexity. The site is updating its Legacy Login page, and all three hashes for Task E will change. The main security check on the legacy page is a direct match of the username, your GTID. You will not know the password needed to access the system, and you will not be provided with it.

  • For flag4, the developers are asking you to assess the difficulty of bypassing the existing security, given that a SQL Injection attack is possible.
  • They also want to explore ways to mitigate this risk. Specifically, they are considering whether to implement only client-side data sanitization or both client-side and server-side sanitization, and whether the same rules should apply consistently on both ends.
    • For flag5, you will attempt to bypass/beat the security using client-side data sanitization.
    • For flag6, you will attempt to bypass/beat the security using client and server-side data sanitization.
      • For this flag, remember that there will be server-side data sanitization that you cannot access, but you should assume the logic will duplicate the client-side data sanitization.
  • The client provided a snippet of the code they used on the legacy login page.
    • You can use this code to determine how to perform the SQL injection.
  • When you succeed, you will log in to the website using your GT ID and no password.
  • The injection will bypass the password requirements and log you in immediately.
  • Once logged in, the hash for flags 4-6, keyed to your GT ID, will be displayed.

Steps to earn flags 4, 5, & 6

  1. Hover over the Task E Menu to reveal three links:
    • Legacy Login
    • New Login – Client
    • New Login – Client/Server

(Each page opens in a new tab.)

  1. Perform the Injection
    • Enter your GTID as the username.
    • Enter a **crafted SQL injection payload in the** password field.
    • Submit the login form.
    • If successful, you’ll be logged in and shown your corresponding flag hash.
    • Summarising the flag challenges as below:

 

Flag Login Type Challenge Description
flag4 Legacy Login No sanitization — perform a basic SQL injection.
flag5 New Login – Client-side Sanitization Bypass/beat client-side JavaScript sanitization.
flag6 New Login – Client + Server-side Bypass/beat both client- and server-side sanitization.
  1. Record Your Hashes Copy the three flags you retrieve (flag4, flag5, flag6) into your final submission JSON.

Where’s My GTID?

If you’re unsure of your GTID, refer to the Submissions & Gradescope page for instructions.

Hints & Tips

For flag5 and flag6

  • Open the browser’s Developer Tools to inspect the client-side JavaScript.
    • If you don’t know how to open Developer Tools, a quick Google search can help you identify that for your browser.
  • Look for sanitization functions — analyze how the input is cleaned or blocked.
    • Input sanitization is cleaning and validating user input to prevent security threats like SQL injection.
  • Consider running these functions manually in the console to understand or bypass/beat them.

Sanitization Considerations

  • There is a difference between encoding, escaping, and sanitization. Understand the difference and the validity of using one or the other, remembering that the client/server-side code is doing sanitization.
  • You have access to the JS where the sanitization is done on the Client Side. You also know that client-side and server-side sanitization are the same.
    • Is there a way in DevTools to call a function again?
  • There is a difference between bypassing and beating the sanitization.
  • Unless it is a single character in the strip, you can “beat the sanitization.”
    • Only one of the lines strips a single character
  • Try to beat the sanitization line by line to understand a valid approach! Use the sanitization code against itself.
  • Natural sanitization is also at play.
    • PHP uses ? and & for variables, so while not “sanitized,” they will break up your injection if used.
    • The web browser converts the character # (if not HTTP encoded) into a blank character.

SQL Injection Hints

  • SQL not-equals can be written as != or <> — one may work even if the other is blocked.
  • Single quotes (‘) are often required to break out of the query string — they are usually not sanitized and are needed for most injections
  • You may think you are writing a SQL injection, but the database might read it as a regular string.
  • Remember that the entire injection statement goes into a string variable.
  • While the exception causing the data breach is returned for troubleshooting, this exception is based on the expectation that the input is a string, not an injection!
    • The exception returned is for your benefit and may NOT be shared in ED.
  • Null works great for an SQLi when you don’t know the schema and the developers don’t expect it. The developers are actively looking for it!
  • There is a difference between ending a statement to begin a new statement and escaping out of the statement.

Reference: Legacy Login Code

  • All login injection tasks are built off flag4.
    • It may be possible that flag4 can never be used as a template when sanitization is involved.
  • Below is the login code that you were able to obtain from the legacy login page (both username and password have a character limit of 96):

<?php

function login($username, $password) {

$sql = “SELECT username, password, hash, count(*) cnt INTO #tmp_user FROM users WHERE username=’$username'”;

$usercheck = $this->db->query($sql)->execute()->fetch();

 

if ($usercheck[‘cnt’] != 1) {

return false;

} else {

$sql = “SELECT hash FROM #tmp_user WHERE password=’$password'”;

$userdata = $this->db->query($sql)->next();

if ($userdata) {

return true;

} else {

return false;

}

}

}

?>

Submission Details

Once you have the hashes for all three flags, please copy and paste them into your submission JSON.

Good luck with Task E! After completing this task, move on to Task F.

Check the validity of your JSON before submitting to Gradescope.

For submission instructions, refer to the Submission Details page.

 

 

TASK F: SQL INJECTION #2 / INFERENCE ATTACK #4

(flag7 – 7.5 pts)

Note:** Task F is an **array flag** and contains **one flag**: flag7. **Please review the Submission Details for specific instructions regarding the format.

Overview

In this task, you’ll combine your knowledge of SQL Injection (SQLi) and Inference Attacks to identify specific students from a fictional grade list who received an “F” in the class.

You’ll start by performing an SQL injection to bypass the login form and gain access to a restricted section of the site. Once logged in, you’ll encounter a security question. To answer it, you’ll use Open-Source Intelligence (OSINT) techniques — methods commonly employed by social engineers to gather publicly available information and exploit it for unauthorized access.

If you complete both stages (SQLi and OSINT), you’ll access extended roster data and use an inference attack to identify students who failed the course.

What is OSINT?

OSINT stands for Open-Source Intelligence. It refers to the practice of collecting and analyzing information from publicly available sources — essentially, anything you can legally access online or offline without hacking or breaking into systems.

For example, imagine you’re working on a school project about climate change. You might research news articles, government reports, YouTube videos, and social media posts to gather insights. That’s OSINT in action — using open sources to build practical knowledge.

Learn more about it here – What is OSINT?

Objective

Your goal is to:

  1. Identify a valid username through OSINT techniques.
  2. Perform a **SQL Injection attack to bypass the** password field.
  3. Complete a Multi-factor Authentication request using OSINT techniques to obtain the prompted information relevant to the account.
  4. Access the roster data and identify the students who received an “F”.
  5. Submit those student names in the flag7 array (order does not matter).

Steps to Complete

  1. Click the Task F Menu
  2. Login Attempt
    • Use the username you discovered or inferred via OSINT.
    • Enter an SQL injection payload in the password field to bypass authentication.
    • Submit the form.
  3. Security Question
    • If your injection is successful, you’ll receive a multi-authentication prompt (e.g., a security question or a PIN).
    • Use OSINT methods to deduce the correct response. Pay attention to contextual hints.
  4. Access Extended Roster
    • If the second authentication step is successful, you’ll gain access to detailed roster data.
    • You must gain some inference knowledge to identify the students who received an F.
    • Identify all students who have received an “F” grade and record their hashes.

Hints & Guidance

  • No input sanitization is present on the login form, particularly in the password field. This means the SQL injection should work similarly to earlier tasks.
  • You cannot use injection on the Multi-factor Authentication page. Don’t try to do that; it will not give you what you want.
  • If you see the security question prompt, it confirms your SQL injection worked.
  • OSINT Focus:
    • Consider what information users might use to answer security questions.
    • Use the provided context or details on the login page to infer possible answers.
    • In the real world, this might include social media (professional and personal), forums, or previously disclosed information — here, you’re working within the fictional context of this task.
  • This is a combination challenge. You are applying:
    • SQL Injection to break into the system.
    • OSINT to get past multi-factor authentication.
    • Inference to extract meaningful insights from data.

Submission Details

Once you identify the students who received an “F”, add the hashes (no particular order) into the flag7 array before proceeding to Task F (order of report does not matter).

Good luck with Task F! After completing this task, move on to Task G.

Check the validity of your JSON before submitting to Gradescope.

For detailed submission guidelines, refer to the Submission Details page.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

TASK G: SQL INJECTION #3

(flag8 – 7.5 pts / flag9 – 7.5 pts)

NOTE:** Task G contains **two flags**: flag8 and flag9. flag9 is reliant on the “partial completion” of flag8 (while you do not need the hash for flag8 to complete flag9, you do need the schema). **Please review the Submission Details for specific instructions regarding the format.

Overview

In Task G, you’ll perform two advanced SQL injection attacks targeting a fictional music store database. These attacks will help you:

  1. (flag8) Modify access rights to uncover hidden table schemas.
  2. (flag9) Extract a valid database user’s login credentials through SQL injection, then log into a simulated admin console to retrieve your flag.

Understanding the “Schema” Page and Your Goal

You’re working with a music store’s website that has a search engine for music albums. While exploring the site, you’ve found a page called “schema.” This page shows behind-the-scenes information — specifically, the structure (or metadata) of a database table used in the main report.

What You’ve Noticed

  • The website follows a predictable pattern, especially in how links are structured.
  • Based on this pattern, manipulating those links is likely a way to gain more access.
  • This could let you see more database information than what’s shown by default.

What You Know

  • The Schema page uses a database user called Schema to connect to the database – confusing, yes, but like programming variables, sometimes the variable name is precisely what it is used for.
  • This user only has read-only access to a table called Music, so you can only see the Music table’s schema.

What You Need to Do

  • Your goal is to use injection to give the Schema user access to all tables, not just Music.
  • That way, when you reaccess the schema page, you’ll see more than just the Music table (if you performed the injection correctly and found the correct manipulation of the URL).
  • Right now, you don’t know the names of the other tables or schemas — that’s part of the challenge.

Task Breakdown

flag8 – Privilege Escalation via Injection

The Schema page reveals metadata for the Music table. This view is read-only and tied to a limited-access database user named Schema.

The Report page uses this same schema to generate queries. Your job is to figure out how to extend the Schema user’s access, so it can view more than just the Music table.

Once you succeed, the Schema page will show additional tables. You will notice that the new table does not have a hash. There is a reason for this:

  • This allows students who are unable to complete the privilege escalation injection to attempt the next flag.

To obtain the hash, you must create the required privilege escalation using the “search” functionality on the Report page. When successful, the new hash will appear in the schema header. That hash is your flag8.

flag9 – Credential Extraction and Login

After gaining visibility into new database tables, you must construct another SQL injection on the Report page. This time, your injection should extract a username and password from another table.

You’ll then log into the Login page using the credentials you uncovered. If successful, you’ll land in a simulated “management console,” where your flag9 hash will be displayed.

Unlike the other login forms thus far, this login page is locked down, and SQLi will not work. You are more than willing to try, but as the great DBA Obi-Wan Kenobi said:

How to Proceed

  1. Hover over the Task G Menu to reveal three links:
    • Schema
    • Report
    • Login

(Each page opens in a new tab.)

  1. Schema Page
    • Displays the schema for the Music table.
    • Use this to understand how the schema is structured and explore ways to gain broader access.
  2. Report Page
    • Accepts a search input, which runs a SELECT … FROM … WHERE SQL query.
    • This is your injection point for both flag8 and flag9.
  3. Login Page
    • Once you discover valid login credentials from your injection, enter them here to retrieve flag9.

Steps to earn flag8 & flag9

flag8 Hash

  1. Display the New Table
    1. By reviewing the Schema page and URL, is there anything you can manipulate to show all schema, not just the Music table schema, which is available to all users?
  2. Find a Privileged Role
    1. Is there a database role you can add your user to that allows viewing (read) other table schemas (not just Music)?
  3. Inject the Role Assignment
    1. Go to the Report page.
    2. Enter your injection in the search bar to add the role.
    3. Click Search.
  4. Check the Schema Page
    1. Return to the Schema page that you have already configured to show the new table.
    2. If your injection worked, the hash for the new table will appear.
      1. At the top of the new table, check the Schema header.
      2. Your flag8 hash will be displayed there.
  5. Submit Your Flag
    1. Add the hash to your JSON file under “flag8”.
    2. Submit it to Gradescope.

flag9 Hash

  1. Query Other Tables
    1. Now that you can view the other table’s schema, try using the search bar again.
    2. This time, inject a query that returns rows from a different table, not just the default one.
      1. Remember that the default query (which you cannot change) will always return data from the Music table. This means you need to find a way to add data from another table to the default “SELECT * FROM Music” query.
  2. Run Your Injection
    1. Go to the Report page.
    2. Type your injection into the search input.
    3. Click Search.
  3. Look for Credentials
    1. If successful, the report results will display a username and password.
    2. These credentials are for the Management Console login.
  4. Log In
    1. Go to the Login screen.
    2. Enter the username and password from the report data.
    3. If correct, you’ll access the system.
  5. Get and Submit the Hash
    1. Once logged in, your flag9 hash will be shown on the screen.
    2. Add it to your JSON file under “flag9”.
    3. Submit your file to Gradescope.

Hints & Tips

General

  • The Report page is your main injection point.
  • Think carefully about how to construct SQL that extends queries to include other data (joins, unions, etc.).
  • You’ll need to experiment and iterate to find the correct structure.

flag8

  • There are two parts:
    1. Viewing the schema of all tables.
    2. Gaining access/rights to the non-public table.
  • There is more than one way to complete this task; however, it may be helpful to use SQL Server’s built-in stored procedures to escalate privileges.
  • You don’t need admin rights — just enough read access to expose the hidden table. The developers are looking for unauthorized admin access!
  • A successful injection might appear to fail — verify by checking the Schema page again.
  • Don’t try to brute force access; that might corrupt your virtual environment.

flag9

  • flag9 can be completed independently of flag8 (to a degree – you must obtain the schema for the missing table). Flag9 can be achieved without providing the required rights to the hidden table in flag8 because the database user schema is not used on the report page, and the database user being used has access to both tables.
  • Can you combine results from multiple tables with a UNION or similar SQL pattern?
  • Think about what the data output tells you — success might look subtle.

URL Exploration

  • You may find useful pages by manipulating URLs directly.
  • Patterns in link structures may reveal hidden or undocumented pages.

Security Model

  • Client-side sanitization is gone here, but server-side logic remains similar to earlier tasks.
  • Login injection does not work on this task — injection is limited to the Report page only.

Submission Details

Once you’ve obtained the hashes, add them to your submission JSON and check the files for any formatting issues before submitting to Gradescope.

Good luck with Task G! After completing this task, move on to Task H.

Check the validity of your JSON before submitting to Gradescope.

See Submission Details for more information.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

TASK H: SQL INJECTION #4

(flag10 – 7.5 pts / flag11 – 7.5 pts)

NOTE:** Task H contains **two flags**: flag10 and flag11. flag11 is reliant on the completion of flag10 (or you can choose to use your GTID for the username and password, but not receive a hash for flag10). **Please review the Submission Details for specific instructions regarding the format.

Overview

You’re testing the security of a banking website that allows users to log in and view transaction history. Your objective is to prove the system is vulnerable through two stages of SQL injection attacks :

  • flag10: Log in as a valid user without knowing any credentials.
  • flag11: Modify the transaction history to show a $2150.50 balance, no overdraw, and exactly 10 transactions .

Steps to earn flag10 & flag11

flag10 – Login Without Username

Goal: Log in to the banking site without knowing a valid username. You must use SQL injection in both the username and password fields to:

  • Extract or generate a valid user from the database (see the hints section below for assistance in knowing what the database structure looks like)
  • Pass the authentication check

Key Hints:

  • Username injection must extract or resolve to a valid row from the user table.
  • The injection logic will differ between the username and password fields.
  • Brute forcing usernames, while possible, will be extremely tedious and time-consuming – aim to write a smart injection.
  • You must use the skills you gained in Task D with more sophisticated SQL knowledge.
  • Expect the site to run something like:
    • SELECT * FROM users WHERE username = ‘<input>’ AND password = ‘<input>’
  • There is no sanitization for this task, the same as there was no sanitization for flag4 password injection
  • The username injection should extract a real entry. While there are various ways to complete the attack, a sub-query may be a beneficial place to start.

What success looks like:

  • The site logs you in, revealing a username and a hash just beneath it.
  • That hash is your flag10.

flag11 – Tamper Transaction History

Goal: Once logged in, you’ll see 10 transaction entries and a balance. One entry will be an overdraw fee (-$25). You must:

  1. Remove the overdraw fee completely (including from any audit trail).
  2. Insert a new transaction to raise the total balance to exactly $2150.50.
  3. Maintain 10 total transactions .

Critical Part: You can’t inject into text boxes. You must modify the form payload via Chrome Developer Tools before the form submits. You are altering the data sent to the server, not to the UI!

  • Routing & Account Number are only to simulate a real-world transaction (you do not need to find/use real information)
  • Every time you log out/in, the transaction ledger resets

How to Execute flag11

  1. Find the Transfer Form:
    • Click the Transfer button to open the form.
    • You’ll see fields like Routing Number, Account Number, and Amount.
  2. Open Chrome DevTools (F12):
    • Set a breakpoint on the Submit button of the pop-up transfer form.
  3. Alter the JSON/Form Payload:
    • Use DevTools to pause the form transmission and inject custom SQL into the backend submission.
  4. Craft the SQL Injection:
    • You must inject an SQL statement that:
      • Inserts a credit transaction (you can set the $ amount using the UI, but will need to change the payload before it sends)
      • Deletes the overdraw fee entry and removes it from the audit trail

NOTE: This backend expects/uses JSON.

  1. Syntax Style:
    • The backend uses SQL in one of these forms:
      • INSERT INTO table (…) VALUES (…)
      • INSERT INTO table (…) SELECT … FROM …
    • Determine which one is used by guessing or trial/error – again to simulate a real-world scenario (this information would most likely not be publicly available).
  2. Verify Results:
    • Final screen must show:
      • Balance: $2150.50
      • 10 Transactions
      • No Overdraw Fee
      • Validation Hash — This is your flag11

NOTE: If anything is off (wrong balance, 11 transactions, or an overdraw still exists), the hash will be wrong.

Hints Recap

flag0

  • Look at how a sub-query could be helpful to create your injection for the username
  • You have already completed the requirements for the password injection in a previous flag
  • You must pull a real user from the DB; attempting an injection to bypass the user will not work.

flag11

  • Use DevTools to edit a field value before the form is submitted.
  • Find the table schema; maybe something you have previously done may work here as well.
  • Look for a JavaScript function that is delivering your payload and pause the transmission to update the transaction with your payload.
  • Then continue the transmission of your payload and check the results.

Submission Details

Once you’ve obtained the correct hashes, submit your JSON file on Gradescope after checking for JSON formatting issues.

Good luck with Task H! After completing this task, move on to Task I (REMEMBER: Task I is extra-credit).

Check the validity of your JSON before submitting to Gradescope.

See Submission Details for more information.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Task I: Defend Against Inference and SQL Injection Attacks

(flag12 – 20 pts)

NOTE:** Task I is an **array flag** (Inference – Elements 1-3 → 10 pts / SQLi – Element 4 → 10 pts) and contains **one flag**: flag12. **Please review the Submission Details for specific instructions regarding the format.

Overview

In this task, you’ll shift from attacking to defending. Instead of exploiting vulnerabilities (as in Tasks A–G), you’ll write SQL to prevent inference and SQL injection (SQLi) attacks.

NOTE: All validations will initially show yellow (not run). Click the Run test button next to each to validate individually.

Accessing the Test Pages

  1. Hover over the Task I Menu to reveal two links:
    • Test Pages
    • Run Validation Checks

(Each page opens in a new tab.)

  1. Click Test Pages (opens in a new tab).
    1. Use the dropdown to select from:
      1. Employee Report Search → Test SQLi UNION injection defense
      2. Avg Salary By Title Report → Validate TitleGenderAgeReport
      3. Avg Salary By Dept Report → Validate DepartmentEthnicityDurationReport
      4. Login → Test SQLi login injection defense

Part 1: Inference Defense (flag12 Elements 1–3)

View: TitleGenderAgeReport

  • Report average salary and bonus by Title, Gender, and Age
  • Ensure k-anonymity (k=2) is enforced
  • Follow the base table column order
  • No default sort required

TGA Report De-Identification Rules

  • Age Buckets: <36, 36–50, 51–65, >65
  • Priority: Title > Gender > Age
  • Use ‘Redact’ to mask data (use ‘Other’ for Title)
  • If needed for anonymity, use ‘Omit’ to exclude records

TGA Format Rules

  • Salary: average, 2 decimal places, $ prefix, no commas
  • Bonus: average, 2 decimal places, % suffix, remove trailing zeroes

View: DepartmentEthnicityDurationReport

  • Report average salary and bonus by Department, Ethnicity, and Duration
  • Calculate Duration from Hire Date to Today (365-day years, FLOOR could be useful)
  • Follow table column order
  • No default sort required

DED Report De-Identification Rules

  • Duration Buckets: <6, 6–10, 11–20, >20
  • Priority: Department > Ethnicity > Duration
  • Use ‘Redact’ to mask data (use ‘Other’ for Department)
  • If needed for anonymity, use ‘Omit’ to exclude records

DED Format Rules

  • Salary: average, 2 decimal places, $ prefix, no commas
  • Bonus: average, 2 decimal places, % suffix, remove trailing zeroes

Part 2: SQL Injection Defense (flag12 Element 4)

Procedure: uspSQLReplace

  • Do not change input parameters or how @filter is returned
  • Only return the @filter variable (no SELECT statements)
  • Client-side Sanitization was “provided” in other flags. You should convert the provided sanitization logic to SQL and use it here
  • Prevent attacks like ‘ OR 1=1 — and UNION SELECT
  • Allow valid inputs that exist (e.g., for login, if a user truly has the ultra secure password “password”, you must allow it while still sanitizing injection attempts)
  • Validate both Login and Search input

Tips

  • Use Common Table Expressions (CTEs) to group and bucket data
  • Prioritize columns in order of importance when grouping
  • Only hardcode values when told (e.g., ‘Redact’, ‘Other’, ‘Omit’)
  • Use sp_executesql for secure dynamic SQL (docs)

Reset Code (If Needed)

— Reset TitleGenderAgeReport

ALTER VIEW [dbo].[TitleGenderAgeReport] AS

SELECT *

FROM CompanyEmployees;

GO

 

— Reset DepartmentEthnicityDurationReport

ALTER VIEW [dbo].[DepartmentEthnicityDurationReport] AS

SELECT *, ” AS Duration

FROM CompanyEmployees;

GO

 

— Reset uspSQLReplace

ALTER PROCEDURE [dbo].[uspSQLReplace]

(@tblName varchar(64), @colName varchar(64), @colValue varchar(1024), @colDataType varchar(32), @filter varchar(1024) OUTPUT)

AS

BEGIN

DECLARE @sql nvarchar(max);

DECLARE @param nvarchar(64);

DECLARE @found int = 0;

 

SELECT @filter = @colValue;

 

/*Student Code Should Go Here*/

END;

GO

DbGate:

Below is how to edit a view:

  • Click on the view you are working on
    • Click on SQL
  • Click on Alter View
  • Main Alter View screen

Submission Details

Include your flag12 hash (array of 4 elements) in the JSON file and submit it to Gradescope!

Good luck with Task I!

Check the validity of your JSON before submitting to Gradescope.

See Submission Details for more information.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SUBMISSION DETAILS:

File Submission Instructions

This project must be submitted through Gradescope, which you can access via Canvas.

How to Submit

  1. Go to the Canvas course page for this class.
  2. Click on Gradescope in the sidebar.
  3. Select Project DBSec from the available assignments.
  4. Upload your final submission file: project_dbsec.json.

Make sure you validate your JSON file before submitting! Use https://jsonlint.com to check your JSON file format.

Important Notes About Gradescope Behavior

Gradescope uses an auto-grader to evaluate your submission. Please be aware of the following:

  • Do not submit again while the grader is running. The system may show partial results while the grading is in progress. These are not final. The only valid results are those displayed after the grader finishes.
  • If Gradescope shows “Autograder ran into a problem” :
    • Create a Private Post on Ed Discussions requesting a TA to complete a manual re-run of your last valid submission.
    • If you decide to resubmit instead, be aware that:
      • It will count as one of your 16 allowed submissions, and the failed submission will not be returned to you, even if the grader failed before evaluating it.
      • Make sure to mark your last valid submission as Active (Default behavior – Last submission will be marked as active)

Submission Limits and Responsibilities

  • You are allowed a maximum of 16 submissions for this project.
  • This limit is generous and based on prior student experience. Most students complete the project with far fewer.
    • Task A should only take one submission (2 if you typed your GTID wrong). *Note that the return of the hash also has your GTID for Task A.
    • flags 4-6, 8-11, as long as your GTID from Task A was correct, will only return one possible hash upon completion.
    • This leaves a possible thirteen (13) attempts for flags 1-2, 6, 10.
    • flag12 is an extra credit, and as long as your GTID from Task A was correct, it will only return the possible hashes upon completion.
  • If you exceed 16 submissions, any submission beyond that point will be automatically graded as 0.
  • Gradescope uses your most recent submission by default, so your final grade could be a zero unless you manually mark Active on a previous valid submission.

To avoid this:

  • Always verify that your most recent submission is valid and fully graded.
  • Use the “Activate Submission” option on Gradescope to select the one you want to use for grading.
  • This is explained in detail in the Submissions & Gradescope page.
  • Remember: You are responsible for making sure your selected submission is the one sent to Canvas when the project closes.

JSON File Format and Editing Guidelines

The file you will submit is called: project_dbsec.json

This file is available on the Desktop of the provided Virtual Machine. If it is missing, you can create one from scratch by copying the format below (see final section).

File Editing Rules

  • Use a plain text editor to create or modify the JSON file.
    • Recommended editors: TextEdit (set to plain text), Vim, Nano, Notepad++
    • Do not use: Word processors such as LibreOffice Writer, Microsoft Word, or any tool that may introduce special characters or formatting. These will break your file and cause autograder errors.
  • Avoid:
    • Ellipses (…)
    • Angle brackets (< and >)
    • Smart quotes or curly quotes
    • Trailing commas
    • Any extra characters that are not part of valid JSON syntax
  • Validate your JSON before submitting using an online tool such as JSONLint

Special Formatting Instructions for Array Flags

  • Tasks B, C, F, and I are array flags:
    • Array flags begin with [ and end with ]
    • Elements in the array use ‘ to start/end the element, and the elements are separated using ,
    • Do not use double quotes(“ “) or omit the single quotes(‘) — incorrect formatting will result in autograder failure.
  • Task B and Task C require additional data beyond the hash:
    • Task B: Append the employee’s salary to the hash with an underscore (_). Format the salary to two decimal places, with no dollar sign and no commas.
      • Format for a single element of the Array flag – ‘<copy employee hash 1 here>_<salary of employee 1 *no $ or comma **must be numeric – i.e 85650.10>’
    • Task C : Append the CPT code of the voter to the hash, using an underscore as well.
      • Format for a single element of the Array flag – ‘<copy voter hash here>_<cpt code of voter>’
    • All values (including hashes with appended data) should be placed in the JSON array as strings wrapped in single quotes.

Template Format for project_dbsec.json

NOTE: New line added after every element is for clarity and **should not be used **, as it can cause the JSON to become invalid. You are responsible for ensuring your JSON is valid before submission.

{

“flag0”: “<copy flag0 hash here>”,

“flag1”: “[

‘<copy employee hash 1 here>_<salary of employee 1>’,

‘<copy employee hash 2 here>_<salary of employee 2>’,

‘<copy employee hash 3 here>_<salary of employee 3>’,

‘<copy employee hash 4 here>_<salary of employee 4>’,

‘<copy employee hash 5 here>_<salary of employee 5>’,

‘<copy employee hash 6 here>_<salary of employee 6>’,

‘<copy employee hash 7 here>_<salary of employee 7>’,

‘<copy employee hash 8 here>_<salary of employee 8>’

]”,

“flag2”: “[

‘<copy voter hash here>_<cpt code of voter>’,

‘<copy voter hash here>_<cpt code of voter>’,

‘<copy voter hash here>_<cpt code of voter>’,

‘<copy voter hash here>_<cpt code of voter>’,

‘<copy voter hash here>_<cpt code of voter>’,

‘<copy voter hash here>_<cpt code of voter>’,

‘<copy voter hash here>_<cpt code of voter>’,

‘<copy voter hash here>_<cpt code of voter>’

]”,

“flag3”: “<FirstNameLastName>_<DateOfBirth>_<SocialSecurityNumber>”,

“flag4”: “<copy flag4 hash here>”,

“flag5”: “<copy flag5 hash here>”,

“flag6”: “<copy flag6 hash here>”,

“flag7”: “[

‘<copy student hash here>’,

‘<copy student hash here>’,

‘<copy student hash here>’,

‘<copy student hash here>’,

‘<copy student hash here>’,

‘<copy student hash here>’,

‘<copy student hash here>’,

‘<copy student hash here>’

]”,

“flag8”: “<copy flag8 hash here>”,

“flag9”: “<copy flag9 hash here>”,

“flag10”: “<copy flag10 hash here>”,

“flag11”: “<copy flag11 hash here>”,

“flag12”: “[

‘<copy TitleGenderAgeReport hash here>’,

‘<copy DepartmentEthnicityDurationReport hash here>’,

‘<copy EmployeeReport hash here>’,

‘<copy SQLInjection hash here>’

]”

}

  • If a task has multiple entries where the order matters (e.g., Task B), insert each entry in the appropriate position in the array.
  • Do not include ellipses (…) or additional formatting.
  • Ensure you enter only the required number of values, in the correct order.

NOTE: The number of entries for an array flag, except Flag12, are mere examples and do not suggest the total number of flags; this is an added challenge. In reality, the number of flags can be less or more than the example flag count for each array flag.

An example of what the submitted file content should look like

If you need to recreate the file manually, use this template structure. Replace the placeholders with your actual flag values:

{

“flag0”: “f64c3063af19c10afeeec201ce07a92627dbfe2ad594b5729c719d7727c1”,

“flag1”: “[‘a478e5926bce_85650.00’, ‘dd94bd958dba_132950.05’, ‘d958dbd94bda_119950.15’, ‘dd94958dba_79950.10’, ‘dbdd94bd958dba_85950.35’, ‘dd94bd958dba_102450.55’]”,

“flag2”: “[‘164266c8e971_97760’, ‘3b70f2b5b53c_96319′, ’66c16428e971_33760’, ‘3bb5b70f253c_94519’, ‘428e66c16971_85760′, ’70f23bb5b53c_96119’]”,

“flag3”: “JaneDoe_19010101_123456789”,

“flag4”: “e96281643cb6661fbc0533e28e64”,

“flag5”: “7552619643cb6661fb9b738c0533”,

“flag6”: “aa2ba3bb2456f39d51553298e821”,

“flag7”: “[‘f2e7aa586cd8’, ‘ed9361f82830’, ‘f2e7aa586cd8’, ‘ed9361f82830’, ‘f2e7aa586cd8’, ‘ed9361f82830’]”,

“flag8”: “8e61e428168eeb43cb6661fee962”,

“flag9”: “58661e43cb6661fc8eebd8e3ce71”,

“flag10”: “a9268eeb2bfead641e3cb6661f59”,

“flag11”: “301fa58e8eeba221eec155a0f562”,

“flag12”: “[‘679fba439156’, ‘cafa10f622c7’, ‘d5e36dc5a36b’, ‘2d2a0cc88888’]”

}

The # of elements in the array flags do not coordinate with the submission and is for demonstration only. it is up to you to determine for those flags how many elements to report!

 

 

 

 

 

 

 

 

 

TROUBLESHOOTING

Am I on the correct page for the flag I am trying to solve?

  • Your browser tab will start with the flag# followed by the page. i.e. – flag4 – Login (Legacy)

Menu Links do not load / Website(s) are not loading (The site can’t be reached / We’re having trouble finding that site)

  • Steps to fix it:
    1. Stop the Container:
      • Open a Terminal window and run: ./StopContainer.sh.
      • Wait for the message: “Container Stopped, current datetime.”
    2. Start the Container:
      • Open a Terminal window and run: ./StartContainer.sh.
      • Wait for the message: “Container Started, current datetime.”
    3. Open Chrome Browser:

Copying and pasting between the Workstation and the Virtual Machine isn’t working

  1. Open your Virtual Machine (VM) using the VirtualBox application.
  2. From the VirtualBox menu, select Devices -> Shared Clipboard.
  3. Ensure it is set to Bidirectional.

Task A submission failed

  1. Check your GTID: Make sure there are no extra spaces at the beginning or end.
  2. Replace the placeholder in the submission: Ensure everything between “” is replaced with your hash (including < > symbols).
  3. Validate your JSON.
  4. Re-complete Task A: Follow the steps in Task A again.

Tasks B-I are not unlocked and say “Task A needs to be completed before this Task is unlocked.”

  1. Complete Task A.
  2. If you’ve already completed Task A, but the system was reset, compare the hash from your previous submission with the new hash after re-completing Task A.

Task A says, “You have reached the maximum # of entries; the VM will need to be restored.”

  1. Restore the VM from the provided OVA file.

JavaScript files are not showing in the Web Developer Tools

  1. Ensure you are under the Sources tab in Developer Tools.
  2. Expand the tree view under Page > localhost > assets > scripts
  3. If the .js file is missing:
    • Close and reopen your browser.
    • Clear your browser cache before retrying.
    • Try using a different browser (e.g., Firefox or Chrome).

The VM is bricked (no longer functioning)

  1. Restore the VM from the OVA file.

Gradescope won’t allow more submissions (16 submissions limit)

  1. This project has a limit of 16 submissions; you cannot make more than the limited number of submissions.
  2. Ensure your highest-scored submission is marked “Active” for grading.
  3. If you believe this is an error, contact a TA via private ED discussion with the relevant details.

Example Error page

The above error page shows up

  1. Close your browser.
  2. Revisit the page that caused the error.

If the above steps did not resolve the error

  • Do this to fix it:
    1. Stop the Container:
      • Open a Terminal window.
      • Run the command: ./StopContainer.sh.
      • Wait for the message: “Container Stopped, current datetime.”
    2. Start the Container:
      • Open a Terminal window.
      • Run the command: ./StartContainer.sh.
      • Wait for the message: “Container Started, current datetime.”

If the above steps still did not resolve the error, try these steps

  1. Restart the VM.
  2. Log in to the VM.
  3. Stop the Application:
    • Open a Terminal window.
    • Run the command: ./StopContainer.sh.
    • Wait for the message: “Container Stopped, current datetime.”
  4. Start the Application:
    • Open a Terminal window.
    • Run the command: ./StartContainer.sh.
    • Wait for the message: “Container Started, current datetime.”
  5. Access the page that errored.

Finally, if the above steps still did not resolve the error, try these steps

  • Do this to fix it:
    1. Restore the VM.
    2. Start the VM and log in.
    3. Stop the Container:
      • Open a Terminal window.
      • Run the command: ./StopContainer.sh.
      • Wait for the message: “Container Stopped, current datetime.”
    4. Start the Container:
      • Open a Terminal window.
      • Run the command: ./StartContainer.sh.
      • Wait for the message: “Container Started, current datetime.”
    5. Access the page that errored.
  • If you still have problems, contact the TA staff via a private ED post to get help with your specific error.

 

  • SCRIPTSSTEPS_CS6035-DBSEC-a32wmy.zip

Related products

Related in this category

More in this category