[SOLVED] Cs6035 dbsec 2025 project

Projects / Database Security / Background and Setup
BACKGROUND:

“Good News, Everyone!” A penetration testing firm has hired you, and as your first assignment, you’ve been given a small portfolio of websites to test. You immediately notice that the pages of different clients look very similar, with similar structure and conventions. With more digging, you determine that the same software company created them. Having this information, you know that any vulnerability you find on one site will likely be seen on every client’s site. You also recognize the style of the pages and vulnerabilities and feel you have worked with this company in the past, remembering that they often use the same logic on the client and server side.
You aim to bring visibility to these vulnerabilities by finding sensitive data not intended for viewing and testing the client’s security on sites where user input is required. As a pen tester, you will have to wear many hats. Good luck, and we hope you enjoy learning about a couple of database attacks.
SETUP:
To get set up for the tasks, carefully follow the steps below. Log into the VM with the following user.
Username: Provided on project release in Database Security Project Canvas post
Password: Provided on project release in Database Security Project Canvas post
project_dbsec.json is available on the desktop. Put all hashes in this file and submit it as your final deliverable in Gradescope.
• To edit project_dbsec.json:
• The file should be located on the desktop; if not, please copy the format listed in the Submission section
• Note: You can create and edit this file using TextEdit or Vim. Do not use LibreOffice or any Word Document editor. It must be in proper JSON format with no special characters to pass the autograder, and these Word Document editors will likely introduce special characters.
To access the project:
• You do not need to run the system upgrade and click the cancel button.
• Start the Container:
• Run this at the terminal to start the requirements of the project
$ ./StartContainer.sh
• Open Web Browser:
• Open your browser, choosing Google Chrome or Firefox.
• In the address bar, go to the URL http://www.gt-cs6035.com
• Start with Task A
• Once complete, submit the flag0 hash to verify your GTID is correct, as all Tasks are based on this first hash!
• Other than Task A being completed first, Tasks A – H can be completed in whatever order.
However, the instructions are laid out for simplicity, completing the tasks in order.
Azure Data Studio Setup: Azure Data Studio is unfortunately a bit of a resource hog. You only really need it for the extra credit. Any testing can be done online with SQLFiddle. Another option is to SSH into the VM and run Azure Data Studio on the host.
• Open Azure Data Studio (from Terminal Window: azuredatastudio)
• Expand the Connections (if not already expanded), and Click on New Connection

• Fill out the Connection Details based on the screenshot below
• Server -> www.gt-cs6035.com
• User name -> CS6035Student
• Password -> CS6035DBSec

• Click connect, and if the Connection error below appears, click Enable Trust server certificate.

PROJECT TIME EXPECTATIONS: Previous semester students have stated that you should expect the project breakdown regarding time allocation to be approximately 1/3 of Inference Tasks and 2/3 of Injection Tasks. This excludes extra-credit Tasks (Defender Task, for example). It is highly recommended to start early!
GATECH ID
• Please see the RequiredReading page for instructions on retrieving your GTID.
• Be very careful! When you copy and paste, strip off all leading spaces or special characters.

4/7/25, 2:58 PM Task A | CS 6035
CS 6035

Projects / Database Security / Task A
Task A: GTID Verification (flag0 – 5 pts)
*NOTE – Task A has 1 flag (flag0)
This task will set up the rest of the project, so we offer five free points—like the bonus points you got in kindergarten for putting your name (and spelling it right) on a test.
Note that you **only have five attempts at entering your correct GTID; at this point, you will be locked out and have to restore your VM.
To earn your hash for flag0, you must perform the following actions.
1 Click on the Task A Menu from the home page. The page will open in a new tab.

1 Enter your nine-digit GTID and click Submit Where do I find my GTID?
• Please see the Required Reading page for instructions to retrieve your GTID.
https://github.gatech.edu/pages/cs6035-tools/cs6035-tools.github.io/Projects/DatabaseSecurity/TaskA.html 1/2 4/7/25, 2:58 PM Task A | CS 6035

