Spring Security 6 Custom Login, OAuth2 Login with Google and Basic Auth

 

Introduction:

Spring Security is a framework that provides authentication, authorization, and protection against common attacks. With first class support for securing both imperative and reactive applications, it is the de-facto standard for securing Spring-based applications.

Consuming Authentication objects:

Spring security provides Authentication. 

• Authentication (authn): represent the users.
• Principal: (name, email etc)
• GrantedAuthorities: roles.

• Authorization (authz): are the users  allowed to perform.

Image from Spring Security, demystified

Demos: 

In this post I integrate Custom Login Form, OAuth2 Login with Google and Basic Auth.

Technology

• Spring Boot 3.0.6
• Java 17
• OAuth2
• Maven 
• IntelliJ IDEA

Security Configuration:

SecurityConfig.java: In this class, I configure Custom Login, OAuth2 Login with Google and Basic Auth. sometimes we need apply some filter before, or some case we need test with user admin in this case I create the class AdminAuthenticateProvider without password.

package com.henry.springsecurity.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.Customizer;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityCustomizer;
import org.springframework.security.core.userdetails.User;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.provisioning.InMemoryUserDetailsManager;
import org.springframework.security.web.SecurityFilterChain;
import org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter;

@Configuration
@EnableWebSecurity
public class SecurityConfig {


@Bean
public SecurityFilterChain securityFilterChain(HttpSecurity http) throws Exception {

return http
.authorizeRequests( authorizeConfig -> {
authorizeConfig.requestMatchers("/").permitAll();
authorizeConfig.requestMatchers("/login/**").permitAll();
authorizeConfig.requestMatchers("/error").permitAll();
authorizeConfig.requestMatchers("/favicon.ico").permitAll();
authorizeConfig.anyRequest().authenticated();
})
.formLogin( login -> {
login.loginPage("/login").permitAll();
login.defaultSuccessUrl("/private");
login.failureUrl("/login?error=true").permitAll();
}) //Normal Login
.logout(logout -> {
logout.logoutSuccessUrl("/login?logout=true").permitAll();
logout.invalidateHttpSession(true).permitAll();
logout.deleteCookies("JSESSIONID").permitAll();
})
.httpBasic(Customizer.withDefaults()) // support basic auth
.oauth2Login(oauth -> {
oauth.loginPage("/login").permitAll();
oauth.defaultSuccessUrl("/private");
oauth.failureUrl("/login?error=true").permitAll();
}) // OpenID Connect with google
.addFilterBefore(new CustFilter(), UsernamePasswordAuthenticationFilter.class)
.authenticationProvider(new AdminAuthenticateProvider())
.csrf()
.disable()
.build();
}

@Bean
public UserDetailsService userDetailsService(){
return new InMemoryUserDetailsManager(
User.builder()
.username("henry")
.password("{noop}password")
.authorities("ROLE_USER")
.build()
);
}

@Bean
public WebSecurityCustomizer webSecurityCustomizer() {
return (web) -> web.ignoring() .requestMatchers("/resources/**", "/static/**", "/css/**");
}
}

AdminAuthenticateProvider.java

package com.henry.springsecurity.config;

import org.springframework.security.authentication.AuthenticationProvider;
import org.springframework.security.authentication.UsernamePasswordAuthenticationToken;
import org.springframework.security.core.Authentication;
import org.springframework.security.core.AuthenticationException;
import org.springframework.security.core.authority.AuthorityUtils;

public class AdminAuthenticateProvider implements AuthenticationProvider {
@Override
public Authentication authenticate(Authentication authentication) throws AuthenticationException {
var username = authentication.getName();
if("admin".equalsIgnoreCase(username)){
return UsernamePasswordAuthenticationToken.authenticated(
"admin",
null,
AuthorityUtils.createAuthorityList("ROLE_ADMIN")
);
}
return null;
}

@Override
public boolean supports(Class<?> authentication) {
return UsernamePasswordAuthenticationToken.class.isAssignableFrom(authentication);
}
}

CustFilter.java

package com.henry.springsecurity.config;

import jakarta.servlet.*;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import org.springframework.web.filter.OncePerRequestFilter;

import java.io.IOException;

public class CustFilter extends OncePerRequestFilter {

@Override
protected void doFilterInternal(HttpServletRequest request,
HttpServletResponse response,
FilterChain filterChain) throws ServletException, IOException {
System.out.println("Hello filter");
filterChain.doFilter(request, response);
}
}

