Lab 04: SQL Injection Attack Lab

Due Sunday October 22nd at 11:59 PM

SQL Injection Attack Lab

Adapted from SEED Labs: A Hands-on Lab for Security Education.

SQL injection is a code injection technique that exploits vulnerabilities in the interface between web applications (web apps) and databases. The vulnerability is present when user inputs are not properly handled within the web app before being sent to a back-end database.

Many web apps take inputs from users, and then use these inputs to construct SQL queries, which retrieve information from a database. Web apps also use SQL queries to store information in the database. These are common practices in the development of web apps. When SQL queries are not carefully constructed, SQL injection vulnerabilities can occur. SQL injection is one of the most common attacks on web apps.

In this lab, we have created a web app that is vulnerable to SQL injection attacks. Our web app includes examples of common mistakes made by many web developers. Your goal is to find ways to exploit these SQL injection vulnerabilities, demonstrate the damage that can be done by these attacks, and master the techniques that can help defend against these types of attacks.

This lab covers the following topics:

• Various SQL statements: SELECT, UPDATE, etc.
• SQL Injection strategies
• An effective SQL Injection countermeasure: prepared statements

Resources

• Code related to this lab can be found in 04_sqli/ of our class’s GitHub repository.
• A website dedicated to SQL injection attacks.
• How Does the Internet Work? (YouTube)
• How the Internet works (MDN Web Docs)
• How the Web works (MDN Web Docs)
• HTTP Tutorials (MDN Web Docs)
• PHP Manual (php.net)
• MySQL API (php.net)
• Chapter 12 in the SEED Textbook.

 10.9.0.5        www.seedlabsqlinjection.com 

$ cd ~
$ git clone https://github.com/reesep/csci476-code.git code # name the local clone 'code'
$ cd /home/seed/code/04_sqli

# First, build the container
$ docker-compose build    # Build the container image

# Next, start/stop the container(s) as needed
$ docker-compose up -d    # Start the container (-d runs container in the background; i.e., detached)
$ docker-compose down     # Shut down the container

$ docker ps -a   # Show all containers (default shows just running)
$ dockps         # Show active containers using custom formatting for docker ps
$ docksh <id>    # Connect to container with <id>

### Examples ###

# The following example shows how to get a shell inside hostC
$ dockps
b1004832e275  hostA-10.9.0.5
0af4ea7a3e2e  hostB-10.9.0.6
9652715c8e0a  hostC-10.9.0.7

# Attach to the container with an ID that starts with "96"
$ docksh 96
root@9652715c8e0a:/#

# NOTE: If a docker command requires a container ID, you do not need to type the entire ID string.
# Typing the first few characters will be sufficient so long as it can uniquely identify a container.

### see docker aliases ###
$ grep docker ~/.bashrc

$ sudo rm -rf mysql_data

SELECT * from credential
WHERE name='$name' and password='$pwd';

Lab Tasks

This lab has been tested on the pre-built SEED VM (Ubuntu 20.04 VM).

Task 1: Get Familiar with SQL Statements (3 pts)

The objective of this task is to get familiar with SQL commands by experimenting within the provided database. The data used by our web app is stored in a MySQL database, which is hosted on our MySQL container. We have created a database called sqllab_users, which contains a table called credential. The table stores the personal information (e.g., eid, password, salary, ssn, etc.) of every employee. In this task, you will interact with this database from the mysql command line interface (CLI) to get familiar with SQL queries.

First, make sure your containers are running (see the “Environment Setup” section above).

Next, get a shell on the MySQL container:

$ dockps
# ...ids of containers...
$ docksh <id-of-mysql-container>

Then start the mysql client program to interact with the database. The username is root and password is dees.

# Inside the MySQL container
$ mysql --user=root --password=dees

After you log in, you can create a new database or use an existing one. You can see which databases currently exist using the show databases command. We have already created the sqllab_users database for you, so for this task you just need to load this existing database using the use command. To show what tables exist within the sqllab_users database, you can use the show tables command to print out all the tables of the currently selected database.

After running the commands above, you need to use a SQL command to print all the information for the employee Alice.

Please provide proof of your results (e.g., command line output, screenshot).

Task 2: A SQL Injection Attack on a SELECT Statement

