Prompt

Conduct a security audit on the city's medical directory system.

Scope: This challenge is limited to HTTPS in scope, please do not attack any other ports on this server and do not brute force attack this web server.

Tutorial Video

Walk-Through

This challenge involves the exploitation of a SQL injection vulnerability. SQL is a language used to retrieve information from a SQL-compatible database. If you are not familiar with SQL, you can access this free lesson on Khan Academy. The website in this challenge unsafely trusts what the user inputs into the search bar, allowing an attacker to craft the SQL statement being executed by the server. This allows the attacker to expand the scope of the query and grab more data than the developers intended.

Questions 1 – 3 can be solved by conducting simple queries against the database. Querying each letter in the alphabet will yield all the users. However, a query for a blank space will also return the entire directory since each user in the database has a space in their name.

Questions 4 and 5 require the use of SQL injection. This is a process of trial and error. The difficulty with SQL injections is that the attacker does not control the entirety of the SQL statement because the input provided via the search bar is being added into a section of the SQL statement. This means that the SQL injection itself will not be a valid SQL statement - the combination of the existing SQL statement, plus the SQL injection will need to be a valid SQL statement. This means the SQL injection must be carefully crafted so that the final SQL statement is valid. In the case of this challenge, the SQL statement being executed is:

SELECT name, type FROM USERS WHERE name LIKE "% + query + %";

This SQL statement will obtain the name and the type of users that have a name that contains the query provided from the search box. The “%” characters work with the LIKE operator to indicate that there may be 0 or more characters on either side of the query. Without the “%” characters, only users whose names exactly match the query would be obtained.

You can obtain this information about the SQL statement by adding in random SQL characters into the search box. As soon as you provide input which makes the SQL statement invalid, you will get an error message that contains the necessary information to piece together the SQL statement.

Most common SQL vulnerabilities allow an attacker to add a second condition to the SQL statement, such as adding an OR condition followed by 1=1. This statement would return all rows in the SQL table regardless of whether the original condition was true or not as 1=1 will always be true and anything “OR”ed with a true value will always return true. It is possible to do something similar with this website; however, that would limit the attacker to only obtaining the name and type fields. The vulnerability on this website also makes it possible to run multiple SQL statements in one shot, which allows the attacker to create a second SQL statement to obtain all the fields.

To exploit this vulnerability, the original SQL statement must be terminated so that a second one can be started. By escaping from the quotations, it is possible to chain the original statement with a second statement that will yield more data than intended. The following input into the search bar will accomplish this:

1"; SELECT * FROM USERS WHERE "%"="

The 1"; terminates the SQL statement that the server is attempting to execute.

The SELECT * FROM USERS WHERE starts a second SQL statement which will grab all the fields from the users table.

The "%"=" matches the end of the SQL statement that is added after the query. Refer to the fact that %"; was being added to the end of the input submitted by the search bar. The net result of the two parts combined is $\colorbox{#d9d900}{\textsf{"\%"="}} \colorbox{#efefef}{\textsf{\%";}}$ which will cause the WHERE to be true for every row in the table.

The 1 in the input was chosen specifically because no doctors had a 1 in their name, preventing duplicate results as any results from the first statement would be in addition to the results from the second statement. The 1 could be replaced by any other unused character to achieve the same effect or removed entirely if duplicate results are not a concern.

Be careful with the encoding of the % as some browsers may modify this.

SELECT name, type FROM USERS WHERE name LIKE "%1"; SELECT * FROM USERS WHERE "%"="%";

The yellow highlights the portion of the SQL statement that is provided in the search bar. The executed query will return results containing all the fields of all users stored in the database. Please keep in mind there are multiple different ways this vulnerability could be exploited and this document is only demonstrating one of those ways.

Questions

What is the name of the only Orthopedist?

Enter a blank space into the search box to show all the results and find the Orthopedist.

What is Katie Cain’s profession?

Enter a blank space into the search box to show all the results and find Katie Cain’s profession

How many medical professionals can be found in this registry?

Enter a blank space into the search box to show all the results and count the total results.

What is the name of the person who has a password of "greyblob"?

Type 1"; SELECT * FROM USERS WHERE "%"=" into the search box to perform a SQL injection and display all of the columns in the USERS table.

What is Mike Torres' password?