OAuth2 Login with Google Configuration:

Go to Console Cloud Google 

1. Create new project or choose existing project.
2. Go to APIs & Services or Type oauth in search bar
3. Choose  Credentials 
4. Click on CREATE CREDENTIALS ->  OAuth client ID 
5. Application Type -> Web application
6. Name -> your app name
7. Authorized redirect URIs: http://localhost:8080/login/oauth2/code/google
8. CREATE

View 

Create a login page our own.

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:th="https://www.thymeleaf.org">
<head>
<title>Please Log In</title>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta/css/bootstrap.min.css" rel="stylesheet" >
<link href="https://getbootstrap.com/docs/4.0/examples/signin/signin.css" rel="stylesheet" >
<link rel="stylesheet" href="/css/login.css" type="text/css"/>
</head>
<body>
<section class="vh-100">
<div class="container-fluid h-custom">
<div class="row d-flex justify-content-center align-items-center h-100">
<div class="col-md-8 col-lg-6 col-xl-4 offset-xl-1">
<div
class="d-flex flex-row align-items-center justify-content-center justify-content-lg-start">
<p class="lead fw-normal mb-0 me-3">Login with OAuth 2.0</p>
</div>
<div class="d-flex flex-row align-items-center justify-content-center justify-content-lg-start">
<table class="table table-striped">
<tbody>
<tr>
<td><a href="/oauth2/authorization/google">Google</a></td>
</tr>
</tbody>
</table>
</div>
<form th:action="@{/login}" method="post">
<div class="divider d-flex align-items-center my-4">
<p class="text-center fw-bold mx-3 mb-0">Or</p>
</div>
<!-- Username input -->
<div class="form-outline mb-4">
<input type="text" name="username" class="form-control form-control-lg"
placeholder="Username" />
</div>
<!-- Password input -->
<div class="form-outline mb-3">
<input type="password" name="password" class="form-control form-control-lg"
placeholder="Password" />
</div>
<div class="text-center text-lg-start mt-4 pt-2">
<button type="submit" class="btn btn-primary btn-lg"
style="padding-left: 2.5rem; padding-right: 2.5rem;">Login</button>
</div>
</form>
</div>
</div>
</div>
</section>
</body>
</html>

Controller:

LoginController.java

package com.henry.springsecurity.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;

@Controller
public class LoginController {

@GetMapping("/login")
public String login() {
return "login";
}
}

WebController.java

package com.henry.springsecurity.controller;

import org.springframework.security.core.Authentication;
import org.springframework.security.oauth2.core.oidc.user.OidcUser;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Optional;

@RestController
public class WebController {


@GetMapping("/")
public String publicPage(){
return "Hello Henry";
}

@GetMapping("/private")
public String privatePage(Authentication authentication){
return "Welcome " + getName(authentication);
}

private String getName(Authentication authentication) {
return Optional.of(authentication.getPrincipal())
.filter(OidcUser.class::isInstance)
.map(OidcUser.class::cast)
.map(OidcUser::getEmail)
.orElseGet(authentication::getName);

}
}

Project Dependencies:

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.0.6</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.henry</groupId>
<artifactId>spring-security</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>spring-security</name>
<description>Demo project for Spring Boot Security</description>
<properties>
<java.version>17</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-security</artifactId>
</dependency>


<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-oauth2-client</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>

<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>

</project>

application.yml

spring:
security:
oauth2:
client:
registration:
google:
client-id: YOUR_CLIENT_ID
client-secret: YOUR_CLIENT_SECRET

Run & Test

Run Spring Boot application with command: mvn spring-boot:run. by console, IntelliJ etc.

• Without Security go to: http://localhost:8080/ result this:

• With Security go to: http://localhost:8080/private result this:

Type:

User: henry 

Password: password 

Note: In the case, we will be test with admin user,  you can typing anything password, because I created the  AdminAuthenticateProvider class and configure in Spring Security config authenticationProvider. for more details check in the end the references.

• Test 1 - With user and password result this

• Test 2 - With  OAuth2 Login with Google result this

• Test 3 - With Basic Auth

• Test 4 - With admin user typing anything password.

Source Code

Here on GitHub.

References.

Spring Security, demystified by Daniel Garnier Moiroux

https://docs.spring.io/spring-security/reference/index.html

https://docs.spring.io/spring-security/reference/features/authentication/index.html

Spring Data Redis

