Wednesday, June 19, 2013

SQL injection in Java-JDBC

Hi All,

Welcome to Java-recent.

In this post we will discuss about SQL injection and its security threats with an example.

SQL injection :-
  • means injecting unwanted/undesired sql statements into our application queries,which will result unexpected behavior or pose security threats like displaying data that is not supposed to be shown to end user etc.
  • This happens in data driven web applications irrespective of technology used(Java,.Net,PHP etc)
  • SQL injection attack(SQLA) is considered one of the top 10 vulnerabilities from 2007- 2010

There are primarily 3-4 ways of implementing SQLA.
  • Incorrectly filtered escape characters
    • Passing escape characters to application query for example from a form input field
      Ex:- SELECT mail FROM COMPANY WHERE name = '' OR 1=1 -- -' AND id = '';
  This type of query may get formed due to sql injection
  • Providing comments like – - ,/* in middle of the query which will comment out rest of the query
          Will show live implementation by an example
  • Incorrect type handling
    • Passing different data type values to the query,this will happen in cases where we retrieve request parameter directly and place in the query which helps in passing escape characters to query

  • Blind SQL injection
    • Blind SQL Injection is used when a web application is vulnerable to an SQL injection but the results of the injection are not visible to the attacker
Example :- Suppose we have a request URL as below http://localhost:8083/AjaxExample/DesignNumeric?name=Google

The request parameter can be modified by a hacker to know the information about data base server or can run unwanted queries which can fetch undesired data. This type of injection causes performance issues also,sometimes there may be a fault query which may take more time and consume application resources

For detailed info on SQL injection refer SQL injection

Here I will explain about incorrectly filtered escape characters with an example

Applications/Software used for below example
  • Orcale as database
  • Eclipse IDE
  • Apache Tomcat 7
  • ojdbc14.jar for connecting to Oracle DB from Java
  • Table Name :- COMPANY
      SNO
    • NAME
      MAIL
      1
    • Google
    • google@gmail.com
      2
    • Yahoo
    • yahoo@yahoo.com
      3
    • Microsoft
    • microsoft@live.com
  • Connection String :-jdbc:oracle:thin:@localhost:1521:xe","system","admin"
  • Html page :- Submit.html takes user name as input
  • Servlet :- Design.java used to connect to database and print the Email Id of the user submitted in the Submit.html page
Source code :-

Submit.html
<form action="Design">
Enter name <input name="name"><br>
<input type="submit" value="click here to get details"
</form>

Design.java :-
@WebServlet("/Design")
public class Design extends HttpServlet {
private static final long serialVersionUID = 1L;

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String uName=request.getParameter("name");
Connection conn=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
//Creating connection object
conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","admin");
Statement statement=conn.createStatement();
ResultSet rs= statement.executeQuery("select mail from COMPANY where name="+"'"+uName+"'");//Executing query
//Printing Resultset data
PrintWriter out=response.getWriter();
out.println("<head><body>");
while(rs.next())
{
String emailID= rs.getString("MAIL");//column name in COMPANY table
out.println("<h4>"+emailID+"</h4><br>");
}
out.println("</body></head>");
}catch (ClassNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally
{
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}



Explanation :-

  • In Design.Java servlet we are connecting to data base and fetching mail Id of the user name entered in Submit.html page
  • request.getParameter("name");
    • Is used to get value of the name entered in text box
  • statement.executeQuery("select mail from COMPANY where name="+"'"+uName+"'");
    • The above statement processes the query and stores data in ResultSet

Scenario1:-
  • User enters Google in the Submit.html

  • The resultant query that gets generated is
    select mail from COMPANY where name='Google',
  • This will give expected output as google@gmail.com
Now lets see the Scenario where SQL injection happens

Scenario 2 :-
  • User now enters input which has special characters like ' OR '1'='1 in the text box

  • The query that gets generated is select mail from COMPANY where name=' ' OR '1'='1'
  • Here in the where clause user has used '1'='1' which will always be true,so what will be output of below query? It will print all the mailId's,which is a pure security issue by posing unrelated info to the end user

google@gmail.com


yahoo@yahoo.com


microsoft@live.com

Clearly we have seen how a hacker can get unintended information,there are many ways to minimize or stop SQL attacks. Needs a detailed post for them,so will explain in my coming posts. A brief note about them is below
  • Setting access permissions on DB system tables,view etc.
  • Using PreparedStatement, parametrization of query values
  • Parsing/filtering escape characters
  • Exact type checking before passing value to a query

Happy Learning

Please provide your valuable comments on this article and share it across your network.




Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Like and Share