Java Updates

We all must have been associated with development one way or the other. What I have noticed that in early phases we tend to focus more on correctly developing the business logic, rather than thinking about the BIG Picture.

One such aspect of the 'BIG picture' is SQL-Injection also popularly known as SQLI.

SQL Injection (SQLI) attack is considered one of the top 10 web application vulnerabilities of 2007 and 2010 by the Open Web Application Security Project.

SQLI is a vulnerability in code for database handling which is being caused due to negligence during the development phase of the application, that can cause a lot of damage later on.

SQL Injection also can be of many types, please refer the image below (Courtesy: wikipedia):

Now, lets get back to the theme of this blog:

I will firstly write a code in Java which is vulnerable to SQL-I and demonstrate few attacks.

Let us being by creating a working environment, following are the pre-requisites to get started:

Pre-Requisites
SDK	Java EE-6
Web Container	JBoss 7.1 AS
Database Server	MySQL

We will create a simple JSP, Servlet and JDBC based application to be as simple to demonstrate as possible.

Steps to begin:

Execute the following script in MySQL:

Database Setup:

CREATE DATABASE IF NOT EXISTS test;

USE test;

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL,
  `username`  varchar(255) default NULL,
  `email`  varchar(255) default NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO user VALUES (1, 'HIMANSHU', 'abc@xyz.com');
INSERT INTO user VALUES (2, 'TEST', 'pqr@xyz.com');
INSERT INTO user VALUES (3, 'SOME OTHER GUY', 'def@xyz.com');

SQLI Console (JSP page):

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<html>
<head>
<title>Test SQL Injection</title>
</head>
<body>
 <form action="${pageContext.request.contextPath}/demosqlinjection" method="post">
  <table>
<tr>
    <td colspan="2"><h2>
Let's demonstrate some SQL-Injection</h2>
</td>
   </tr>
<tr>
    <td>Username:</td>
    <td>
     &lt;input id="username" name="username" type="text" /&gt;
    </td>
   </tr>
<tr>
    <td colspan="2">
     &lt;input type="submit" value="submit" /&gt;
    </td>
   </tr>
</table>
</form>
</body>
</html>

Servlet Code (Here is where the code is buggy):

@WebServlet(name = "demoServlet", urlPatterns = "/demosqlinjection")
public class DemoServlet extends HttpServlet {


 //Vulnerable to SQLI attacks
 @Override
 protected void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
  res.setContentType("text/html;charset=UTF-8");
  PrintWriter out = res.getWriter();
  try {

            String user = req.getParameter("username");
            Connection conn = null;
            String url = "jdbc:mysql://127.0.0.1:3306/";
            String dbName = "test";
            String driver = "com.mysql.jdbc.Driver";
            String userName = "root";
            String password = "root";
            try {
                Class.forName(driver).newInstance();
                conn = DriverManager.getConnection(url + dbName, userName, password);

                Statement st = conn.createStatement();
                String query = "SELECT * FROM  user where username='" + user + "'";
                out.println("Query : " + query);

                System.out.printf(query);
                ResultSet resultSet = st.executeQuery(query);
                out.println("
");
                out.println("
");
                out.println("\nResults");
                while (resultSet.next()) {
                 out.println("

");
                    String s = resultSet.getString("username");
                    out.println("\t\t" + s);
                }
                conn.close();

            } catch (Exception e) {
                e.printStackTrace();
            }
        } finally {
            out.close();
        }
 }

}

As you see in the line highlighted above, the way we are forming the SQL Query is through concatenation of parameters, which will gives us the query results as expected. But makes the code vulnerable to SQL-Injection.

Few examples of SQL Injection because of the above code: (Including Screenshots)

1. A simple query piggy-backed:

Here we are append asdas' or '1'='1, if we append it as is in the query the new query formed is:

select * from user where username='asdas' or '1'='1';
Request:

Response:

2. UNION based SQLI, fetching sensitive information from database:

Here we use UNION clause to get the credentials of the users provisioned to use MySQL.

Section appended: asd' UNION SELECT max_user_connections, CONCAT(user, '##', password), password FROM mysql.user u-- or '1'='1

New Query formed:

select * from user where username='asd' UNION SELECT max_user_connections, CONCAT(user, '##', password), password FROM mysql.user u-- or '1'='1';

Request:

Response:

3. Timing SQLI:

In this type of particular attack we basically execute a query which is time consuming making system slow or delayed to respond. Like, for SQL Server we have waitfor delay ('delay period'), similarly for MySQL we have BENCHMARK.

Request:

Response:

How to prevent SQL Injection?
Well if we discuss about this, the most easiest way to prevent direct SQLI is making use of parametrized queries instead of normal queries.

In order to demonstrate the same I would be changing the code of the servlet which we considered buggy earlier.

Servlet Code (Updated to prevent SQLI):

@WebServlet(name = "demoServlet", urlPatterns = "/demosqlinjection")
public class DemoSQLInjectionServlet extends HttpServlet {

 //Safe from SQLI
 @Override
 protected void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
  res.setContentType("text/html;charset=UTF-8");
  PrintWriter out = res.getWriter();
  try {

            String user = req.getParameter("username");
            Connection conn = null;
            String url = "jdbc:mysql://127.0.0.1:3306/";
            String dbName = "test";
            String driver = "com.mysql.jdbc.Driver";
            String userName = "root";
            String password = "root";
            try {
                Class.forName(driver).newInstance();
                conn = DriverManager.getConnection(url + dbName, userName, password);

                String query = "SELECT * FROM  user where username=?";
                java.sql.PreparedStatement pstmt = conn.prepareStatement(query);
                pstmt.setString(1, user);

                out.println("Query : " + query);

                System.out.printf(query);
                ResultSet resultSet = pstmt.executeQuery();
                out.println("
");
                out.println("
");
                out.println("\nResults");
                while (resultSet.next()) {
                 out.println("

");
                    String s = resultSet.getString("username");
                    out.println("\t\t" + s);
                }
                conn.close();

            } catch (Exception e) {
                e.printStackTrace();
            }
        } finally {
            out.close();
        }
 }
}

The highlighted part above demonstrates an alternate way to execute the same query, but the beauty of this approach is that it resists SQLI.

If we try to run the same test with the new code, we will see that none of query gets executed, they are just simple passed-by.

GOLDEN RULE:
The important thing to remember is to never construct SQL statements using string concatenation of unchecked input values. Creating of dynamic queries via the java.sql.Statement class leads to SQL Injection.

NOTE:
There are a number of tools available which can be used to test for SQL Injection, Few includes:

sqlmap
Burp (I have used this one to test the SQLInjection via modifying HTTP Headers)

In later parts of the blog, I will try to cover the SQL-Injection using various tools.

Java Updates

Tuesday, May 14, 2013

SQL Injection a threat : Controlled