Introduction:

The Spring Data Redis (SDR) framework makes it easy to write Spring applications that use the Redis key-value store by eliminating the redundant tasks and boilerplate code required for interacting with the store through Spring’s excellent infrastructure support.

Redis Requirements

Spring Redis requires Redis 2.6 or above and Spring Data Redis integrates with Lettuce and Jedis, two popular open-source Java libraries for Redis.

Configuring the Lettuce Connector

<!--Lettuce -->
<dependency>
   <groupId>io.lettuce</groupId>
   <artifactId>lettuce-core</artifactId>
   <version>6.2.3.RELEASE</version>
</dependency>

Create a new Lettuce connection factory:

@Configuration

public class AppConfig {

    @Bean
    public LettuceConnectionFactory redisConnectionFactory() {

        return new LettuceConnectionFactory(new RedisStandaloneConfiguration("localhost", 6379));
    }
}

Configuring the Jedis Connector

<dependency>
   <groupId>redis.clients</groupId>
   <artifactId>jedis</artifactId>
   <version>4.3.1</version>
</dependency>

Create a new Jedis connection factory:
@Configuration
public class AppConfig {
    
    @Bean
    public JedisConnectionFactory redisConnectionFactory() {

        RedisStandaloneConfiguration config = new RedisStandaloneConfiguration("localhost", 6379);
        return new JedisConnectionFactory(config);
    }
}

Simple configuration in application.yaml
# Server config
server:
  port: 9000

# Redis Config
spring:
  redis:
   host: 127.0.0.1
   port: 6379

Overall, Lettuce's event-driven, non-blocking architecture and thread-safety make it a good choice for high-concurrency environments, whereas Jedis' simplicity and ease of use make it a good choice for simpler applications. However, the choice between Lettuce and Jedis ultimately depends on the specific requirements of the application and the environment in which it will be deployed.
RedisTemplate
The template is, in fact, the central class of the Redis module, due to its rich feature set. The template offers a high-level abstraction for Redis interactions. 

@Bean
public RedisTemplate<String, Object> redisTemplate(RedisConnectionFactory connectionFactory) {
    RedisTemplate<String, Object> template = new RedisTemplate<>();
    template.setKeySerializer(new StringRedisSerializer());
    template.setHashKeySerializer(new StringRedisSerializer());
    template.setValueSerializer(new GenericJackson2JsonRedisSerializer());
    template.setConnectionFactory(connectionFactory);
    return template;
}


Use the RedisTemplate to store and retrieve values
private final RedisTemplate<String, Object> redisTemplate;

public My_class(RedisTemplate<String, Object> redisTemplate) {
    this.userRepository = userRepository;
}

The opsForValue() method of the RedisTemplate to interact with Redis as a key-value store.

Technology
Spring Boot 3.0.4
Spring Data Redis 3.0.4
H2 Database
Java 17
Docker
Maven 
IntelliJ IDEA


Downloading and installing Redis
docker run --name my-redis -p 6379:6379 -d redis

Configuring Redis Cache Spring Boot
Use the @EnableCaching annotation on Spring Boot main class:


@SpringBootApplication
@EnableCaching
public class SpringBootRedisCacheApplication implements CommandLineRunner {

   private final Logger LOG = LoggerFactory.getLogger(getClass());

   private final PersonRepository personRepository;

   public SpringBootRedisCacheApplication(PersonRepository personRepository) {
      this.personRepository = personRepository;
   }

   public static void main(String[] args) {
      SpringApplication.run(SpringBootRedisCacheApplication.class, args);
   }

   @Override
   public void run(String... args) throws Exception {

        //Populating embedded database here
      LOG.info("Current user count is {}.", personRepository.count());
      Person p1 = new Person("p1","test", 25);
      Person p2 = new Person("p2", "test",28);
      Person p3 = new Person("p3", "test", 60);

      personRepository.save(p1);
      personRepository.save(p2);
      personRepository.save(p3);
      LOG.info("Data: {}.", personRepository.findAll());

   }
}

Repository
@Repository
public interface PersonRepository extends JpaRepository<Person, Long> {
}

Controller
@Cacheable annotation:

@GetMapping("/{personByRedisTemplate}") using RedisTemplate, retrieve the last person id.
@GetMapping("/{personId}") save in RedisTemplate and method will put a person into a cache named as ‘persons’, identifies that person by the key as ‘personId’ and age < 26.
 @CachePut updating Cache.
