Background Information

The project aimed to expose us to the world of computer security through research and hands-on experimentation. As we were attached to the purple team of the Enterprise IT programme centre who were in-charge of the cybersecurity needs of the EIT PC, we were guided through the use of Webgoat and the research of our chosen web application vulnerability.

What we did

We were tasked with trying out the various challenges found on Webgoat. Through the lessons we learnt from Webgoat, we were then supposed to research on one of the OWASP Top 10 vulnerabilities and present it to the director, our mentors and our fellow interns. Webgoat is a web application intentionally designed with various vulnerabilities in order for us to learn how we can exploit these vulnerabilities and how we can mitigate its impacts. The OWASP Top 10 is a list of the most pressing web application vulnerabilities published by OWASP, the Open Web Application Security Project.

Record of Activities

December Holidays Readings

During the December holidays, we were tasked by our mentors to read up more about the following topics related to computer security so that we will have a basic foundation regarding web applications:

• HTML, JavaScript and SQL
• Chapters 0 through 3 of the OWASP Testing Guide 4.0

Throughout the attachment, we spent a considerable amount of time trying to complete the challenges found on Webgoat. Some of the challenges were relatively easier while some of them required a lot of effort in order to resolve. There were a handful of questions that were too challenging for us and we resorted to finding the solutions online. However, when we did this, we made sure that, after checking to make sure that the solution works, we thoroughly read through and understood the solutions before moving on.

There were also many terms that appeared in the Webgoat lessons that were new to us. Hence, we had to search up the meaning of these terms and the part that they play in web applications. These searches typically brought up other words that we had not seen before and we searched those new words online as well in order to ensure that we get a full picture of web application security.

To DSTA

The deliverable that we had to present to DSTA was an individual presentation to the director, mentors, other EIT staff as well as other interns on the 23^rd of January. For our presentation, we had to choose one of the Top 10 vulnerabilities to research and present in a 5 minutes long presentation. Our presentations contained a self-introduction to tell the director and staff more about who we are, our interests as well as our motivation for choosing this project. We then dived into the impacts of our chosen vulnerabilities before showing what we learnt about the vulnerability, including how it can be exploited as well as how it can be defended. At the end, all the interns also presented more about our experiences at DSTA and what we took away from this 3 weeks long attachment.

To TJC

We had prepared a report containing all the information that we managed to find regarding our chosen vulnerabilities that contains information that did not make it into the presentation to the directors due to the limited time. We also prepared a daily log containing our day-to-day activities as well as a reflection containing the hard and soft skills as well as lesson that we had learnt through this attachment to DSTA.

Slides

Final Report

Introduction

What is OWASP?

The Open Web Application Security Project, abbreviated as OWASP, is a non-profit foundation aimed at improving the security of web applications through collaborative, community-led open-source project[1]. The OWASP Top 10 project aimed to raise awareness amongst developers and companies but it has since evolved into the de facto application standard. It is released every 3 to 4 years and has collaborated with the community to highlight the biggest vulnerabilities present in contemporary web applications[2].

SQL Injection

SQL Injection has been at the top of the OWASP top 10 for the past three releases[3] which highlights how prevalent and how much of a risk it poses to web security. SQL injection makes use of input fields in web applications. These types of input fields typically query against a database in order to retrieve relevant information to display for the user. However, if they are not properly implemented, the fields may allow the user to execute malicious code by entering specific queries[4].

Impacts of SQL Injection

SQL Injection has the potential to compromise what’s known as the CIA triad. SQL Injection can compromise availability by simply dropping the databases required or by preventing users from accessing them via some other means. SQL Injection can compromise integrity by removing or adding certain rows or columns in a table that could cause users to misunderstand the dataset or be provided with inaccurate information. Lastly, SQL Injection can compromise confidentiality by disclosing information to unauthorised people who should not have that information[5].

SQL injection has high exploitability as almost anything and any web application that contains any type of user input has could potentially have vulnerabilities that can be exploited using SQL injection. It is also very prevalent and extremely easy for attackers to exploit as many web applications, especially those containing legacy code, contains input fields that are vulnerable to SQL injection and many services, such as scanners and fuzzers[6] are able to automatically detect these vulnerabilities if they are present on web apps. Lastly, they pose significant technical risk due to its ability to compromise all 3 aspects of the CIA triad and potentially reveal information that allow attackers to gain full admin access and rights[7].

Real-world Examples

Now, you might be wondering what the relevance of SQL injection might be in the real world, and here are a few examples of how SQL injection could have been used maliciously in the past and also some SQL injection vulnerabilities disclosed by whitehat hackers.

