Showing posts with label Criteria. Show all posts

What is SQL Injection and how to avoid it in Java?

SQL Injection
Its a technique where attacker try to alter(modify/change) your SQL query using input parameters.
SQL injection may leads to unexpected transaction (i.e select, update, delete, etc...). We'll see the basic SQL injection examples and later on see how to prevent it using Prepared Statement, Hibernate Criteria and HQL.

Source code (SQLInjection.java)
import java.util.ArrayList;
import java.util.List;

/**
 * Example of SQL injection.
 * @author javaQuery
 * @date 8th November, 2016
 * @Github: https://github.com/javaquery/Examples
 */
public class SQLInjection {

    public static void main(String[] args) {
        /* You are getting parameter value from web page or other user input */
        String parameter = "12"; // normal condition
        new SQLInjection().getUser(parameter);

        /**
         * SQL injection using parameter value. 
         * - If user can change parameter in url, use some script, etc...
         */
        parameter = "12 or 1 = 1";
        new SQLInjection().getUser(parameter);
    }

    /**
     * Get user from database.
     * @param id
     * @return 
     */
    public List<Object> getUser(String id) {
        List<Object> result = new ArrayList<Object>();

        String sql = "SELECT * FROM users WHERE id = " + id + ";";
        System.out.println("SQL Query: " + sql);

        /* prepare connection and execute query */
        return result;
    }
}
Output
In following queries, 1st query is valid and return result as expected but when 2nd query is executed it'll select all users from database and that may leads to unexpected behavior of your system.
SQL Query: SELECT * FROM users WHERE id = 12;
SQL Query: SELECT * FROM users WHERE id = 12 or 1 = 1;
In this example I used user table and this table contains very few records 1k, 10k, etc... but
What if you are selecting data from table which contains millions of records? - Answer is SYSTEM CRASH

Other ways of SQL injection
Consider you are getting value of username and password from parameter into param_username and param_password.
String param_username = "\" or \"\"=\"";
String param_password = "\" or \"\"=\"";

//SQL Injection:
String sql = "SELECT * FROM users WHERE username = \"" + param_username + "\" AND password = \"" + param_password +"\"";
System.out.println(sql);
//OUTPUT: SELECT * FROM users WHERE username = "" or ""="" AND password = "" or ""=""

============================================
String param_userid = "123; DROP TABLE messages;";

//SQL Injection:
String sql = "SELECT * FROM users WHERE id = " + param_userid;
System.out.println(sql);
//OUTPUT: SELECT * FROM users WHERE id = 123; DROP TABLE messages;

First and foremost way: Handle Datatypes
For the sake of simplicity developers don't handle data types at coding. In above code I used String as input parameter in method getUser but should've use Integer/Long. If I used Integer or Long then I've to convert String => 12 or 1 = 1 to Integer/Long => Not Valid Number. It'll prevent SQL Injection.


Avoid SQL Injection using Prepared Statement
Prepared Statement doesn't append values in your SQL query rather it provide SQL query and parameter values separately to database. Database will take care of every parameter value for escape character, special character and every other precaution needed.

Source code (PreparedStatementExample.java)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Example of SQL injection.
 * @author javaQuery
 * @date 8th November, 2016
 * @Github: https://github.com/javaquery/Examples
 */
public class PreparedStatementExample {
    public static void main(String[] args) {
        new PreparedStatementExample().getUser("12");
    }
    
    /**
     * Get user from database.
     * @param id
     * @return 
     */
    public List<Object> getUser(String id) {
        List<Object> result = new ArrayList<Object>();

        String sql = "SELECT * FROM users where id = ?;";

        /* prepare connection and execute query */
        try {
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "root", "root");
            PreparedStatement prepareStatement = connection.prepareStatement(sql);
            prepareStatement.setInt(1,Integer.parseInt(id)); // index of ? is '1', perform null/number check for 'id'
            //execute prepared statement
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        return result;
    } 
}
Output
With PreparedStatement only following query can be generated.
SELECT * FROM users where id = 12;
prepareStatement.setInt: 12 or 1 = 1 is passed as value then Integer.parseInt will throw java.lang.NumberFormatException: For input string: "12 or 1=1".
prepareStatement.setString: What happen if String is used for Number data type in MySQL?


Avoid SQL Injection using Hibernate Criteria
Hibernate Criteria internally uses Prepared Statement to execute query.

Source code
String param_id = "12";

Criteria criteria = session.createCriteria(User.class);
/**
 * 'param_id' provided as String but 'id' declared as Integer/Long in User.java
 * So it'll throw exception(java.lang.String cannot be cast to java.lang.Integer) for invalid data type. (SQL injection handled)
 */
criteria.add(Restrictions.eq("id", param_id));
User user = criteria.uniqueResult();

==============================

// valid query
Integer param_id = 12;

Criteria criteria = session.createCriteria(User.class);
criteria.add(Restrictions.eq("id", param_id));
User user = criteria.uniqueResult();

Avoid SQL Injection using HQL
Its same as Hibernate Criteria.

Source code
String param_id = "12";

Query query = session.createQuery("FROM User WHERE id = :param_id");
/**
 * 'param_id' provided as String but 'id' declared as Integer/Long in User.java
 * So it'll throw exception(java.lang.String cannot be cast to java.lang.Integer) for invalid data type. (SQL injection handled)
 */
