3.8.1 SQL Injection Exercise

1. Introduction

SQL Injections are possible when improperly validated/escaped input is interpreted by a SQL Parser (i.e., included in a SQL statement). Dangerous consequences, such as data leakage and unauthorized access, can result.

1.1 Exercise Description

In this exercise, we provide a very simple program that checks for a given username and password in a portable SQLite database. If a correct username and password is given, then the program will display a welcome message. If an incorrect password or nonexistent username is supplied, the program will display a failure message. Your objective is to exploit a SQL injection vulnerability and trigger a "successful" login without a correct password (or even without a correct username).

Note: This program violates several best practices regarding password storage, credential management, authentication, etc., but you should focus on the SQL Injection vulnerability for this exercise.

1.2 Vulnerability Mitigation - Prepared Statements

Prepared Statements protect an application from SQL Injections by parsing the SQL query separate from the runtime input. For example, a prepared statement may look like SELECT * FROM USERS WHERE username == ?, and this is the string that is parsed by the SQL database management system. The parser reserves a place for the input (string in this case) in place of the ? and does the comparison on the input without ever parsing SQL metacharacters from the input.

We will fix the SQL Injection vulnerability in Main.java using prepared statements. If implemented correctly, the prepared statement will force the database to compare real passwords with the entire input string and thus return false unless the user's password is exactly what is input to the program.

2. Exercise Instructions

This exercise will be completed entirely on the command line terminal of the provided virtual machine. To open the terminal, right-click on the "EXERCISES" directory and select "Open in Terminal". Enter the following command to change into the SQL Injection exercise directory:

cd 3.8.1_sql_injections

2.1 Compile the Program

We provide a Makefile that will compile the program. Every time you change the Main.java file, you must recompile the program before running it again. Enter the following command to compile the program:

make

2.2 Run the Program

The next step is to run the program and test some inputs. To execute the program after compiling, enter the following command:

java Main

You should see output from the program prompting for a username. Type a username and press enter. The program will then prompt you for a password. Type a password and press enter. To ease exploitation, the password field will not be hidden. The program will check the SQLite database for the username/password combination and tell you if the login was successful. The following is an example of a correct username/password input:

username: some_guy
password: his_password
Login Successful! Welcome some_guy

Try this a few times with different usernames and passwords to see how the program behaves. To exit the program, type exit in place of a username. All of the "correct" username and password combinations can be found in create_db.sql, which the Makefile uses to generate the database.

2.3 Inspect the Program Code

Now that you understand the basic behavior of the program, it's time to look at the implementation. This program is implemented in Main.java. Use your favorite text editor to open this file. Enter the following command to open the file in Nano:

nano Main.java

Spend some time looking at the code and tracing the flow of execution. You're looking for an attack surface and corresponding attack vector. In other words, how can an attacker's input reach the database?

2.4 Exploit the Vulnerability

Exit the text editor. Run the program again in the same way as Step 2. Enter a username like "some_guy" and then a password that, when inserted into the SQL query, will ensure that the WHERE clause is always satisfied.

Once you manage to get a "Login Successful" message without a correct password, it's time to fix the vulnerability!

2.5 Mitigate the Vulnerability

Let's go back to our text editor and open Main.java (see Step 3). This time, we're going to make some changes. Using what you know about Prepared Statements, make some changes to the vulnerable code sample so the user input is never interpreted as part of the SQL query.

Once you have a potential fix implemented, save and exit the file. Now recompile and run the program as we did in the compilation step and the run step. Try your exploit again. Make sure to test a good password so you know the intended function is not broken.

Repeat the process of changing the program, compiling, and testing until you are convinced that the vulnerability is mitigated and the original program intent remains functional.