A SQL injection is basically a technique through which attackers can execute their own (malicious) SQL statements; the input, which is specially crafted to inject attacker-chosen SQL statements, is generally referred to as the payload. Through the injected SQL statements, attackers can steal information from the victim database, or even worse, they may be able to make changes to the database (e.g., update or delete information). Our employee management web application has SQL injection vulnerabilities that mimic the mistakes frequently made by web app developers.

To explore SQL injection vulnerabilities in this task, we will use the login page found at http://www.seedlabsqlinjection.com/. The login page is shown in the figure below. As is typical in authentication, the web app prompts users to provide a username and a password. The web app then authenticates users based on these two pieces of information. Presumably, only employees who know their own username and password should be able to log in. Your objective throughout this task is to exploit a SQL injection vulnerability in the login page to successfully log into the web app without knowing any valid employee credentials.

$input_uname = $_GET['username'];
$input_pwd = $_GET['password'];
$hashed_pwd = sha1($input_pwd);

$sql = "SELECT id, name, eid, salary, birth, ssn, address, email, nickname, password
        FROM credential
        WHERE name= '$input_uname' and password='$hashed_pwd'";
$result = $conn -> query($sql);

// The following is primarily pseudocode. You won't find most of these exact lines in the
// actual source code linked above, but the idea is what is important.
if (id != NULL) {
  if (name == 'admin') {
     return All employees information;
  } else if (name != NULL) {
    return employee information;
  }
} else {
  Authentication fails;
}

Task 2.1: SQL Injection Attack from the Login Webpage (4 pts)

In this task you need to log into the web app as the administrator from the login page, which will enable you to see the information of all employees. We assume that you do know the administrator’s account name, which is admin, but you do not know the password. You need to decide what payload to enter into the “Username” and/or “Password” fields to succeed in your attack. Your lab report should include the payload you used for the username and password, as well as a screenshot that show you were able to login successfully.

Task 2.2: SQL Injection Attack from the Command Line (4 pts)

In this task you need to repeat Task 2.1, but you need to do it without using the login webpage. You can use command line tools, such as curl, which can be used to send HTTP requests. Using curl is probably the quickest and easiest way to go, but there are also other great libraries that are fun to learn; e.g., requests in Python, httpparty in Ruby.

$ curl 'www.seedlabsqlinjection.com/unsafe_home.php?username=alice&password=mypass123'

Task 2.3: (Try to…) Append a New SQL Statement (3 pts)

In the above two attacks, we only view arbitrary information from the database; it could be interesting to explore whether it is possible modify the database using the same vulnerability in the login page. One idea is to use the SQL injection attack to turn one SQL statement into two, with the second one being an update or delete statement. In SQL, a semicolon (;) is used to separate two SQL statements. With this idea in mind, now try to craft your input so that you can run two SQL statements via the login page.

(Spoiler alert!) It turns out that there is a countermeasure to prevent this specific issue known as a prepared statement. Feel free to do some independent research on this topic and describe your discovery in the lab report.

Task 3: SQL Injection Attack on UPDATE Statement

If a SQL injection vulnerability happens to an UPDATE statement, the damage could be quite severe, because attackers can use such SQL injection vulnerabilities to modify databases. In our employee management web app, there is an “edit profile” page (see figure below) that allows employees to update their profile information, including nickname, email, address, phone number, and password. To access this page, employees need to log in first.

When employees update their information through the edit profile page, the following SQL UPDATE query will be executed. The PHP code implemented in /var/www/SQLInjection/unsafe_edit_backend.php file is used to update employee’s profile information.

$hashed_pwd = sha1($input_pwd);
$sql = "UPDATE credential SET
    nickname='$input_nickname',
    email='$input_email',
    address='$input_address',
    password='$hashed_pwd',
    PhoneNumber='$input_phonenumber'
    WHERE ID=$id;";
$conn->query($sql);

Task 3.1: Modify Your Salary (4 pts)

As you can see in the edit profile page, employees can only update their nicknames, emails, addresses, phone numbers, and passwords; they are not authorized to change their salaries.