Hints:
• After obtaining your hash, add your flag0 hash into the JSON file and submit it to Gradescope to verify it is correct before proceeding to Tasks B-H!
• While this means you will “burn” a submission, it is HIGHLY recommended as the wrong setup means all flags will generate incorrectly!
Include your flag0 hash into the JSON file, and now, onto Task B!
See Submission Details for more information.
https://github.gatech.edu/pages/cs6035-tools/cs6035-tools.github.io/Projects/DatabaseSecurity/TaskA.html 2/2

CS 6035

Projects / Database Security / Task B
TASK B: INFERENCE ATTACK- #1 (flag1 – 20 pts)
This happened with the first reports you are looking at that were provided to you for testing. You have been given internal reports for a single company. One report is a simple employee roster; one report lists out how long employees have been members of the organization, one report groups data on all employees together to provide the average salary for employees based upon the state that they live in, and the final report lists the average salary for employees based upon how long they have been a part of the company. These four reports do not violate the client’s controls on access to sensitive Human Resource data. However, as you do your analysis, you realize that there has been a hole opened in those controls to someone who puts the reports together and does a little analysis of the contents of the reports.
The audit reveals that, at minimum, at least one person’s salary is publicly available to all employees who can view these four reports. To complete Task B, you need to find a hole in the protection where you can definitively find the actual salary of employees. After finding the first employee, continue looking for a hole in the protection to find the next employee whose salary you can definitively find. Continue this for n employees in an iterative fashion. Each employee will have a hash associated with them. This hash is unique to your VM and was generated when you completed task A. Once you have identified which employees have the exposed salary, record the hash value displayed for that employee and their salary (2 decimal places) on the report in your JSON file for flag1. You will pass Task B if you have the correct hash values. Remember that submissions for the entire project are limited, so if you randomly try different hash codes, you will only hurt yourself later in the project.
For Task B – order matters (order of finding) for the flags (array[0] is the first employee found, array[1] is the second employee found, etc.)!
To earn your hash for flag1, you must perform the following actions.
• Hover over the Task B Menu, which will display four reports: Employee, Duration, Salary by State, and Salary by Duration.

• Click on the report you want to view (e.g., Employee Report). It will open in a new tab.
• Review the data on all four reports. You are searching through the data to see if you can find at least one employee whose salary you can positively identify precisely.
• With one employee found and hence eliminated, is there another employee whose salary you know precisely? Repeat steps 2-4 until no further elimination is possible. Each employee found they should be added to the next array pointer in the flag (i.e., the employee found order – 1 => array1 for the second employee found).
Hints:
• You need to find a place where data is isolated to a single person. Look at the four reports to see if anything makes the data unique. You might need to combine multiple reports to do this.
• Once you isolate an employee, continue to see if you can isolate another employee. Rinse and repeat until you can no longer isolate an employee.
• If you know it’s elimination, and employee n-1 leads to n, logic should tell you that to find n, n-1 would have something in common with n.
• All Inference flags:
• Only look at the data relevant to the task *Don’t get tied up on data that provides no value to the task.
• If you use an external application to troubleshoot the data and try to sort, make sure the entire dataset is sorted
• All Array flags:
• You will receive credit for each element in the array that is correct
• For flag arrays with more than the hash (salary/cpt), there is no partial credit for the array 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 6 elements in the array, and you have only 2, you will not be penalized for the missing 4)
• You will be penalized if you overfill the expected array (i.e., if there are 6 elements in the array, and you have 7, you will be penalized for the extra 1)
• You are reporting a salary which is US currency and hence, 2 decimal places
Include your flag1 array consisting of hashes_salaries into the JSON file, and now, onto Task C!
See Submission Details for more information.

CS 6035

