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();

What happen if String is used for Number data type in MySQL?

String for Number datatype in MySQL

For Number data type, MySQL parse String value character-by-character until it finds character other than number.

Queries
SELECT * FROM users WHERE id = '12 or 1=1'; \\ 1 -> OK, 2 -> OK,  -> false-and-return
> SELECT * FROM users WHERE id = 12;

SELECT * FROM users WHERE id = '123XYZ'; \\ 1 -> OK, 2 -> OK, 3 -> OK, X -> false-and-return
> SELECT * FROM users WHERE id = 123;

SELECT * FROM users WHERE id = '1&23XYZ'; \\ 1 -> OK, & -> false-and-return
> SELECT * FROM users WHERE id = 1'

How to sum values from List, Set and Map using stream in Java 8?



Following examples shows how you can use java 8 Stream api to do summation of Integer, Double and Long in List, Set and Map.

Source code (Item.java)
public class Item {

    private Long id;
    private String name;
    private Double price;
    
    public Item(String name, Double price) {
        this.name = name;
        this.price = price;
    }
    
    /* getter - setter */

    public Double getPrice() {
        return price;
    }

    public void setPrice(Double price) {
        this.price = price;
    }
}

List
  • Summation of Integers in List.
  • Summation of Price from List of beans(Item).

Source code (ListStreamSum.java)
import com.javaquery.bean.Item;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * Summation of element in List using Stream api in java 8.
 *
 * @author javaQuery
 * @date 17th October, 2016
 * @Github: https://github.com/javaquery/Examples
 */
public class ListStreamSum {

    public static void main(String[] args) {
        /* Summation of Integers in List */
        List<Integer> integers = new ArrayList<>(Arrays.asList(10, 20, 30));

        Integer integerSum = integers.stream().mapToInt(Integer::intValue).sum();
        System.out.println("summation: " + integerSum);

        /* Summation when you have list of beans */
        Item motoG = new Item("MotoG", 100.12);
        Item iPhone = new Item("iPhone", 200.12);

        List<Item> listBeans = new ArrayList<>(Arrays.asList(motoG, iPhone));

        Double doubleSum = listBeans.stream().mapToDouble(Item::getPrice).sum();
        System.out.println("summation: " + doubleSum);
    }
}

Output
summation: 60
summation: 300.24

Set
  • Summation of Integers in Set.
  • Summation of Price from Set of beans(Item).

Source code (SetStreamSum.java)
import com.javaquery.bean.Item;
import java.util.Arrays;
import java.util.HashSet;
import java.util.Set;

/**
 * Summation of element in Set using Stream api in java 8.
 *
 * @author javaQuery
 * @date 17th October, 2016
 * @Github: https://github.com/javaquery/Examples
 */
public class SetStreamSum {

    public static void main(String[] args) {
        /* Summation of Integers in Set */
        Set<Integer> integers = new HashSet<>(Arrays.asList(10, 20, 30));

        Integer integerSum = integers.stream().mapToInt(Integer::intValue).sum();
        System.out.println("summation: " + integerSum);

        /* Summation when you have set of beans */
        Item motoG = new Item("MotoG", 100.12);
        Item iPhone = new Item("iPhone", 200.12);

        Set<Item> listBeans = new HashSet<>(Arrays.asList(motoG, iPhone));

        Double doubleSum = listBeans.stream().mapToDouble(Item::getPrice).sum();
        System.out.println("summation: " + doubleSum);
    }
}

Output
summation: 60
summation: 300.24

Map
  • Summation of Integers in Map as a value.
  • Summation of Integers in List as a value.
  • Summation of Price from List of beans(Item) in Map.

Source code (MapStreamSum.java)
import com.javaquery.bean.Item;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Summation of element in Map using Stream api in java 8.
 *
 * @author javaQuery
 * @date 17th October, 2016
 * @Github: https://github.com/javaquery/Examples
 */
public class MapStreamSum {

