[SOLVED] CS 6035 Database Security

Learning Goals of this Project:You will learn about two different types of database vulnerabilities and how each attack works. Inference attacks are security attacks that attempt to extract sensitive information from a database by making educated guesses based on the available information. Hackers use this prevalent practice to data mine troves of information to connect the dots and extract sensitive information.In an SQL injection attack, an attacker injects malicious SQL code into a web application’s input fields to manipulate the database, gain unauthorized access to sensitive data, or execute harmful actions.In particular, we will cover these learning topics:The final deliverables:A single JSON formatted file will be submitted to Gradescope. This file should be named **project_dbsec.json. _**A template can be found in the /home/dbsec/Desktop _folder.See Submission Details for more information.Important Reference Material:Valuable Information – see Database Security PrerequisitesAll-Encompassing:SQL Test / SQL FiddleChrome DevTool DocumentationChrome Debug JavascriptHTML Table to CSV/Excel ConverterMicrosoft Excel (you will need to use your Student Account and allow pop-ups)Inference:Hylkema, Michael – A Survey of Database Inference Attack Prevention Methods Thought Co – How to Copy Code From a WebsiteSQL Injection:Microsoft – OperatorsOWASP – SQL InjectionHacking Loops – The Ultimate SQL Injection Cheat SheetPortSwigger – Using SQL Injection to Bypass AuthenticationPortSwigger – SQL Injection: The Union OperatorSQL Shack – SQL Union overview, usage and examplesSubmission:Gradescope (auto-graded) – see Submission DetailsVirtual Machine:Apple M-based systems (i.e., not Intel Chip)The M-based chipset is not supported. If you choose to use such a device, extensions will not be granted due to issues that arise.Intel/AMD x64 version https://www.virtualbox.org/wiki/Downloads (Version 7.0/latest is required)For those curious, the RDBMS used to run the database for this project is the Microsoft SQL Server developer edition, which runs on Linux.Note: The DBSec project is in the master VM. The link will be provided on the project release.TABLE OF CONTENTSFAQBackground and SetupTask 0Task 1Task 2Task 3Task 4Task 5SubmissionTroubleshootingTask 0 | CS 6035                                                                         https://github.gatech.edu/pages/cs6035-tools/cs6035-tools.github.io/Proj…CS 6035Projects / Database Security / Task 0*NOTE – Task 0 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 0 Menu from the home page. The page will open in a new tab.1 Enter your GTID and click SubmitWhere do I find my GTID?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 1-4!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 1!See Submission Details for more information.CS 6035Projects / Database Security / Task 1NOTE – Task 1 is an array flagYour first two attacks will involve what is called an inference attack. This is not an actual hack against a system; it often doesn’t involve hackers. Data security is about keeping data confidential on a need-to-know basis, and most data at most companies is secured by permissions that limit who can see data. These permissions can be at the level of the table (the full list of a particular type of data; for instance, the employee table contains the basic data about employees), the level of a row (a row, in this case, would correspond to the record for a single employee), or the level of a column (in this case a piece of information about an employee, like gender or position at work). However, maintaining these controls takes thought and care, which can often be inadvertently and carelessly circumvented. An inference attack usually involves access to standard reports available to many employees. Consider the sensitive area of salary. A company might consider a column to restrict access and make an employee roster report that anyone could view that does not list people’s salaries. However, a company may use other standard reports not carefully designed to protect those controls. By combining two or more reports, a rank-and-file employee can discover salary details they are not supposed to know by combining the information on the two reports and inferring the missing data, thus the name “inference attack.” You use multiple data sets to learn information you are not supposed to know.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 HR 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 allemployees who can run these four reports. To complete task 1, 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. Each employee will have a hash associated with them. This hash is unique to your VM and was generated when you completed task 0. Once you have identified which employees have the exposed salary, record the hash value displayed for that employee with their salary (2 decimal places) on the report in your JSON file for flag1. You will pass task 1 if you have the correct hash values. Remember that you only get 20 submissions for the entire project, so if you randomly try different hash codes, you will only hurt yourself later in the project.For Task 1 – order matters (order of finding) for the flags (array0 is the first employee found, array1 is the second employee found, etc.)!To earn your hash for flag1, you must perform the following actions.Hover over the Task 1 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 exactly.Once you have found an employee whose salary you know exactly, look at the left of their record on the employee report. In the ID column of the report, there will be a hash (ID) for them. Record this hash and enter it into your JSON file in array0. NOTE: the hashes are generated in connection with your GTID, so they will be unique to you and your account. You will need to append to this hash a _ followed by the employee’s salary (2 decimal places *you may need to do some math).With one employee found and hence eliminated, is there another employee whose salary you know exactly? 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 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 assist you in tracking down the hole that exposes salaries. If you use an external application to troubleshoot the data and try to sort, make sure the entire dataset is sortedThere is no partial credit for the correct hash or for the correct salary (to receive credit, the hash_salary has to be correct in its entirety)Include your flag1 array consisting of hashes_salaries into the JSON file, and now, onto Task 2!See Submission Details for more information.CS 6035Projects / Database Security / Task 2**NOTE – Task 2 is an array flagNow that you have seen how an inference attack can compromise data 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 internet-facing 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.The problem here comes from the incomplete deidentification of the insurance company’s claim report data. Because of this, it may be possible to combine these four sources to find a specific person’s medical history to know that on a specific date, a specific person had a specific procedure done. 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 just such a fashion). Your objective in this inference attack is to find specific persons with a procedure you can link by name and identity to a specific attack in the medical data using all four data sources. You may want to look up the work done on the patient using the codes in the medical history on the medical codes list, which might be helpful in the process. You will find a hash next to each person in the voter report. Select the correct hash as your response to this task by listing the hash in your JSON file for flag2. Just as in Task 1, start by isolating the data, then after that isolation, see if you can isolate down to individuals using the data available and logic. You will pass task 2 if you have the correct hash values. Remember that you only get 20 submissions for the entire project, so if you randomly try different hash codes, you will only hurt yourself later in the project.For Task 2 – order does not matter (order of finding) for the flags!To earn your hash for flags2, you must perform the following actions.Hover over the Task 2 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 0 (we will use that ID when we grade inGradescope). 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 1, this is not required to complete this task.Unlike Task 1, 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 1, 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.Sometimes, there is no perfect join of data, and logic needs to be incorporated,If you use an external application to troubleshoot the data and try to sort, make sure the entire dataset is sortedThere 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 3!CS 6035Projects / Database Security / Task 3/ flag4 – 5 pts / flag5 – 7.5 pts)*NOTE – Task 3 has 3 flags (flag3 is an array flag, flag4, flag5)The next two tasks involve the most common database attack – SQL injection. This attack is one of the most common information security attacks worldwide. Yet, it is also one of the easiest to mitigate (in other words, lazy or careless programming is what causes this attack to be possible). SQL injection is possible when inputs are not sanitized. That sounds complicated, but that means giving someone a form or web page to enter data; there are inputs for a user to type in; as a programmer, you are responsible for ensuring that those input fields are inspected somewhere in the process and any bad input is sanitized. SQL injection happens when the contents of the input fields without data sanitization are used in a text string to create a database query and sent to the database server in a form the developer did not intend.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 be as simple as bypassing security, collecting information you should not have, or, at worst, making actual changes to the data of the website that the website owners do not want. 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 3, there are two different types of attacks you need to completeFor flag3, 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) not to allow a viewer to limit the data to an individual (such as Task 1). However, there is a login where an end user can log in to get a roster list with more detailed data. Your task is to use social engineering to obtain the login account (user) and SQL Injection to bypass the password (no sanitization *discussed in flag4 and flag5). Upon a successful login bypass attack, you will be prompted for a four-digit PIN, which you will again need to use some social engineering to figure out. 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 flag3 array (order does not matter).For flag4 and flag5, you have a website where your user ID is your GTID, as you entered on Task 0. 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. 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 server-side and client-side data sanitization being the same. 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 determine 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 flags4-5 keyed to your GT ID will be displayed.To earn your hash for flags3-5, you must perform the following actions.Hover over the Task 3 Menu, which will display three links: Legacy Login, New Login—Option 1, and New Login—Option 2.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 – Option 1) – this is a client-side data sanitization SQL Injection on the password field.For flag5 (New Login – Option 2) – this is both a client and server-side data sanitization (2x) SQL Injection of the password field.Enter your Social Enfineering account (flag3) / GTID in the username input field (flags4-5) andany 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.If your information is correct, you will log into the system and receive your hash (flags4-5). For flag3, you must gain some inference knowledge to obtain the students who received an F.Hints:For flag3, pay attention to what the login page tells you and think about what someone may use for a 4-digit PIN If you receive the prompt for the PIN, your injection worked 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.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.Here is some login code that you were able to obtain that provides a direction of what occurs; however, you can not validate if it is the exact code used (both username and password have a character limit of 96):function login($username, $password) {$sql = “SELECT username, password, hash INTO #tmp_user FROM users WHERE username=’$username’        “SELECT username, password, hash, count(*) cnt FROM #tmp_user GROUP BY username, pass$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;}}}Include your flags3-5 hashes into the JSON file, and now, onto Task 4!See Submission Details for more information.CS 6035Projects / Database Security / Task 4/ flag7 – 7.5 pts)You will be completing two flags for this task with flag7 reliant on the completion of flag6. We strongly recommend you wait until you complete task 5 before trying flag7, as some knowledge from task 5 will be needed to complete the flag.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.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 collect 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 3 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 flag6.The developers want to verify the strength of their protection for the account’s transaction history. Once you complete flag6 and log in, 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’ll 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.When you have done this correctly, the proper hash will appear below the balance as the value associated with “Validation.” Unlike all other tasks, be warned – there will always be a hash there, and its value will change. You, therefore, can receive an incorrect hash. The hash will ONLY be correct if the balance is $2000 and the number of transactions is 10, with NO overdraft fees. Do not try to submit this to Gradescope unless you have no overdraft fee, 10 transactions, and a$2000 balance – whatever hash you may have outside of those conditions will not be correct.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 4, click on the Task 4 Menu from the home page. The page will open in a new tab.Flag6: The text boxes you must inject into are on the top right.Flag7: You cannot add your injection anywhere in the UI; you must do this using Web Dev Tools. Do not add it to the Routing or Account numbers, as this will not help you.Flag6 Task Hints:Task 4 uses a query similar to Task 3 to perform the login. If you need help knowing how to query database information to find a username, that query will give you valid metadata information.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 3 (flag3). You don’t need to worry about changing your injections to bypass a filter.Flag7 Hints: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.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 need to do task 5 before doing the flag7 because you will learn how to find this information on the website. Based on what you learned in Task 5 with how to find the schema, apply this knowledge to find the database schema for Task 4. Look closely at the URLs in Task 5 as you did to complete that task, and experiment with what you might need to get a valid URL without a hyperlink. Pay close attention to the schema. Unlike Task5, you do NOT need to worry about crafting an injection to enable rights to tables to see all the required 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 ToolsWeb Developer Tools can be concerned about your security and may block you from pasting data into a variable. If you encounter this, go to the section where warnings are displayed. You may find that Chrome is blocking you, and you must take action to unblock it. This will be necessary to complete your task, assuming you copy and paste the data into the variable (and you definitely will want to do it that way).Include your flag6 and flag7 hash into the JSON file and submit it to Gradescope!See Submission Details for more information.Disclaimer: You are responsible for the information on this website. The content is subject to change at any time.CS 6035Projects / Database Security / Task 5/ flag9 – 7.5 pts)*NOTE – Task 5 has 2 flags (flag8, flag9)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 database user Schema to access the database and that this user only has select/read access to the Music table. 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. You do not know the table name to which you are trying to add access or the database schema to which the table belongs. This is important in discovering how to craft your attack’s first part.So, your task is first to figure out how to modify access for the Schema user, then see if you can find any additional information about tables in the database. Finally, 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 5. 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!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 flag8, you must perform the following actions.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.”Return to the Schema page once you have completed the injection correctly (the report page’s results may be misleading).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 flag8 hash. Record the hash into your JSON file and submit it to Gradescope.To earn your hash for flag9, 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 flag9 and submit it to Gradescope.Hints:You notice that the pages and urls for the different clients look very similar, with similar structure and conventions, can a slight manipulation lead to provide more information? How can you add contents from another table to an existing SQL query to return one data set in a report?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.There is a difference between encoding, escaping, and sanitization. Understand the difference and the validity of using one or the other, remembering there is no client-side and only server-side sanitization.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!Think about the results received from your successful injection in flag8. What is that telling you if you receive a similar output in flag9?The login bypass logic you used from Task 3/4 will not work on Task 5, although the choice to attempt is up to you. We explicitly prevented SQL injection on the login screen for this exercise.Improperly messing with access can cause issues. You will likely need to re-import your VM Image if you cannot access pages.Include your flag8 hash into the JSON file and submit it to Gradescope!See Submission Details for more information.Disclaimer: You are responsible for the information on this website. The content is subject to change at any time.