Projects / Database Security / Task C
TASK C: INFERENCE ATTACK- #2 (flag2 – 25 pts)
Now that you have seen how an inference attack can compromise data (data made available inadvertently) in a single company, we consider how inference attacks can compromise data by combining unrelated data sets.
For this attack, four completely unrelated data sources should be considered. All four are internetfacing and, therefore, available to anyone with internet access. The first report is a sample report produced for a local hospital concerning types of procedures done within the last few years. The second report is a voter registration database with certain well-established fields (you could go to your local board of elections and get a report like this on all registered voters in your district, most likely). The third report is a partially de-identified report for an insurance company for a sample data set built by the same developers we have been dealing with for public use. “Partially de-identified” means that though some attempts were made to remove an obvious link to actual patients, there is still some data left on the report that might link back to a real person if looked at carefully. We also have provided a helpful list of medical codes to link to the hospital report.
For Task C – order does not matter (order of finding) for the flags!
To earn your hash for the flag, you must perform the following actions.
• Hover over the Task C Menu, which will display four sites: Medical History, Voter Registration, Insurance Claims, and Medical Codes.

• Click on the site you want to view (i.e., Medical History). The site will open in a new tab.
• You must carefully examine all four sources to determine where a patient’s history was compromised. You will determine how to do this.
• Once you have identified the exposed patient, look up the hash code (ID Column) on the voter registration report for the exposed patient and record it. NOTE: the hashes are specific to you and your GTID as provided in Task A (we will use that ID when we grade in Gradescope). You must append a _ followed by the voter’s CPT (procedure *NOT diagnosis) code to this hash.
Hints:
• As with the prior task, feel free to make liberal use of copying data from the VM into a spreadsheet or other similar program on your host, and feel free to do whatever analysis you need to do to figure out the record you seek. As in Task B, this is not required to complete this task but it will be helpful.
• Unlike Task B, where the data can be more easily narrowed to one record, you will need to narrow it to multiple records and then use logic to narrow it to one. Unlike Task B, where the order matters to narrow the data, it does not matter here as it is logic-based instead!
• 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.
• There is no partial credit for the correct hash or for the correct procedure (to receive credit, the hash_procedure has to be correct in its entirety)
Include your flag2 array consisting of hashes_cptcodes into the JSON file, and now, onto Task D!
See Submission Details for more information.

CS 6035

Projects / Database Security / Task D
TASK D: SQL INJECTION – #1 (flag3 – 5 pts / flag4 – 10 pts / flag5 – 10 pts)
*NOTE – Task D has 3 flags (flag3, flag4, flag5)
To get into the basics of SQL injection, you can start by looking up online “SQL Injection Cheat Sheet,” which is a helpful introduction to the topic. But what you will need to do to accomplish this task is figure out how to write some basic SQL code (the complexity won’t be in the SQL code but in the bypass of the SQL Injection security) that can be placed into the input field of a form and passed to the website in such a way that it is a valid SQL statement that does things that the original developer did not intend. This can involve bypassing security, accessing unauthorized information, or, in the worst case, making unauthorized changes to the website’s data. Once the hole exists, your power to exploit it can be immense as long as you can guess the structure of the underlying database or map it out.
For Task D, you have a website where your user ID is your GTID, as you entered on Task A. The site wants to upgrade its old Legacy Login page. The legacy page’s main security is checked to see if there is a direct match to a user by the username (your GTID). You do not know and will not receive the password to enter the system. For flag3, the developers are asking you to determine the difficulty in bypassing the existing security, knowing that a SQL Injection attack is possible. The developers want to attempt to mitigate the risk of such an attack; however, they are wondering if they should be using just client-side data sanitization or both client and server-side data sanitization, with the same server-side and client-side data sanitization. For flag4, you will attempt to bypass the security using client-side data sanitization. For flag5, you will attempt to bypass the security using client and server-side data sanitization. The client provided a snippet of the code they used on the legacy login page. You can use this code to try to figure out how to perform the SQL injection. When you succeed, you will log into 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 flags3-5 keyed to your GT ID will be displayed. All three hashes for Task D will be different.
To earn your hash for flags3-5, you must perform the following actions.
1 Hover over the Task D Menu, which will display three links: Legacy Login, New Login – Client, New Login – Client/Server.