    public static void main(String[] args) {
        /* Summation of Integers in Map */
        Map<String, Integer> integers = new HashMap<>();
        integers.put("A", 10);
        integers.put("B", 20);
        integers.put("C", 30);

        Integer integerSum = integers.values().stream().mapToInt(Integer::intValue).sum();
        System.out.println("summation: " + integerSum);

        /* Summation when you have List/Set in Map */
        Map<String, List<Integer>> listInMap = new HashMap<>();
        listInMap.put("even_numbers", new ArrayList<>(Arrays.asList(2, 4, 6)));

        integerSum = listInMap.values().stream()
                .flatMapToInt(list -> list.stream().mapToInt(Integer::intValue))
                .sum();
        System.out.println("summation: " + integerSum);

        /* Summation when you have List/Set of beans in Map */
        Item motoG = new Item("MotoG", 100.12);
        Item iPhone = new Item("iPhone", 200.12);

        List<Item> items = new ArrayList<>(Arrays.asList(motoG, iPhone));

        Map<String, List<Item>> itemMap = new HashMap<>();
        itemMap.put("items", items);

        Double doubleSum = itemMap.values().stream()
                .flatMapToDouble(list -> list.stream().mapToDouble(Item::getPrice))
                .sum();
        System.out.println("summation: " + doubleSum);
    }
}

Output
summation: 60
summation: 12
summation: 300.24

What is the difference between List, Set and Map in Java?

List , Set , Map in Java8

List interface
An ordered collection (also known as a sequence).

Characteristics
  • Element added from 0th index, 1st, 2nd, ... nth (Sequentially).
  • List allows to insert/update/read element at specific index.
  • List allows duplicate values.
  • It maintains insertion position (1st point).
  • It allows null value.

ArrayList is one of the popular implementation of List used by programmers.

Examples


Set interface
Collection which don't allows duplicate values.

Characteristics
  • Unlike List, Set will not allow index based insert/update/read.
  • Set doesn't allow duplicate. e1 and e2 such that e1.equals(e2)
  • It doesn't maintain insertion position (unordered collection). However you can maintain order using LinkedHashSet.
  • It allows null value.

HashSet is one of the popular implementation of Set used by programmers.

Examples


Map interface
Collection to hold [KEY, VALUE] data.

Characteristics
  • Unlike List, Map will not allow index based insert/update/read.
  • Map doesn't allow duplicate KEY.
  • It doesn't maintain insertion position (unordered collection). However you can maintain order using LinkedHashMap.
  • It allows one null KEY and n number of null VALUE.

HashMap us one of the popular implementation of Map used by programmers.



Collection Framework examples in Java 8

How to convert List of data to Set of data and vice versa in Java?

Following excerpt shows how you can convert List<T> to Set<T> and Set<T> to List<T> in java.

Source code (ListToSet.java)
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

/**
 * List to Set example.
 *
 * @author javaQuery
 * @date 7th October, 2016
 * @Github: https://github.com/javaquery/Examples
 */
public class ListToSet {

    public static void main(String[] args) {
        /* Create list of string */
        List<String> strings = new ArrayList<String>();
        strings.add("A");
        strings.add("B");

        Set<String> stringSet = new HashSet<String>(strings);
        /**
         * new HashSet(Collection<? extends E> c) 
         * We created Set of String so we can initialize HashSet using any collection that extends String.
         */
        
        for (String string : stringSet) {
            System.out.println(string);
        }
    }
}
You would like to read How to Initialize List in declaration?.

Source code (SetToList.java)
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

/**
 * Set to List example.
 *
 * @author javaQuery
 * @date 7th October, 2016
 * @Github: https://github.com/javaquery/Examples
 */
public class SetToList {

    public static void main(String[] args) {
        /* Create set of string */
        Set<String> strings = new HashSet<String>();
        strings.add("A");
        strings.add("B");

        List<String> list = new ArrayList<>(strings);
        /**
         * new ArrayList(Collection<? extends E> c) 
         * We created List of String so we can initialize ArrayList using any collection that extends String.
         */
        
        for (String string : list) {
            System.out.println(string);
        }
    }
}
You would like to read How to Initialize Set in declaration?.

Output
A
B

What is difference between method Overloading and Overriding in Java?

Its one of the popular Interview question asked to Java developer with 0 - 1 year experience. We'll understand method overloading followed by method overriding.

Method Overloading
Same method name but different parameters in class.

Source code (MethodOverload.java)
/**
 * Example of method overloading
 *
 * @author javaQuery
 * @date 5th October, 2016
 * @Github: https://github.com/javaquery/Examples
 */
public class MethodOverload {

    public int x = 0;

    /**
     * Method overloading - Same method name(getAndIncrement) with no parameter.
     * Increment x by 1.
     *
     * @return
     */
    public int getAndIncrement() {
        x = x + 1; // you can also use like x = getAndIncrement(1);
        return x;
    }

    /**
     * Method overloading - Same method name(getAndIncrement) with parameter.
     * Increment x by given count(value).
     *
     * @param add
     * @return
     */
    public int getAndIncrement(int add) {
        x = x + add;
        return x;
    }

