SQL Injection security Vulnerability

This article i will be covering SQL Injection security vulnerability.

What is SQL Injection?
SQL Injection means, modifying application query in such a way that it tweaks the actual query results. With this issue present in application Hacker can easily manipulate the query and can gain access to application data and functionality.

How to address this issue?
There are many things which application developer can take care during the development phase which can avoid these issues.

1. Usage of PreparedStatement while doing database operations.
2. Usage of CallableStatement which is used to call database Procedures. 
3. Input validations and Data Encoding.
4. Database related constraints like, usage of proper grant, privilege, views etc.   

Below is simple example on how the attacker can use this vulnerability to exploit system.

Lets assume that in the web application there is login screen which prompts user to enter user credentials. 

In the application to validate the user following query is used.

SELECT USERNAME FROM USER WHERE PASSWORD='ABCD' AND USERNAME='ABCD' ;

and in java code this query is prepared using string concatenation operation something like shown below.

public String validateUser(String userName, String password){
 Statement stmt=null;
 String query="SELECT USERNAME FROM USER WHERE PASSWORD='"+password+"' AND USERNAME='"+userName+"'";
 stmt=con.createStatement();
 ResultSet res= stmt.executeQuery(query);

}

Now to exploit this limitation in the code,hacker can easily manipulate the query by passing additional query while login from the application login page and he can access the application without having valid user account. This can be done as shown below.

Application will receive the information from UI and it will prepare following query based on above java code.

SELECT USERNAME FROM USER WHERE PASSWORD='ABCD' AND USERNAME='abcd' OR 'A'='A' ;

Once above query is executed by database, even if the hacker enters the wrong user name and password, he is able to get into the system, as the condition "OR 'A"='A"" always returns true. This value hacker passed from UI and application blindly accepts this value and prepares the query.

This is one of the kind of exploitation which hacker can do if application is vulnerable to SQL Injection.

to address this issue one of the simple solution is to migrate to PreparedStatement. But there are situations where this migration is not very straight forward when application team doesnot want to modify legacy codes. Then other approaches can be taken up like escaping/encoding the user input and append those value in sql queries.

Prepared Statement Approach:
Below is the sample code which will remove the SQL injection issue. There are ORM framework available which can be used in the application and these framework takes care of all these standards. like, MyBatis, Hibernate, Spring DAO etc.

public String validateUser(String userName, String password){
 String query="SELECT USERNAME FROM USER WHERE PASSWORD=? AND USERNAME=?";
 PreparedStatement stmt=connection.prepareStatement(query);
 stmt.setString(1,password);
 stmt.setString(2,userName);
 ResultSet res= stmt.executeQuery();
}

I hope this explanation helps to understand the SQL injection issue.