SIT 218/738: Secure coding

Credit task 5.2C: SQL injection (Part 2)

Objective

Prevention of SQL injection attack requires both input validations and safe SQL statements.

In this task you will address SQL injection attack using prepared statements. A prepared

statement binds the user input after the query is prepared using the PreparedStatement()

method. Additionally, the Hibernate ORM uses PreparedStatement() by default, so you will

need to analyse safe SQL statements used in Hibernate ORM that addresses the SQL injection

attacks.

Overview

Task1: In the SQL Injection web app which you used for Task 5.1P, replace the vulnerable query with

a safe SQL query that uses PreparedStatement() and setString() methods similar to what we used in

the SQLInjectionTest.java program discussed in the "Ontrack 5.1P Start Activity". (You don't need to

run the webapp with new query, only the safe SQL query is required.)

Task2: The Hibernate ORM uses PreparedStatement() by default to generate SQL queries. Additionally,

it uses parameter binding to generate safe SQL queries. For this task you are required to:

a.

Download the secure version (SQLInjection-secured) of the previous vulnerable webapp from

the resources section.

b. Import the SQLInjection-secured webapp into Eclipse IDE (follow the steps like you did in Task

3.2C).

c. Highlight the secure SQL queries in the existsClient() and areCredentialsCorrect() functions

in ClientDAOImpl.java file located in the src folder under Dao as shown in the following

screenshot.

SQLInjection-secured

▸ Deployment Descriptor: SQLInjection-secured

JAX-WS Web Services

Java Resources

Referenced Libraries

▸ build

src

main

java

edu

@deakin

sit218

coachwebapp

▸ controller

đạo

ClientDAO.java

ClientDAOImpl.java

▸entity/nd. Execute the application and try to bypass credentials for the username 'Alice' using SQL

injection attack and record the response on web page and console log from Hibernate as

shown below. You can view the exact query passed to the SQL server in the Eclipse Console.

Markers Properties & Servers Data Source Explorer Snippets Terminal Console Progress

Tem Server chest (Apache Tomcat) lopt/ec ipselectipserpluginstory eclipse just openik hotspot ne fulllimm.x86_64 17.0 6 x20230204.1729/rebinjana |

Jul 17,

erate.ec.confections underal orientarecterpPostsections t

0:15:bersate comection posta)

317, 3825 9:24:39 m arg.hibernate.lalectalet

::incialect: org.hibernate.dialect.Malect

bernate:

Submission Requirements:

Submit one PDF file containing the following information:

1. Correct piece of code using PreparedStatement() method.

2. Highlight the parameter binding code used in Hibernamte ORM in the existsClient()

and areCredentialsCorrect() functions in ClientDAOImpl.java file located in the src folder

under Dao

3. Screenshot of the application's response when targeted with SQL injection for the username

'Alice'

4. Screenshot of the SQL queries generated by Hibernate in the Eclipse console.