Ontrack Task5.
1 Start Activity
SQL injection is a type of security vulnerability that occurs when an attacker inserts malicious SQL code
into a query, which is then executed by a database server. This can lead to unauthorized access, data
theft, data manipulation, and even complete system compromise. SQL injection attacks are mostly
directed towards web applications that use SQL databases in the backend.
Run a simple SQL based command line program
1. First let's start the mysql database on the kali Linux, to do this use the command
sudo systemctl start mysql
2. Start eclipse
cd /opt/eclipse/eclipse
sudo ./eclipse
when prompted for password enter ‘kali’
3. Navigate to the 07-coachwebapp-spring-hibernate project in the project explorer.
4. Expand the ‘src’ folder until you see the SQLInjectionTest.java file. Double click the file and
you will see the code loaded in the eclipse workspace.
Make the following changes before proceeding.
Uncomment the code between lines 29 and 35 by deleting the characters ‘/*’ and ‘*/’ as shown
below:
Change to
Also comment the lines between 38 and 41 as shown below by adding ‘/*’ and ‘*/’ :
Change to
Now save the code by pressing cntrl+s.
5. Right click on the SQLInjectionTest.java file under test folder in the project explorer and
select Run As and select Java Application.
You will notice the code running in the console at the bottom of the eclipse window.
Activity-1: Test the application to retrieve user information
The aim is to first retrieve some existing information from the database table. The piece of code that
uses the input given by the user to search the database is shown below.
If we want to fetch the information regarding client ‘Bob’ we can enter this in the console and the
application will retrieve the matching the results. Now enter the value ‘Bob’ in the console where it
prompts “Enter your name:”
You can see the details of the user Bob displayed which contains the ID, Name, Age and Number of
workouts.
Activity-2: Perform SQL injection
To perform SQLinjection the idea is to enter an input that allows us to fetch all the information which
we are not authorised or do not have access to. Here we do not know the other users in the database.
But we can trick the application to fetch all the user’s information due to the vulnerable code. The
vulnerability in the code is it fetches the users input and without any validations passes it to the SQL
query. So there SQL injections are a result of lack of input validations and secondly generating SQL
queries using the user input. Let's see this in action.
As you can see the input is passed to the SQL query without any validations. This makes the code
vulnerable to SQL injection. When we enter the input as eg. Bob’ OR 1=1--’ then this will translate
into the code as : here input is: Bob’ OR 1=1--’
SELECT * FROM client WHERE name = ‘Bob’ OR 1 = 1 --’’
The value ‘1=1’ is also called as tautology and is always TRUE which will fetch all the values. The --' in
the injected query acts as comment and everything after that will be disregarded by the SQL query.
And when this is passed to the injected OR parameter the SQL query will fetch either Bob OR
everything. Lets try this in the program.
Run the code again as done before using the Run As and enter the below input
Bob' OR 1=1 --'
You have now done the SQL injection attack and retrieved all the values in the table.
Activity-3: Preventing SQL injection
The way to make the SQL query safer is to validate the input and don’t permit unintended input and
also use preparedstaments to prepare the query and set the correct user values in placeholders
instead of generating the query with the user’s input. In the code file SQLInjectionTest.java make
the following changes:
We need to comment the vulnerable code and enable the safe SQL query statement. This can done
by commenting lines 30-34, Add /* before the Statement statement = conn.createStatement(); line
30 and after the ResultSet line 34 add */. Also uncomment the lines from 36 to 42.
Before changes in the code:
After changes in the code.
Now run the java code as done previously. And try to enter the malicious input when it prompts for
name.
You can now see the connection is successful but nothing is retrieved from the database.
Reason for this is that you first prepare the statement and then insert the users value in to the query
rather than generating the query after the user enters the input. This protects the query from being
misused.
Activity-4: SQL injection test in the webapp.
Vulnerable SQL queries are used in user authentication then this can lead to users getting
unauthorised access.
In the Project explorer, open the SQLInjection webapp. Expand the src folder and double click
on ClientDAOImpl.java
Few corrections in the code to run the web app correctly
In the code below, comment (using ‘//’) the line number 61 and change the variable name in line 64
from ‘result’ to ‘username’.
After changes in the code:
Save the file and right click on the SQLInjection project and Run AS on Server.
You will see the app loaded in the webbrowser
Try to login to the application without the password. One of the usernames configured in this
application is ‘Alice’. Login using this username.
Continue to the Task 5.1P.