1 Click on a link. The page will open in a new tab.
• For flag3 (Legacy Login) – this is a straight SQL Injection on the password field.
• For flag4 (New Login – Client) – this is a client-side data sanitization SQL Injection on the password field.
• For flag5 (New Login – Client/Server) – this is both a client and server-side data sanitization (2x) SQL Injection of the password field.
2 Enter your GTID in the username input field and any additional characters in the password input field you determine will cause the injection. Then, press the login button to submit.
Where do I find my GTID?
• Please see the Required Reading page for instructions to retrieve your GTID.
3 If your information is correct, you will log into the system and receive your hash.
Hints:
• For flag4 and flag5, you should use the browser’s Developer Tools to access and debug the client-side code.
• For flag5, remember that there will be server-side data sanitization that you cannot access, but know the logic will duplicate the client-side data sanitization.
• All flags with Sanitization:
• 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 the client and server-side sanitization is 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 charset #- (if not HTTP encoded) into a blank char.
• All Injection flags:
• Sometimes, not equal can be just as good as equal if appropriately used. MSSQL has two ways to state not equal (one where you can beat the sanitization and one where you cannot).
• Single quote (‘) does not get filtered and is needed for most injections
• Just because you think you are writing a SQL injection doesn’t mean the database won’t read it as a regular string.
• Remember that the entire statement is going into a string variable.
• While the exception causing the databreach is returned for troubleshooting, this exception is based on the expectation the input is a string, not an injection!
• Null works great for an SQLi when you don’t know the schema and when the developers aren’t expecting it. The developers are looking for it!
• There is a difference between ending a statement to next begin a new statement and escaping out of the statement.
• All Injection Login flags
• Here 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):

Include your flags3-5 hashes into the JSON file, and now, onto Task E!
See Submission Details for more information.

Task E | CS 6035
CS 6035

Projects / Database Security / Task E
TASK E: SQL INJECTION – #2 & INFERENCE ATTACK- #3 (flag6 – 7.5 pts)
*NOTE – Task E has 1 flag (flag6 is an array flag)
For Task E, you must complete two attacks (Inference and SQLi), which you should now be comfortable with.
For flag6, you have a list of grades for this class (all data is fictional *if by some chance your name is listed, it is 100% coincidence). This data is deidentified (meaning scrubbed) to prevent a viewer from limiting the data to an individual (such as Task B). However, there is a login where users can log in and obtain a roster list with more detailed data. Your task is to use Open-Source Intelligence (OSINT) to obtain the login account (user) and SQL Injection to bypass the password (no sanitization *discussed in flag4 and flag5). After successfully bypassing the login, you will be prompted with a security question. Use OSINT techniques, often used by social engineers, to deduce the correct answer based on the provided context or hints. When you finally log in, use the extended roster data to find the students who have received an F in the class and enter them into the flag6 array (order does not matter).
To earn your hash for flags6, you must perform the following actions.
• Click on the Task E Menu.

• Enter the account you were able to obtain/deduce in the username input field and any additional characters in the password input field you determine will cause the injection. Then, press the login button to submit.
• If your information is correct, you will be prompted for a pseudo-multi-authentication prompt. You will again need to use OSINT techniques to obtain the prompted information relevantto the account.
https://github.gatech.edu/pages/cs6035-tools/cs6035-tools.github.io/Projects/DatabaseSecurity/TaskE.html 1/2 Task E | CS 6035
• You will be logged into the system if the multi-authentication data is correct. For flag6, you must gain some inference knowledge to obtain the students who received an F.
Hints:
• Specifically, with the OSINT aspect of this task, you should concentrate on information gathering (gathering information about a user from accessible data and using it to gain access to their accounts).
• There is no sanitization with the login form (password field). Haven’t you already done this?
Include your flags6 hashes into the JSON file, and now, onto Task F!
See Submission Details for more information.
https://github.gatech.edu/pages/cs6035-tools/cs6035-tools.github.io/Projects/DatabaseSecurity/TaskE.html 2/2

