Showing posts with label Prepared Statement. 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();

Prepared Statement in Microsoft SQL Server

Prepared Statement in Microsoft SQL Server

Database prepares execution plan(steps) for each query fired on it. Preparing execution plan for each query will increase execution cost. To reduce cost on creating execution plan for each query database provides Prepared Statements.

Prepared Statement
Prepared Statements are pre-cached queries stored in database. Prepared Statement used when same query used with different values. Preparing execution plan in advance for particular query will increase query performance.

When, Prepared Statement?
Multiple occurrence of same query with different values will lead to Prepared Statement. Lets understand following queries...
/* Database will prepare two execution plan for following queries */
SELECT * FROM user_master WHERE user_name = 'vicky.thakor'
SELECT * FROM user_master WHERE user_name = 'chirag.thakor'

/* Prepared Statement */
SELECT * FROM user_master WHERE user_name = :param1
- First two queries are simple SQL statement, both are same except its value. For this queries database will prepare two execution plan.
- Third query will be pre-cached query and can accept n number of values and database don't have to prepare execution plan multiple times. Third query template/Prepared Statement used for above two query.

Rule for execution plan
Database will consider space, comma, special symbol while creating execution plan either its Prepared Statement or Simple SQL Query. Database will prepare different execution plan for following queries which seems similar to us but not for database.
/* Execution Plan 1; Comment is part of query*/
/* Native Query */ SELECT * FROM user_master WHERE user_name = 'vicky.thakor'

/* Execution Plan 2; Comment is part of query, We just changed 'q' in comment */
/* Native query */ SELECT * FROM user_master WHERE user_name = 'vicky.thakor'

/* Execution Plan 3 */
SELECT * FROM user_master WHERE user_name = 'vicky.thakor'

/* Execution Plan 4 (Prepared Statement)*/
SELECT * FROM user_master WHERE user_name = :param1

/* Execution Plan 5 (Prepared Statement); Comment is part of query */
/* Prepared Statement */SELECT * FROM user_master WHERE user_name = :param1
So far you noticed how importance of query writing and Prepared Statement. We all are using Prepared Statement from different programming languages, Program just prepare one of the above Prepared Statement and maintain same query structure for all database execution.

Now we will see how you can use Prepared Statements while writing Stored Procedures, functions in Microsoft SQL Server.

Step 1: Clear all available execution plan/Prepared Statements from cache.
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Step 2: We will see the execution plan for simple SQL Query
SELECT * FROM user_master WHERE user_name = 'vicky.thakor'
SELECT * FROM user_master WHERE user_name = 'chirag.thakor'

Step 3: Check your execution plan in cache. You will find two execution plan in it.
SELECT usecounts, size_in_bytes, cacheobjtype, text 
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype != 'Parse Tree'

Step 4: Now again clear all available execution plan/Prepared Statements from cache.
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Step 5: Now we will Prepare Statement in Database. Execute following query two times with different values. Change your table name, column name and value.
EXEC sp_executesql
@statement = N'SELECT * FROM user_master WHERE user_name = @param1',
@parameters = N'@param1 varchar(100)',
@param1 = N'vicky.thakor'

Step 6: Check your execution plan in cache. You will notice that same execution plan used twice.
SELECT usecounts, size_in_bytes, cacheobjtype, text 
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype != 'Parse Tree'

PreparedStatement : Best practice of Database in Java


Database design

What is PreparedStatement?
Its an object that represents a precompiled SQL statement. It allows to execute the same or similar database statements repeatedly with high efficiency. Its template for your query that you use more than one time in your application.  

Why you need to use PreparedStatement over SQL statement?
  • SQL Statement : Create query model each time you fire the query in database. It will consume time to create model of your query. 
select * from user_table where(uid=1); / Creates model for query where uid=1
select * from user_table where(uid=2); / Creates model for query where uid=2
select * from user_table where(uid=1); / Creates model for query where uid=3
  • PreparedStatement : Create query model template on first time you fire the query in database. It'll use template already available in database. It don't need to create model when you change the uid parameter.
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "root", "root");
PreparedStatement userRecord_stmt = con.prepareStatement("select * from user_table where(uid=?);");
//Creates global model and used in future whether you use 1,2...n
userRecord_stmt.setInt(1,103);
ResultSet userRecord_rs = userRecord_stmt.executeQuery();
//next you know what to do...

Bind data in one query when bulk insert operation required.
Firing insert query each time when you need to insert 1000 or say more records its really time consuming. Best database practice is prepare one query that contains 1000 records in one query to insert. 
// Worst practice
insert into notification values(1,"hello world!"); // 1 request sent over network
insert into notification values(2,"hello world!"); // 1+1 request sent over network
insert into notification values(3,"hello world!"); // 1+1+1 request sent over network

// Best practice
insert into notification (a,b) values (1,"hell world!"),(2,"hell world!"),(3,"hell world!"); // Only 1 request sent over network

How to bind data in PreparedStatement?
Let me give you code snippet to perform bulk insert using PreparedStatement. 
PreparedStatement bulk_stmt = con.prepareStatement("insert into notification values(?,?);");
    for (int i = 0; i < 10; i++) {
      bulk_stmt.setInt(1, (i + 1));
      bulk_stmt.setString(2, "hello world!");
      bulk_stmt.addBatch();
    }
bulk_stmt.execute();

Find bulk data using "where column_name in( )"
Getting bulk information from table using single column comparison. 
  • Scenario : Find user details for the following uid : 1, 42, 33, 40, 103
// Worst practice 
select * from user_table where(uid=1);
select * from user_table where(uid=42);
select * from user_table where(uid=33);
// ... and so on

// Best practice
select * from user_table where uid in(1,42,33,40,103) ORDER BY uid;