Tuesday, February 7, 2023

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].

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


  • 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. 
[
  1. {
    • "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). 
[
  1. {
    • "id":1,
    • "name":"Henry"
    },
  2. {
    • "id":2,
    • "name":"Henry"
    },
  3. {
    • "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.
[
  1. {
    • "id":1,
    • "name":"Henry"
    },
  2. {
    • "id":2,
    • "name":"Henry"
    },
  3. {
    • "id":3,
    • "name":"Henry"
    }
  4. ...........
 
  • 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.
[
  1. {
    • "id":1,
    • "name":"Henry"
    },
  2. {
    • "id":2,
    • "name":"Henry"
    },
  3. {
    • "id":3,
    • "name":"Henry"
    }
  4. ...........


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


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/













































at





No comments:
  


































        

      









Subscribe to:
Posts (Atom)









Virtual Threads in Java 21: Simplified Concurrency for Modern Applications



  With Java 21, Virtual Threads have redefined how we approach concurrency, offering a lightweight and efficient way to handle parallel and ...