CS 6035

Projects / Database Security / Task F
TASK F: SQL INJECTION – #3 (flag7 – 7.5 pts / flag8 – 7.5 pts)
*NOTE – Task F has 2 flags (flag7, flag8)
You will be completing two flags for this task.
In this case, you will be looking at a search engine for a database of music albums for a music store. You have discovered a page called “schema,” which offers the user a view of the underlying metadata of the table used to populate the main report. By now, if you have been paying attention, the designers of these sites have followed a similar pattern for how their sites work, especially if you look at the hyperlinks that lead to the pages. You know, therefore, that there is probably a way to get admin access to that schema by leveraging that knowledge of how they use links. That means table definitions might be available to users who poke around and try to find things they should not.
You have learned that the Schema page uses a database user with the same name of Schema to access the database and that this user only has select/read access to the Music table, which provides access to view the tables’ schema. You will need to inject to provide the Schema user access to all tables, so when you find out how to display the database table schemas, more than the Music table will be displayed. The table name and schema you are trying to access are currently unknown. This is important in discovering how to craft your attack’s first part.
So, your task for flag 7 is to figure out how to modify access for the user (Schema) and obtain the hash of the new table with the “correct level” of rights/access. After you get additional information about tables in the database, you can use this data for flag 8, where you must learn how to leverage your discovered information to construct the SQL injection. If you successfully craft a SQL injection, you will find an actual database account appearing on the report that you can use to log into the system using the “Login” screen on task F. If you have the correct login information, you can only obtain it by SQL injection. You can enter the login information into that screen and access the management console. Of course, for our purposes, the “management console” is just the hash you need to get credit for the attack. You will need to enter the newly visible hash in your JSON file.
Both attacks will need to be accomplished using the Report page!
• Hover over the Task F Menu, which will display four links: Schema, Report, and Login.

• Click on the link you want to view (i.e., Schema). The link will open in a new tab.
• You can use the Schema link to see the schema for the table used to build the report.
• The report search is a simple “SELECT…..FROM….WHERE” query using the schema you can see on the initial schema screen. Once you know how to execute the injection, type your attack into the search field and click “Search.”
To earn your hash for flag7, you must perform the following actions.
• Is there a role you can add your user to that would provide access to reading the schema of the table? Once you have figured out how to execute the injection to provide access, type your attack into the search field on the Report page and click “Search.”
• On the Schema page, consider how you might find more information than in this table based on how the links have been designed. You might need admin access to see information.
• When you see the new table (not Music), the Hash in the table Schema header is your flag7 hash.
Record the hash into your JSON file and submit it to Gradescope.
To earn your hash for flag8, you must perform the following actions.
• Once you can obtain the other table’s schema, you can again use the search input to try to return rows from other tables in the database other than what is intended. Once you have figured out how to execute the injection, type your attack into the search field on the Report page and click “Search.”
• Once you have done the injection correctly, a username and a password will appear in the report data. Use these values to log into the management console on the “Login” screen. When you have the correct login, you will get into the system, and your hash will be displayed. Record the hash into your JSON file as flag8 and submit it to Gradescope.
Hints:
• You will need information hidden in the website to craft your injection. The information is hidden in webpages you cannot reach via any links anywhere in our project. You notice that the pages and URLs for the different clients look very similar, with similar structure and conventions. Can a slight manipulation of the URL lead to providing more information?
• You have heard that this company has realized that client-side scripting is easily bypassable, offers a server-side scripting template, and has started removing the client-side sanitization login. However, you are EXTREMELY confident that they are still using the same server-side sanitization logic/code they have previously used for other sites.
• The login bypass logic you used from Task D/E will not work on Task F, although the choice to attempt is up to you. SQL injection on the login screen for this exercise is explicitly disabled.
• Flag7:
• There are two parts to flag7 – one is gaining access to the schema, and the other is displaying the schema.
• The goal is to sneak into the system without raising attention. You do not need elevated access (the administrators are looking for this); read access will suffice
• Is there a role you can add your database user (Schema) that would provide access to reading the table schema?
• SQL Server has built-in Stored Procedures you could use to solve this task
• Improperly messing with access can cause issues. You will likely need to re-import your VM Image if you cannot access pages.
• Flag8:
• Can be completed without completing flag7 (providing access to the database user Schema).
• How can you add contents from another table to an existing SQL query to return one data set in a report?
• Think about the results received from your successful injection in flag7. What is that telling you if you receive a similar output in flag8?
Include your flag7 hash into the JSON file and submit it to Gradescope!
See Submission Details for more information.