@CacheEvict clearing Cache.

@RestController
public class PersonController {

    private final RedisTemplate<String, Object> redisTemplate;

    private final PersonRepository PersonRepository;

    public PersonController(RedisTemplate<String, Object> redisTemplate, PersonRepository PersonRepository) {
        this.redisTemplate = redisTemplate;
        this.PersonRepository = PersonRepository;
    }

    @GetMapping("/personByRedisTemplate")
    public Person getPersonByRedisTemplate(){
        var person =  (Person) redisTemplate.opsForValue().get("personId");
        return person!=null?person:null;
    }

    @Cacheable(value = "persons", key = "#personId", unless = "#result.age < 29")
    @GetMapping("/{personId}")
    public Person getPerson(@PathVariable Long personId) {

       var person = PersonRepository.findById(personId).get();
       redisTemplate.opsForValue().set("personId", person);
       return person;

    }

    @CachePut(value = "persons", key = "#person.id")
    @PutMapping("/update")
    public Person updatePersonByID(@RequestBody Person person) {
        PersonRepository.save(person);
        return person;
    }

    @CacheEvict(value = "persons", allEntries=true)
    @DeleteMapping("/{personId}")
    public void deletePersonByID(@PathVariable Long personId) {
       PersonRepository.delete(new Person(personId));
    }

}


Run & Test

Run Spring Boot application with command: mvn spring-boot:run. by console, IntelliJ etc.
By curl request or Postman.
curl http://127.0.0.1:9000/1
curl http://127.0.0.1:9000/personByRedisTemplate
curl --location --request PUT 'http://127.0.0.1:9000/update' \
--header 'Content-Type: application/json' \
--data '{"id" : 3, "firstname" : "p4", "lastname" : "test", "age" : 30 }'
curl --location --request DELETE 'http://127.0.0.1:9000/3'

Source Code

Here on GitHub.

References.

Why Spring Data Redis

Spring Boot Redis Cache

SQL Injection in Hibernate | Spring JDBC and Prevent it

Introduction:

SQL injection is a type of security vulnerability that allows an attacker to execute malicious SQL code on a database management system through a web application. This can allow an attacker to gain unauthorized access to sensitive information, modify or delete data, or perform other malicious actions on the database.

SQL injection occurs when user-supplied data is not properly sanitized or validated before being used in a database query. If the user-supplied data is directly included in the query, an attacker can manipulate the data to inject malicious SQL code into the query. The attacker's code is then executed by the database management system, allowing the attacker to carry out malicious actions on the database.

For this post, I share 3 techniques: how to do SQL injection and after how to easily prevent it.

for more common SQL Injection can you check SQL Injection Cheat Sheet.

NOTE: In this example the code using  Hibernate and JDBC Template with MySQL. let go.

Downloading and installing MySQL

docker pull mysql/mysql-server:latest

docker run -d -p  3306:3306  --name mysql-docker-container -e MYSQL_ROOT_PASSWORD=mypass -e MYSQL_DATABASE=test_db -e MYSQL_USER=test -e MYSQL_PASSWORD=test_pass mysql/mysql-server:latest

Script

CREATE TABLE customers
  (
     id   BIGINT PRIMARY KEY,
     name VARCHAR(128),
     password VARCHAR(128),
     lastname VARCHAR(128)
  ); 

INSERT INTO customers (id, name, password, lastname)
VALUES
	(1, 'Henry','$2a$10$iWlXVjsSU9.X0oxEjUwyYe3EOc5X2hqacWY7uuyV0BwonTt5SapSu', 'test1'),
	(2, 'Henry','$2a$10$iWlXVjsSU9.X0oxEjUwyYe3EOc5X2hqacWY7uuyV0BwonTt5SapSu', 'test2'),
    (3, 'Henry','$2a$10$iWlXVjsSU9.X0oxEjUwyYe3EOc5X2hqacWY7uuyV0BwonTt5SapSu', 'test3'),
    (4, 'admin','81dc9bdb52d04dc20036dbd8313ed055', 'test4'),
    (5, 'User5','$2a$10$iWlXVjsSU9.X0oxEjUwyYe3EOc5X2hqacWY7uuyV0BwonTt5SapSu', 'test5'),
    (6, 'User6','$2a$10$iWlXVjsSU9.X0oxEjUwyYe3EOc5X2hqacWY7uuyV0BwonTt5SapSu', 'test6'),
    (7, 'User7','$2a$10$iWlXVjsSU9.X0oxEjUwyYe3EOc5X2hqacWY7uuyV0BwonTt5SapSu', 'test7'),
    (8, 'User8','$2a$10$iWlXVjsSU9.X0oxEjUwyYe3EOc5X2hqacWY7uuyV0BwonTt5SapSu', 'test8'),
    (9, 'User9', '$2a$10$iWlXVjsSU9.X0oxEjUwyYe3EOc5X2hqacWY7uuyV0BwonTt5SapSu','test9'),
    (10, 'User10','$2a$10$iWlXVjsSU9.X0oxEjUwyYe3EOc5X2hqacWY7uuyV0BwonTt5SapSu', 'test10');