The first example here was disclosed on HackerOne, a website dedicated to connecting businesses with penetration testers and cybersecurity experts in order to discover and responsibly disclose potential vulnerabilities for at-risk companies to fix or mitigate[8]. The first example here was disclosed by a user by the name of moskowsky and it was disclosed on the 28th of July, 2018. The vulnerability, as summarised by Valve, the vulnerability affected the Steam gaming platform and could be used to read certain data to read certain SQL data from a single backing database due to an unvalidated parameter[9]. The vulnerability was given a severity rating of Critical with a numerical rating of 9.9 out of 10 on the Common Vulnerability Scoring Scheme which allows companies to appropriate gauge and triage the impact of the vulnerabilities[10]. Valve proceeded to pay a $20000 bug bounty and an additional $5000 bonus to moskowsky.

Additionally, in the leak of the Panama Papers[11], it is suspected that the people involved in the leakages exploited a vulnerability related to an SQL injection in order to gain access to the database. Mossack Fonseca, the law firm involved in the leaks, was a Panama-based law firm that helped people transfer their wealth to tax havens such as Panama to avoid taxation. According to a security expert, they had shown “astonishing” disregard for security by not updating their services in a timely manner. As of 2016, their Outlook Web Access login was last updated in 2009 and their client login portal was last updated in 2013. It was the outdated version of Drupal, an open-source website content management system, that had 25 vulnerabilities, one of which was a high-risk SQL injection vulnerability that allows people to execute arbitrary command within their database[12].

How does it work?

Using the following example vulnerable code provided regarding injection[13],

SELECT name FROM users WHERE 

username = 'username' 

AND password = 'password' 

The vulnerability exists because of the section highlighted in red. It takes unescaped and unvalidated user input, in this case, presumably from a text input asking for the users’ username and password and proceeds to run the following query against the database. The database table could potentially look similar to the following database:

In a perfect scenario, what will happen is that the user will enter their actual username and password, in this case John_doe123 and thisissecure:

SELECT name FROM users WHERE 

username = 'John_doe123'

AND password = 'thisissecure' 

This will only return the details of John Doe’s account as the parts highlighted will only both return true for John Doe’s own account as only then will the username and password associated with the account in the database equal to those in the data entered, thus returning only John Doe’s name, John Doe.

However, if someone was aware that this was a vulnerable field and they were looking to maliciously exploit the entry field, they could gain entry into the administrator account through the following input which would be read by the programme as actual code instead of just user input, allowing the attacker to execute any code they’d want:

Username: administrator'--

Password: (anything)

The will result in the following query being executed, if there are no validations in place.

SELECT name FROM users WHERE 

username = 'administrator'--'

AND password = '(anything)' 

This will automatically log the attacker into the administrator’s account. This is because the --[14] character sequence is recognised in SQL to designate that anything after it is a comment and should not be executed. Hence, the query above will only check that there is an account with the username administrator and log the attacker in without double-checking the password as the password check is commented out.

The attacker can also enter the following input:

Username: administrator'; SELECT * FROM users;--

Password: (anything)

This will effectively result in the entire table being printed as the * character tells the programme that you want to return everything from the table that contains all the user data. This is because the ; represents a new query, starting the query of SELECT * FROM users;[15].

Even without an entry field, SQL injection can still be carried by modifying the URL of a website. For example, a website’s URL might look something like this:

http://w34ksite.com/products.php?category=1

The highlighted part is a SQL query that has been disguised in the URL of the website. When it is converted into an SQL query, it will look something like this[16]:

SELECT * FROM products WHERE category=1;

Hence, the attacker can then modify the URL to the following or something similar in order to be able to extract information, in this case all the emails, from the website in question:

http://w34ksite.com/products.php?category=1 UNION ALL SELECT *, NULL, NULL FROM email

This indicates that the URL of websites are also potentially vulnerable to SQL injection and that it is not only input fields that have to be guarded against SQL injection

Types of SQL Injection

Error-based SQL Injection

Error-based SQL Injections rely on there being an error that is thrown by the programme and consequently displayed to the user to tell the attacker more about the structure of the programme, including the functions involved and the programme’s data structure[17]. Through trial and error, it is possible for the attacker to find out critical information such as the name of the database and tables and also the number and names of the columns[18]. For example, the attacker can add the following to the end of his input[19]:

' ORDER BY 1

' ORDER BY 2

' ORDER BY 3

The ORDER BY function is intended to sort a table according to a certain column’s data, however, by enumerating through the different columns, we can find out how many columns there are in the table, which is important when executing a Union-based SQL Injection. This is because an error of the following will be thrown if we exceed the number of columns in the original select query, allowing us to know the number of columns present:

The ORDER BY position number 3 is out of range of the number of items in the select list.

Union-based SQL Injection