CS 6035

Projects / Database Security / Task G
TASK G: SQL INJECTION – #4 (flag9 – 7.5 pts / flag10 – 7.5 pts)
*NOTE – Task G has 2 flags (flag9, flag10)
You will be completing two flags for this task with flag10 reliant on the completion of flag9 (or you can choose to use your GTID for the username and password but not receive a hash for flag9).
Both flags will use the same banking website. This is a standard type of banking site where you log in and see the history of a particular account tied to the login. Your client maintains another site and wants you to verify the protection they have set up. Your job is to show them that the protection is not adequate.
*Part one (flag9): logging into a website as a user without a username
You must complete this first part to get full credit for the project (i.e., 100/100). Your task for this portion is to log into the website without knowing any actual accounts. However, you must log in as a valid user. You, therefore, must craft an injection that is smart enough to find a valid user from the database without having any actual access to the database (see the hints section below for assistance in knowing what the database structure looks like). You must use the skills you gained in Task D with more sophisticated SQL knowledge. There are multiple injections for the username field that will work; your goal is just to ensure that the username is populated from an actual row within the database using your injection. You will need two different injections for the two fields (username and password) to log in. Once you have logged in successfully, you will see a valid username where the username text box was and a hash just below the username. This hash is your answer for flag9.

*Part two (flag10): altering transaction history
The developers want to verify the strength of their protection for the account’s transaction history. Once you complete flag9 and log in, or choose to bypass flag8 (no hash will be provided for flag8) using your GTID for the username and password, you will see a list of 10 transactions over time. You will notice that one of the 10 transactions has an overdraft charge of $35. You will also notice a balance to the account when you bring it up to $1746.52. You need to do two things to test the transaction history and get your flag. You must get the balance to $2000 with 10 transactions and no overdraft fee. To do this, you need to do a transfer of money into the account, and you will need to delete one of the transactions (for this test, the developers want to see if you can remove an unwanted overdraft amount, so you’ll need to remove that specific transaction while adding enough money to make the balance $2000).

Unlike prior injections, attacking the text boxes cannot do this task. Instead, You must alter the form output within the web browser. This will require you to use the Chrome Developer Tools within the browser. This will probably require you to research how to use these tools to make breakpoints and alter the contents of data as found in a form. When you click the Transfer button, you must know how to pause submitting the form to complete the attack. The routing number and account number fields do not matter; feel free to enter any values. You will not be able to alter the account number. You should enter the amount you need to get to $2000 as if the overdraft fee was not there (if you do the injection correctly, that transaction will completely disappear and, therefore, not be used to calculate the balance). If you have the breakpoint set correctly, you can stop form execution and use the Developer Tools to alter the data within the form to include your SQL injection.

Be warned—banking sites use auditing to track history. Simply deleting the transaction will not be enough; you must remove it from the audit trail. Make sure to look at the hints for how to find this information. The developers will not credit you for your efforts unless you can remove all traces of the deleted transaction.
To start Task G, click on the Task G Menu from the home page. The page will open in a new tab.