    public static void main(String[] args) {
        MethodOverload methodOverload = new MethodOverload();
        System.out.println("calling 'getAndIncrement()': " + methodOverload.getAndIncrement());
        System.out.println("calling 'getAndIncrement(int add)': " + methodOverload.getAndIncrement(2));
    }
}
Output
calling 'getAndIncrement()': 1
calling 'getAndIncrement(int add)': 3

Method Overriding
In case of class(sub-class: AddAndDisplay) extends/implements other class/interface (super-class: Addition).
Method with same name and parameter created in sub-class.
Note: Overriding is used when you don't want to use default implementation provided by parent class and will write your own piece of implementation in overridden method.

Source code (Addition.java)
/**
 * Example of method overriding.
 *
 * @author javaQuery
 * @date 6th October, 2016
 * @Github: https://github.com/javaquery/Examples
 */
public class Addition {

    /**
     * Add two numbers and return the result.
     * @param x
     * @param y
     * @return 
     */
    public int add(int x, int y) {
        return x + y;
    }
}

Source code (AddAndDisplay.java)
/**
 * Example of method overriding.
 *
 * @author javaQuery
 * @date 6th October, 2016
 * @Github: https://github.com/javaquery/Examples
 */
public class AddAndDisplay extends Addition {

    /**
     * Method override of class Addition.
     * We're overriding 'add' method because we want to print
     * values of 'x' and 'y' to console along with the 'result'.
     * @param x
     * @param y
     * @return
     */
    @Override
    public int add(int x, int y) {
        int result = x + y;
        System.out.println("(x:" + x + ",y:" + y + ")");
        System.out.println("result: " + result);
        return result;
    }
}

Source code (MethodOverride.java)
/**
 * Example of method overriding.
 *
 * @author javaQuery
 * @date 6th October, 2016
 * @Github: https://github.com/javaquery/Examples
 */
public class MethodOverride {
    public static void main(String[] args) {
        /* Add two number using super class Addition's add method */
        Addition addition1 = new Addition();
        int result = addition1.add(20, 30);
        System.out.println("calling Addition.add(int x, int y)");
        System.out.println("result: " + result);
        System.out.println("********************************************");
        
        System.out.println("calling AddAndDisplay.add(int x, int y)");
        Addition addition2 = new AddAndDisplay();
        /**
         * Notice: We created object of 'Addition' using 'AddAndDisplay'.
         * 
         * So when we call Addition.add(int x, int y) method it'll execute
         * AddAndDisplay.add(int x, int y) method because 'add' is 
         * overridden. 
         * 
         * It'll hide the superclass Addition's add method and execute
         * subclass AddAndDisplay's method.
         */
        addition2.add(10, 20);
    }
}
Output
calling Addition.add(int x, int y)
result: 50
********************************************
calling AddAndDisplay.add(int x, int y)
(x:10,y:20)
result: 30

Examples of Java 8

Java 8
Using IntStream over for loop in Java 8

How to iterate over stream and increment index value in Lambda Expression?
Many of you having trouble for increment index value inside Lambda expression because of Local variable index defined in an enclosing scope must be final or effectively final. There is an alternative for that...

How to get List of Properties (String, Double, Integer) from List of beans in Java 8?
This little excerpt shows How you can fetch List<String>, List<Double>, List<Integer>, List<Object>, etc... from List<Beans>. And it also explains the use of map method in stream.

How to convert List to Map in Java 8 using Stream API?
Code snippet demonstrate converting List<T> to Map<K,V>.

Example of Collectors minBy in Lambda expression
This static method from package java.util.stream and class Collectors used to find maximum value from given Collection. This method takes Comparator as an argument.

Example of Collectors maxBy in Lambda expression
This static method from package java.util.stream and class Collectors used to find maximum value from given Collection. This method takes Comparator as an argument.

Example of distinct in Java 8
Returns a stream consisting of the distinct elements (according to Object.equals(Object)) of this stream.

How flatMap works in Java 8 with Example
Returns a stream consisting of the results of replacing each element of this stream with the contents of a mapped stream produced by applying the provided mapping function to each element. Each mapped stream is closed after its contents have been placed into this stream. (If a mapped stream is null an empty stream is used, instead.)



Example of mapToInt in Java 8
Returns an IntStream consisting of the results of applying the given function to the elements of this stream.

Example of mapToLong in Java 8
Returns a LongStream consisting of the results of applying the given function to the elements of this stream.

Example of mapToDouble in Java 8
Returns a DoubleStream consisting of the results of applying the given function to the elements of this stream.

How to sum values from List, Set and Map using stream in Java 8?