query.setParameter("param_id", param_id);
query.list();

==============================

// valid query
Integer param_id = 12;

Query query = session.createQuery("FROM User WHERE id = :param_id");
query.setParameter("param_id", param_id);
query.list();

How to get valued query from Hibernate Criteria(Not Logger)?


As per my knowledge there is no library available that allows you get Criteria query with its real values. So I came up with solution that allows you to do that.

Hibernate Assist, Its an open source Hibernate Criteria analysis tool. It has many features one of them is to get Criteria query with its value.

Download Librarywww.javaquery.com/p/hibernateassist.html

Source Code
Criteria criteria = objSession.createCriteria(User.class);
criteria.createAlias("Messages", "Messages");
criteria.createAlias("CreditCard", "CreditCard");
criteria.add(Restrictions.eq("Email", "vicky.thakor@javaquery.com"));
List<User> listUser = criteria.list();

HibernateAssist objHibernateAssist = new HibernateAssist(objSession);
objHibernateAssist.setCriteria(criteria);
String strQuery = objHibernateAssist.getValuedCriteriaQuery();
System.out.println(strQuery);

Output
SELECT this_.id                        AS id0_2_, 
       this_.username                  AS username0_2_, 
       this_.password                  AS password0_2_, 
       this_.email                     AS email0_2_, 
       messages1_.id                   AS id1_0_, 
       messages1_.user_id              AS user2_1_0_, 
       messages1_.message_text         AS message3_1_0_, 
       creditcard2_.id                 AS id2_1_, 
       creditcard2_.user_id            AS user2_2_1_, 
       creditcard2_.credit_card_number AS credit3_2_1_ 
FROM   user_master this_ 
       INNER JOIN message messages1_ 
               ON this_.id = messages1_.user_id 
       INNER JOIN creditcard creditcard2_ 
               ON this_.id = creditcard2_.user_id 
WHERE  this_.email = 'vicky.thakor@javaquery.com' 
Note: I tried to manage almost all cases however if you find for your Criteria its not working please comment your issues.

Hibernate Disjunction with Example

Hibernate Disjunction with Example

Hibernate Disjunction, is used to add multiple condition in SQL query separated by OR clause within brackets. To generate following query using Hibernate Criteria we need to use Disjunction.

Query
select
 this_.id as id0_0_,
 this_.username as username0_0_,
 this_.email as email0_0_ 
from
 user_master this_ 
where
(
 this_.username=? 
 or this_.username=?
)

Source Code
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.Restrictions;

/**
 * Hibernate Conjunction with Example
 * @author javaQuery
 */
public class HibernateDisjunctionExample {

    public static void main(String[] args) {
        /* Create hibernate configuration. */
        Configuration objConfiguration = new Configuration();
        objConfiguration.configure("com\\hibernateassist\\hbm\\hibernate.cfg.xml");

        /* Open session and begin database transaction for database operation. */
        SessionFactory objSessionFactory = objConfiguration.buildSessionFactory();
        Session session = objSessionFactory.openSession();
        
        /* Create criteria */
        Criteria criteria = session.createCriteria(User.class);
        
        /* Create object of Disjunction */
        Disjunction objDisjunction = Restrictions.disjunction();
        /* Add multiple condition separated by OR clause within brackets. */
        objDisjunction.add(Restrictions.eq("Username", "vicky"));
        objDisjunction.add(Restrictions.eq("Username", "thakor"));
        
        /* Attach Disjunction in Criteria */
        criteria.add(objDisjunction);
        
        /* Execute criteria */
        criteria.list();
    }
}

Hibernate Conjunction with Example

Hibernate Conjunction

Hibernate Conjunction, is used to add multiple condition in SQL query separated by AND clause  within brackets. To generate following query using Hibernate Criteria we need to use Conjunction.

Query
select
 this_.id as id0_0_,
 this_.username as username0_0_,
 this_.email as email0_0_ 
from
 user_master this_ 
where
(  
 this_.username=? 
 and this_.username=? 
)

Source Code
Following code will generate above given query.
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Conjunction;
import org.hibernate.criterion.Restrictions;

/**
 * Hibernate Conjunction with Example
 * @author javaQuery
 */
public class HibernateConjunctionExample {

    public static void main(String[] args) {
        /* Create hibernate configuration. */
        Configuration objConfiguration = new Configuration();
        objConfiguration.configure("hibernate.cfg.xml");

        /* Open session and begin database transaction for database operation. */
        SessionFactory objSessionFactory = objConfiguration.buildSessionFactory();
        Session session = objSessionFactory.openSession();
        
        /* Create criteria */
        Criteria criteria = session.createCriteria(User.class);
        
        /* Create object of Conjunction */
        Conjunction objConjunction = Restrictions.conjunction();
        /* Add multiple condition separated by AND clause within brackets. */
        objConjunction.add(Restrictions.eq("Username", "vicky"));
        objConjunction.add(Restrictions.eq("Username", "thakor"));
        
        /* Attach Conjunction in Criteria */
        criteria.add(objConjunction);
        
        /* Execute criteria */
        criteria.list();
    }
}