Flag9: The text boxes you must inject into are on the top right.
Flag10: You cannot directly add your injection anywhere in the UI without using Web Dev Tools. Do not add it to the Routing or Account numbers, as this will not help you.
Hints:
• The developers have not set up any sanitization to change what you enter into the field, just like it was for your legacy task in Task D (flag3). You don’t need to worry about changing your injections to bypass a filter.
• Flag9:
• Task G uses a query similar to Task D to log in. If you need help knowing how to query database information to find a username, that query will give you valid metadata information. This is sample code and not the exact code that is used. Do not try to bypass the code (i.e., try to stop aspects of the code from running). Instead, understand what the code is looking for regarding the user and what is needed to have the injection work with the code as is.
• While it is possible to try to “brute force” a user to return, this flag is looking for one specific user, and it will cause you to burn submissions. Again, understand the code breakdown from the hint above!
• If you were genuinely hacking a system and did not know how many users there were in the system, you would assume there would be at least … Starting there probably is a good idea
• What you use for the password (remember there is no sanitization – this sounds like flag 3) will not work for the username.
• Using LIKE is not the best approach for this injection!
• Flag10:
• Once you find the page from the hint above, you should look closer. Can a slight manipulation of the URL lead to providing more information?
• Log out and back in if you make a mistake adding a transfer item. Logging out and back in will reset the transactions to the original amount. You must inject with a single click of the Transfer button, not multiple clicks.
• Pay close attention to the schema.
• There are two main ways to insert a row into a table via SQL (ex. below). You will need to figure out which is used to craft your injection!
• INSERT INTO TABLE (COL1, COL2, …, COLn) VALUES(DATA1, DATA2, …, DATAn)
• INSERT INTO TABLE (COL1, COL2, …, COLn) SELECT DATA1, DATA2, …, DATAn FROM …
• You will not be able to apply your injection using the form; maybe try Web Developer Tools
• This flag builds JSON to deliver the payload, and the start of the payload is an insert statement (but which syntax? – see hint above)
• The bank has processes in place that does not allow multiple transactions in a short period of time!
Include your flag9 and flag10 hash into the JSON file and submit it to Gradescope!
See Submission Details for more information.

CS 6035

Projects / Database Security / Task H
TASK H: Defend Against Inference and SQL Injection Attacks (flag11 – 15 pts)
NOTE – Task H is an array flag (Inference – Elements 1-3 / SQLi – Element 4)
In this task, you will pivot from an attacker to a defender. Instead of trying to find vulnerabilities to exploit, you will write SQL to prevent the attacks you have performed in Tasks 1-5. NOTE—All the validations will show yellow (not run) for their initial run. To limit the time required to run through the validation(s), each test must be run separately by clicking the Run test button next to the test you want to validate.
• Hover over the Task HMenu, which will display two links: Test Pages and Run Validation Checks.

• Click on the link you want to view (i.e., Test Pages). The link will open in a new tab.

