SQL Injection Scenario Using Spring

This repository implements the scenario of a SQL injection attack on a custom web server, using the #Spring-Security Framework version 6.1.2, #Spring-Web, #Gradle, #PostgreSQL for the management of the created database, #Lombok, #Thymeleaf, and #Guava.

Below, we are going to have a brief look at the exact SQL commands used to set up the demo database, along with detailed descriptions of each table. After that, we will demonstrate the use of the app using a step-by-step presentation of a malicious attack scenario!

📢Note📢

🎯Please, clone this repository before reading the description. Don't forget to like👍and share your thoughts😊.

Database Set Up

Assuming PostgreSQL is properly installed on the end-system, we used the following command to create a demo database called GDPR.

CREATE DATABASE GDPR;

Users Table

The users table contains the id field, which is the primary key, and uniquely identifies each record contained in the table. The username field refers to each user's username, and salt is a random string of 10 characters and is used to maximize security in case of password file theft. The password field holds the hashed password of the user using the MD5 activation function. The last_modified field is also used and for future reference refers to the date the password field was last modified. Finally, the description field refers to a string representing a short description of each user. To create the table, the following SQL command was used.

CREATE TABLE users (id SERIAL PRIMARY KEY, username VARCHAR(100) NOT NULL, salt VARCHAR(50) NOT NULL,
password VARCHAR(1000) NOT NULL, last_modified VARCHAR(100) NOT NULL, description VARCHAR(1000) NOT NULL);

And for demonstration reasons, we could insert the following two initial users.

INSERT INTO users (username, salt, password, last_modified, description)
VALUES('p3180102', 'zZzCylMVZq', '41e0d439817897cd9f6b50af0f4c1ab1', '2022-01-18T18:21:32.599599Z', 'None');

INSERT INTO users (username, salt, password, last_modified, description)
VALUES('admin', 'ViMwLywO8w', '2ddf79a32b82f2649b3c1add1553d9f3', '2022-01-18T18:23:09.567006Z', 'None');

Logging Table

Each row in this table refers to a log attempt. Each attempt is characterized by a unique identifier id, the username that attempted to log into the application, the corresponding hashed password, the field indicating whether the login was successful (boolean), and a timestamp field to track the exact date and time the attempt was made. At this point, note that if one of the attempts uses one of the two usernames stored in the database (p3180102 or admin), the password field will record the MD5 hash of the password typed in the form & the corresponding salt retrieved from the database. Otherwise, obviously, the connection is rejected. To create the table, the following code was used.

CREATE TABLE logging (id SERIAL PRIMARY KEY, username VARCHAR(100) NOT NULL, password VARCHAR(1000)
NOT NULL, successful boolean NOT NULL, date VARCHAR(100) NOT NULL);

User Lock Feature 🔐

The application is designed in a way that blocks (for security reasons) the IP addresses of the users who attempt to login to the application, in case they perform more than two consecutive failed login attempts. The reason why choosing to block the addresses, and not the user account, has to do with the fact that by design assumptions, it is desired to completely exclude the possibility of brute force attacks, as the attacker can continuously try to log in with multiple usernames, and different passwords. In this way, even if the attackers use brute force attack software in an attempt to perform a SQL injection attack, the application will lock them out (and no login attempts will be recorded from a certain point onwards). The ability to log in after being blocked becomes available again after a day passes (24 hours), and during that time, the user is constantly updated with alerts in the front-end section of the website while login attempts are not even recorded in the database!

Change Password Feature 🗝

For this particular functionality, after a successful user login, the back-end checks if ten days have passed since the last time the password was changed. If so, then it redirects the user to a new window and asks for the password to be changed again for security reasons.

Demo Scenarios

📌 You can click here to view an IP blocking demo scenario (after failed consistent attempts).

📌 You can click here to view a SQL injection demo scenario.