SQL Injection Vulnerability Solution

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
If you have edited the Main.java file and it contains compiler errors, the make command will fail and show you where the errors were found.

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?

You should eventually find yourself inspecting the following segment of code:

// check for the username/password in database String sqlQuery = "SELECT COUNT(*) AS count FROM USERS WHERE username == '" + username + "' AND password == '" + password + "'"; statement = c.createStatement(); results = statement.executeQuery(sqlQuery); // if no user with that username/password, return false; otherwise must be true if (results.getInt("count") == 0) return false; else return true;
This will be the key to exploiting the SQL Injection vulnerability. We need to make the SQL query return a non-zero number for the count of tuples matching the WHERE clause, and our input to password is used directly in the SQL query.

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.

If you're stuck, start by thinking of how we can change the SQL query's WHERE clause by controlling the password field. A good place to look is in the order of operations of AND and OR in SQL queries. For example, the boolean statement x==y
AND a==b OR True will always evaluate to True, regardless of the variables. Try again with this in mind.

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.

3. Delivery Instructions
You'll need to deliver a short report containing:

Screenshots showing the input used for the attack, and the output you got from the system.
Your commented code for the mitigation.
Screenshots showing the input and output after fixing the vulnerability.
A short explanation on your attack and your mitigation.