• The Test Pages will provide a drop-down with options:
• Employee Report Search can be used to test the SQLi defense against a UNION attack.
• Avg Salary By Title Report can be used to validate your view.
• Avg Salary By Dept Report can be used to validate your view.
• Login can be used to test the SSQLi defense against a LOGIN attack.
To earn your hashes for flag11, you must perform the following actions.
1) Inference Defend (Elements 1-3 of flag11 array -> 5 pts)
• The reports will use the three Views provided to you. You will not have access to alter the EmployeeReport View. You should alter the views DocumentEthnicityDurationReport and TitleGenderAgeReport based on the instructions below.
• You must make the data K anonymous with a value of K=2. While this is a minimal k-anonymity value, this is directly tied to the small value set size.
• There are checks in place to ensure that “no unique records” are met while also ensuring the integrity of the data is not lost any more than it needs to be to ensure the report still holds value.
• You will need to alter 2 Views in Azure Data Studio. 1) TitleGenderAgeReport — This will be a report on the average salary and bonus based on Title, Gender, and Age. * Use Azure Data Studio to Alter View (TitleGenderAgeReport) to report and test SQL. * Only report data that provides value to the report * Columns in the view should follow the table order * There is no need for a default sort * Create buckets for age (<36, 36-50, 51-65, >65), and the data return should be what those buckets are (e.g., <36 or 51-65) * Title is more relevant than Gender * Use Redact, unless for Title, then use Other * If necessary to deidentify LASTLY by duration, use Omit * The final report salary should be the average to 2 decimal places and contain a $, but should not contain a , * The final report percentage should be average, rounded to 2 decimal places, and contain a trailing %. Bonus isn’t currency like salary; trailing zeros should be removed. 2) DepartmentEthnicityDurationReport – This will be a report of average salary and bonus based on
Department, Ethnicity, and Duration * Use Azure Data Studio to Alter View
(DepartmentEthnicityDurationReport) for report and test SQL * Only report data that provides value to the report * Columns in view should follow table order (Duration is based on Hired) * There is no need for a default sort * Create buckets for duration (<6, 6-10, 11-20, >20), and the data return should be what those buckets are (e.g., <6 or 11-20); *duration should be calculated on days based on today and years should be floor, not ceiling Duration should be calculated off of a 365 day year * Department is more relevant than Ethnicity * Use Redact, unless for Department, then use Other * If necessary to deidentify LASTLY by duration, use Omit * The final report salary should be average to 2 decimal places and contain a $, but not a ,. Bonus isn’t currency like salary; trailing zeros should be removed. 2) SQLi Defend (Element 5 of flag11 array -> 2.5 pts)
• You will need to alter 1 Stored Procedure in Azure Data Studio – uspSQLReplace
• DO NOT CHANGE THE PARAMS PASSED TO THE STORED PROCEDURE / YOU MUST RETURN @filter VARIABLE
• You need to allow valid users to log in while stopping an invalid injection code
• You need to allow valid data to be queried while stopping an invalid union injection code
• You need to use the sanitization code you have access to Do not stray from this logic regarding the filtering
Hints:
• Duration is a calculated field that will need to be returned (which is why it is added to the View Template and passed back as a blank string)
• When using Group/Partition By, it is crucial to use the data you need and not necessarily the column directly
• Do not hardcode data (other than where necessary Redact/Other/Omit)
• Using subselects / common table expressions could be helpful in the inference defense
• How can Dynamic SQL be beneficial for enhanced sanitization – https://learn.microsoft.com/enus/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sqlserver-ver16
• Stored Procedures can return variables and/or results via SELECT statements. This is not what you want to do for this task (you will get the Error Page). The @filter variable/parameter is automatically returned as part of the procedure declaration. Any value set in the variable at the end of the procedure script will be returned.
• Column Data Type is important to take into account • Using dynamic SQL could be helpful in the SQLi defense Azure Data Studio:
• Follow the instructions in Background and Setup
• Expand your new connection, and expand the Student database, expand Tables, Views, and
Programmability/Stored Procedures to see relevant objects. You can alter the object on the Views (not EmployeeReport) and Stored Procedures by right-clicking and choosing Script As Alter.

• A new tab will open, allowing you to alter the object via T-SQL. To commit your changes, click on the Run icon.

Reset Code for Views/Stored Procedure:
• TitleGenderAgeReport
ALTER VIEW [dbo].[TitleGenderAgeReport] AS
SELECT * FROM CompanyEmployees;
GO
• DepartmentEthnicityDurationReport
ALTER VIEW [dbo].[DepartmentEthnicityDurationReport] AS
SELECT *, ” AS Duration FROM CompanyEmployees;
GO
• uspSQLReplace
ALTER PROCEDURE [dbo].[uspSQLReplace](@tblName varchar(64), @colName varchar(64), @colValue varchar(10 AS BEGIN declare @sql nvarchar(max); declare @param nvarchar(64); declare @found int = 0;
select @filter = @colValue;
/*Student Code Should Go Here*/
END;
GO

Include your flag11 hash into the JSON file and submit it to Gradescope!
See Submission Details for more information.