After obtaining adequate information about the database and tables, such as the number and data types of the columns, we can then find out more information by exploiting a union-based SQL injection. This exploits the way that the UNION operator works in SQL. The function of the UNION operator is to join the results of two separate SELECT operations into a single table. However, the UNION operator only works when both tables have the same number of columns, which is why it is important to figure out the number of columns in a table using Error-based SQL Injections or through other means[20].

For example, the following code might have been used to sort though and only return things from a certain category:

SELECT name FROM products WHERE category='toys'; 

However, this field can be maliciously manipulated to return data that should be kept confidential and not publicly available, even if the data was kept on another table. By entering an input similar to the following, the attacker can retrieve information from another table in the same database[21], such as users, along with sensitive data:

INPUT: toys' AND 1=2 UNION SELECT CONCAT(username, “~”, password) AS login FROM members;--

SELECT name FROM products WHERE category=1 AND 1=2 UNION SELECT CONCAT(username, “~”, password) AS login FROM members;--

The first highlighted portion will always return a value of false as 1=2 will always return false which makes the entire WHERE conditional false due to the AND operator. This ensures that any data that is returned will always be the data the attacker is aiming to extract which comes from the back half of the UNION query. The CONCAT()[22] operator is important as the UNION operator only works when the first SELECT operation and the second SELECT operation returns the same number of columns, as such, we have to join the username and password into one column which we give the alias of login.This will return the table of information containing all of the users’ username and password which can be maliciously used[23].

Boolean-based SQL Injection

Boolean-based SQL Injection queries the database using queries that are designed to return either true or false boolean values. This approach is extremely time-consuming as the attacker has to manually enumerate through all the possible characters for the entire length of the entry that they are trying to find out.

For example, in trying to find out the password of an account, the attacker can try to insert something similar to the following query into a login field where 'tom' is a registered account name:

USERNAME: tom' AND substring(password,1,1)='t

The complete query will look similar to this:

SELECT * FROM user WHERE username='tom' AND substring(password,1,1)='t';

We know that the first part will return true as 'tom' is indeed a valid account name. However, the entire statement will only return true when the second part, which is highlighted, returns true as well. It will only return true when the first letter of tom’s password is t as the substring() function takes in 3 parameters. The first one is the string to cut, which in this case, is the password of tom’s account. The next integer represents the location of the first character included in the cut and the last integer represents the length of the cut[24]. The attacker will then continue to try out all the possible characters for the second character by running:

USERNAME: tom' AND substring(password,1,2)='tx

The attacker will continue to do so until they have guessed the entire length of the password through fuzzing the possible characters. This method however, only works when the web application gives a different response depending on if the query returns true or false[25].

Time-based[26]

By using the IF() operator in SQL injections, the attacker can implement a delay if a certain boolean conditional constructed returns true or false. This is especially useful if the web application is indeed vulnerable to SQL injections but do not show a visible difference in response if the query returns true or false. Additionally, such attacks will usually also not be logged by the web application, allowing the attacker to carry out their attack discretely. By analysing the differences in the time taken for a response to the query to be generated, the attacker can tell if the conditional they added into the query returned true or false.

For example, an attacker can input the following code to try to fuzz the password of the administrator’s account, with the highlighted section being the attacker’s input:

SELECT * FROM products WHERE id=1; IF SYSTEM_USER='sa' WAIT FOR DELAY '00:00:15';--

In this case, if SYSTEM_USER does indeed equal to 'sa', there would be a 15 seconds delay in the response provided by the web application as the delay will also execute when the first conditional evaluates to true. This process will require the attacker to slowly enumerate through the various possible characters and also keenly monitor the delays in the responses provided by the web application[27].

How to mitigate?

Most of the ways of mitigating the impacts of SQL injection involves explicitly differentiating and indicating which segments of the query are meant to be executed as part of the SQL query and which parts of the query are meant to be understood and interpreted to be user input. This mainly involves inserting user input into the SQL query as parameters or by escaping certain characters so that the programme understands that it is not meant to be read as part of the code.

Whitelist Input Validation

The simplest way to prevent SQL injection is to ensure that a value that does not correspond to a valid search term will not even be allowed to execute[28].

For example, in the case of searching through a catalog of items at a store, the following code can be used to ensure that only search terms that contain valid categories will be allowed through, otherwise, an error will be thrown, preventing any malicious injected code from being erroneously run by the programme (the example is done in JavaScript)[29]:

String columnName;

switch(PARAM):

  case "food"  : x.do();

                 break;

  case "drinks": y.do();

                 break;

  ...

  default      : Throw Error;

Hence, if anyone were to enter code for an SQL injection, it would not be recognised as a valid column name and therefore, it will not be run as the programme will run the default function which is to throw an error.

Prepared Statements

Prepared statements with parameterised queries forces the application developer to first write all the SQL query-related code explicitly before passing each of the users’ input into the query as parameters later on. This allows the application to very clearly and easily distinguish between what is meant to be a user-input string and what is meant to be part of the SQL query[30].