Assume that you (Alice) are a disgruntled employee, and your boss did not increase your salary this year. You want to increase your own salary by exploiting the SQL injection vulnerability in the “edit profile” page. Please demonstrate how you can achieve this kind of update. We assume that you do know Alice’s credentials and that salary information is stored in a column named salary. Your lab report should include the payload you used for the nickname field, as well as a screenshot that show you were able to edit your information successfully.

Task 3.2: Modify the Salary of Others (4 pts)

After increasing your own salary, you decide to punish your boss Samy. You want to reduce their salary to 1 dollar. (Dont worry, Samy is a total jerk and they deserve this!)

Please demonstrate how you can achieve this update. Your lab report should include the payload you used for the nickname field, as well as a screenshot that show you were able to edit Samy's information successfully.

Task 3.3: Modify the Password of Others (4 pts)

After changing Samy’s salary, you are still disgruntled, so you want to change Samy’s password to something that you know, and then you can log into their account and do further damage. Please demonstrate how you can achieve this. Your lab report should include the payload you used for the nickname field, as well as a screenshot that show you were able to change Samy's password successfully.

You need to demonstrate that you can successfully log into Samy’s account using the new password. One thing worth mentioning here is that the database stores the SHA1 hash value of passwords instead of the plaintext password string. You can look at the unsafe_edit_backend.php code to see exactly how the password is being stored.

You are welcome to generate a sha1 hash of the new password however is most convenient for you. For example, you can do this directly in an SQL statement in MySQL or at the MySQL CLI, using a normal command line tool such as openssl, or using an online sha1 calculator.

Task 4: SQLi Countermeasure: Prepared Statements (4 pts)

The fundamental problem of the SQL injection vulnerability is the failure to clearly separate code from data. When constructing a SQL statement, the program (e.g., PHP program) knows which part is data and which part is code. Unfortunately, when the SQL statement is sent to the database, the distinction is lost; the boundaries that the SQL interpreter sees may be different from the original boundaries that were intended by the developers. To solve this problem, it is important to ensure that the view of the boundaries between code and data are consistent in the server-side code and in the database.

The folks at PortSwigger (the makers of Burp Suite) do a nice job of summarizing why parameterized queries are an effective countermeasure:

The most effective way to prevent SQL injection attacks is to use parameterized queries (also known as prepared statements) for all database access. This method uses two steps to incorporate potentially tainted data into SQL queries: first, the application specifies the structure of the query, leaving placeholders for each item of user input; second, the application specifies the contents of each placeholder. Because the structure of the query has already been defined in the first step, it is not possible for malformed data in the second step to interfere with the query structure. You should review the documentation for your database and application platform to determine the appropriate APIs which you can use to perform parameterized queries. It is strongly recommended that you parameterize every variable data item that is incorporated into database queries, even if it is not obviously tainted, to prevent oversights occurring and avoid vulnerabilities being introduced by changes elsewhere within the code base of the application.

$sql = "SELECT name, local, gender  
        FROM USER_TABLE
        WHERE id = $id AND password ='$pwd' ";
$result = $conn->query($sql))

// Prepare the query
$stmt = $conn->prepare("SELECT name, local, gender
                        FROM USER_TABLE
                        WHERE id = ? and password = ? ");

// Bind parameters to the query
$stmt->bind_param("is", $id, $pwd);
$stmt->execute();
$stmt->bind_result($bind_name, $bind_local, $bind_gender);
$stmt->fetch();

Task 4.1: Patching Code to Use Prepared Statements

In this task, we will use the prepared statement mechanism to fix SQL injection vulnerabilities. For the sake of simplicity, we have the patched version that uses prepare statements: You will need to take this code, and paste it into the 04_sqli/image_www/code/defense/unsafe.php file. If you point your browser to the following URL — http://www.seedlabsqlinjection.com/defense/ — you will see a page similar to the original login page for the web app. This page allows you to query an employee’s information, but you need to provide the correct user name and password.

The data submitted via this page will be sent to the server program getinfo.php, which invokes a program called unsafe.php. The SQL query inside this PHP program is vulnerable to SQL injection attacks. Your objective for this task is to update unsafe.php with the correct code that uses prepare statements (given above), and verify that your SQL injection attack no longer works.

Submission

Submit your assignment as a single PDF to the appropriate D2L dropbox