Technology

• Spring Boot 3.0.2
• Java 17
• Spring JDBC 6
• Hibernate 6
• Jakarta 3.1
• Docker
• Maven 
• IntelliJ IDEA

SQL Injection Attack Samples

Line Comments Sample SQL Injection Attacks

• Spring's JdbcTemplate:  Simple query to find By Name And Id, with concat and string replace.

 String sql = "SELECT  * from customers where name ='"+name+"' AND id="+id;
 var list = jdbcTemplate.query(sql,   (rs, rowNum) ->
             Customer.builder()
             .id(rs.getInt("id"))
            .name(rs.getString("name"))
            .build()
);

If we send these parameters:

var  name = "Henry' -- '";
var id = 1;

Should  return one record, however return all lists with name are (Henry). because the original query was changed this way  [SELECT  * from customers where name ='Henry' -- '' AND id=1].

If we use replace, the same thing happens.

String sql = "SELECT  * from customers where name =:name AND id=:id";
        sql = sql.replace(":name", name).replace(":id",String.valueOf(id));
        var list = jdbcTemplate.query(sql,   (rs, rowNum) ->
                Customer.builder()
                        .id(rs.getInt("id"))
                        .name(rs.getString("name"))
                        .build()
        );

If we send these parameters:

var  name = "'Henry' -- ";
var id = 1;

Should return one record, however return all lists with name are (Henry). because the original query was changed this way  [SELECT  * from customers where name ='Henry' --  AND id=1].