The following code, written in JavaScript, makes it clear to the programme that "customerName" should be considered as a user-input string and not to be part of the query to be executed:

String custname = request.getParameter("customerName"); 

String query = "SELECT account_balance FROM user_data WHERE user_name = ? ";

PreparedStatement pstmt = connection.prepareStatement( query );

pstmt.setString( 1, custname); 

ResultSet results = pstmt.executeQuery( );

The first line simply obtains the user input from a field known as "customerName". The second line crafts the SQL query, leaving the ? to indicate a parameter where the user input should be substituted into to form a complete query. The third line creates a PreparedStatement using the query defined in line 2. The fourth line then replaces the first parameter, the ?, with the user-input. The last line stores the value returned by the query as a ResultSet[31].

Reflections

3 Content Knowledge or Skills Learnt

1. Through this programme, I learnt a lot more about SQL Injection and how SQL Injection can be carried out as well as how its impacts can be mitigated as this was the vulnerability that I chose to research on. SQL injection makes use of input fields in web applications. These types of input fields typically query against a database in order to retrieve relevant information to display for the user. However, if they are not properly implemented, the fields may allow the user to execute malicious code by entering specific queries. SQL Injection has the potential to compromise what’s known as the CIA triad. SQL Injection can compromise availability by simply dropping the databases required or by preventing users from accessing them via some other means. SQL Injection can compromise integrity by removing or adding certain rows or columns in a table that could cause users to misunderstand the dataset or be provided with inaccurate information. Lastly, SQL Injection can compromise confidentiality by disclosing information to unauthorised people who should not have that information.
2. Additionally, it really opened my eyes to how life as a developer, or more generically in IT fields, is like. I always thought that life as a developer is very monotonous and lonely as developers mostly spend their time alone with a computer trying to code their projects. In reality, the developers at EIT were all very sociable and lively people who always made time to talk to us and to joke around with one another. Also, the process of developing an application or web service does not only require one to code but it is actually a multi-step process that starts with considering what society needs and thinking about how to best meet that need. Failing to do this might result in an app not fulfilling its intended goal leading to a waste of time and effort as the developers only cared about creating an app but did not think about the users’ needs and experiences.
3. Lastly, as this is the first time I had the opportunity to conduct in-depth research into a field that I’m not familiar with, I learnt a lot more about how to systematically look into a new topic with a lot of foreign terminology. Whenever I read something that I did not understand, I would try to finish reading that article to the best of my ability and note down the question I had. Then, I would search those up separately and piece together what I understand in order to gain a more complete picture of the entire system. Sometimes, when I search up something new, it might lead to more terminology that I do not understand which means that I will have to repeat this process. However, it is important to me in this case to know where to stop as, understanding web applications and its vulnerabilities requires some knowledge of typical web architecture but I should not be carried away reading about all unique and rare vulnerabilities or dive so deep that it is no longer about web applications and more about machine code for example as it is unlikely that understanding it will help me gain a better understanding of the topic I am supposed to research about. Also, if it is something only tangentially related to the topic and I only want to roughly understand it without getting too technical, appending “ELI5” to a search term will pull up Reddit threads where people have simplified the topic to be easier to understand.

2 Interesting Aspects of your Learning

1. Solving the challenges on Webgoat provided us with hands-on learning that allowed us to better understand what we have read. It allowed us to transfer the theory into practice which allowed us to better grasp the finer details about how an exploit works or what makes a web application vulnerable. Trying and failing to exploit the vulnerability also gave us a better picture of how to defend against it as we know the difficulties we faced first hand. Thus, we would aim to increase such difficulties, thus stalling a malicious attackers progress.
2. During our self-reading, there were a handful of things which we had read and researched thoroughly and thought that we had fully understood them. However, when we had to explain them to each other as we were constantly asking each other questions to clarify our doubts, in the process of explaining those things, we realised that we actually had some doubts or misconceptions about them that we had not realised that we had. It was only through explaining to each other that we picked up on holes in our understanding and thus went online to find out more about a topic in order to clarify these doubts and clear our misconceptions.

1 Take Away for Life

1. In anything we do, it is always important to be passionate about it. Throughout our research, we ran into many obstacles and came across many really complicated concepts that might not even be thought until the second year in university. Additionally, we had to put in a lot of effort in order to thoroughly understand the concepts as most of the concepts built on top of one another, leaving out one concept might result in a shaky foundation, causing us to make many misconceptions along the way. Admittedly, the research did get tiring and repetitive at times. This is why it is important for us to be passionate and genuinely interested in the topic as, without that drive, we would have given up. However, as we innately wanted to find out more about computer security and we were genuinely curious about this topic, we persevered and managed to finish our task.