[
{
"id":1,
"name":"Henry"
},
{
"id":2,
"name":"Henry"
},
{
"id":3,
"name":"Henry"
}
...........

• Hibernate 6:  Simple query to find By Name And Id, with concat and string replace.

String sql = "SELECT e FROM customers e where name='" + name + "'AND id="+id;
var list = entityManager
           .createQuery(sql, Customer.class)
          .getResultList();

If we send these parameters:

var  name = "'Henry' -- ";
var id = 1;

Awesome, Hibernate convert original query this way. 

Hibernate: select c1_0.id,c1_0.name from customers c1_0 where c1_0.name=('Henry'--'') and c1_0.id=1

And return one record. 

[
{
"id":1,
"name":"Henry",
"password":"124",
"lastname":"test1"
}
]

If we use replace:

String sql = "SELECT  e from customers e where name =:name AND lastname =:lastname";
        sql = sql.replace(":name", name).replace(":lastname",lastname);
        System.out.println(sql);
        var list = entityManager
                .createQuery(sql, Customer.class)
                .getResultList();

If we send these parameters:

var name = "'Henry' -- ";
var lastname = "'test1'";

Hibernate convert original query this way: [SELECT  e from customers e where name ='Henry' --  AND lastname ='test1']

However Hibernate not permit the query, it will be send next error:  org.hibernate.query.sqm.ParsingException

Bypassing Login Screens (SMO+)

SQL Injection 101, Login tricks

Spring's JdbcTemplate: Simple query to find By Name And password.
String sql = "SELECT  * from customers where name ='"+name+"' AND password='"+password+"'";
        var list = jdbcTemplate.query(sql,   (rs, rowNum) ->
                Customer.builder()
                        .id(rs.getInt("id"))
                        .name(rs.getString("name"))
                        .build()
        );

        return list;

If we send these parameters: (If we use .trim() to remove whitespace, some values in SQL injection don't work. however the good practice is the data should be properly sanitized).

/** No works
 Henry' /*
 Henry' or 1=1 /*
 * */

// SQL injection Ok
//var name = "Henry' -- ";
//var name = "Henry' # ";
//var name = "Henry' or 1=1 -- ";
//var name = "Henry' or 1=1 # ";
var name = "Henry' or '1'='1 --";
var pass = "123";

Should return one record, however return all lists with name are (Henry). 

[

1. {
   • "id":1,
   • "name":"Henry"
     
   },
2. {
   • "id":2,
   • "name":"Henry"
     
   },
3. {
   • "id":3,
   • "name":"Henry"
     
   }

]

• Hibernate 6:  Simple query to find By Name And password.

var  list = entityManager
                .createQuery("SELECT e FROM customers e where name='" + name + "' AND password='"+pass+"'", Customer.class)
                .getResultList();

If we send these parameters:

/** No works
 Henry' --
 Henry' #
 Henry'/*
 Henry' or 1=1 --
 Henry' or 1=1 #
 Henry' or 1=1 /*
* */

var name = "Henry' or '1'='1 --"; 
var pass = "123";

Should return one record, however return all lists with name are (Henry). 

[
{
"id":1,
"name":"Henry"
},
{
"id":2,
"name":"Henry"
},
{
"id":3,
"name":"Henry"
}
]

Boolean SQL Injection Attacks

Spring's JdbcTemplate:  Simple query to find By Name.

  String sql = "SELECT  * from customers where name ='"+name+"'";
        var list = jdbcTemplate.query(sql,   (rs, rowNum) ->
                Customer.builder()
                        .id(rs.getInt("id"))
                        .name(rs.getString("name"))
                        .build()
        );

        return list;

If we send this parameter:

var name = "abc' or '1'='1";

Should not return any  record, however return all lists.
[
{
"id":1,
"name":"Henry"
},
{
"id":2,
"name":"Henry"
},
{
"id":3,
"name":"Henry"
}
...........
 
Hibernate:  Simple query to find By Name.
   var  list = entityManager
                .createQuery("SELECT e FROM customers e where name='" + name + "'", Customer.class)
                .getResultList();

If we send this parameter:

var name = "abc' or '1'='1";

Should not return any  record, however return all lists.
[
{
"id":1,
"name":"Henry"
},
{
"id":2,
"name":"Henry"
},
{
"id":3,
"name":"Henry"
}
...........

Prevent SQL Injection

• Parameterized queries, also known as prepared statements, use placeholders to represent values in a query. The actual values are then passed as parameters to the query, separately from the SQL code. This makes it much more difficult for an attacker to inject malicious code into a query.

For e.g.

Spring's JdbcTemplate

 String sql = "SELECT  * from customers where name =? AND id=?";
        var list = jdbcTemplate.query(sql,   (rs, rowNum) ->
                Customer.builder()
                        .id(rs.getInt("id"))
                        .name(rs.getString("name"))
                        .build()
        ,new Object[]{name, id});

        return list;

  String sql = "SELECT  * from customers where name =:name AND id=:id";

        MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
        mapSqlParameterSource.addValue("name", name);
        mapSqlParameterSource.addValue("id", id);


        var list = namedParameterJdbcTemplate.query(sql, mapSqlParameterSource,  (rs, rowNum) ->
                        Customer.builder()
                                .id(rs.getInt("id"))
                                .name(rs.getString("name"))
                                .build()
                );

        return list;

Hibernate 6

 var list = entityManager
                .createQuery("SELECT e FROM customers e where name=:name AND password=:password", Customer.class)
                  .setParameter("name", name)
                  .setParameter("password", pass)
                .getResultList();

• keep your software up to date to ensure that any security vulnerabilities are patched.

• Consider using a web application firewall (WAF). A WAF or web application firewall helps protect web applications by filtering and monitoring HTTP traffic between a web application and the Internet. It typically protects web applications from attacks such as cross-site forgery, cross-site-scripting (XSS), file inclusion, and SQL injection, among others.

• Use of Properly Constructed Stored Procedures

• Allow-list Input Validation

• For more check SQL Injection Prevention Cheat Sheet.

Test

Spring JDBC main classes:

1. BooleanAttacksJDBCTemplate
2. BypassingLoginAttacksJDBCTemplate
3. LineCommentsAttacksJDBCTemplate

Hibernate 6 main classes:

• BooleanAttacksHibernate
• BypassingLoginAttacksHibernate
• LineCommentsAttacksHibernate

Source Code

Here on GitHub.

References.

https://www.invicti.com/blog/web-security/sql-injection-cheat-sheet/

https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html

https://www.cloudflare.com/learning/ddos/glossary/web-application-firewall-waf/