Tuesday, May 14, 2013

SQL Injection a threat : Controlled

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:
  1. sqlmap
  2. 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.

Thursday, May 9, 2013

Remove Duplicate Rows from a Table in MySQL

You have a table with duplicate rows – somehow a unique index didn’t get created and a bug has added duplicate records to your table. :(

Let us create a scenario:

Create the table:

CREATE TABLE `t1` (`yearcol` varchar(255) default NULL,
                   `firstname` varchar(255) NOT NULL);

Let's add some data now:

insert into t1 values ('2010', 'ABC');

insert into t1 values ('2010', 'ABC');

insert into t1 values ('2010', 'XYZ');

insert into t1 values ('2010', 'PQR');

On doing a simple select query we get the result as:
The highlighted columns shows that there is duplicacy in the table.
select * from t1;

The result generated is:

yearcol firstname
2010 ABC
2010 ABC
2010 XYZ
2010 PQR


Now to remove the duplicates and fix the indexing issue. We will use ALTER TABLE which will help me add the UNIQUE INDEX along with IGNORE  keyword, which will help me fix the duplicates.

The official documentation of MySQL says:
  • IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.         

Now let's apply the UNIQUE INDEX using ALTER TABLE command:
ALTER IGNORE TABLE t1 ADD UNIQUE INDEX dup_idx (year_col, firstname);

We get the output something like:
mysql> ALTER IGNORE TABLE t1 ADD UNIQUE INDEX dup_idx (yearcol, firstname);
Query OK, 4 rows affected (0.14 sec)
Records: 4  Duplicates: 1  Warnings: 0

On doing a simple select query we get the result as:
The highlighted columns shows that the duplicate column is no longer in tabler.
select * from t1;

The result generated is:

yearcol firstname
2010 ABC
2010 XYZ
2010 PQR

And we have achieved the removal of duplicates from the table and also fixed the UNIQUE INDEX in the table to save ourselves from any further future accidents like these.  :)

NOTE:
For some versions of MySQL, this solution does not work. This is basically due to engine being used:

In order to fix the same (Thanks to Kethan for the update):
ALTER TABLE t1 ENGINE MyISAM;
ALTER IGNORE TABLE t1 ADD UNIQUE INDEX dup_idx (yearcol, firstname);
ALTER TABLE t1 ENGINE InnoDB;

Tuesday, May 7, 2013

Spring Security : Role based welcome page

As the title suggests, this blog is written taking into consideration the need to have different welcome page for users with different roles.

If I talk about a simple web-application with just a single welcome page for all roles I can simple do it with using the <welcome-file-list> in web.xml.


       profile.jsp


When it comes to simple web-application where our code controls the security of the application, we can do the same on the basis of roles and redirecting response to the appropriate page, using RequestDispatcher or if we are using MVC frameworks like Spring or Struts then we just need to pass the right view and rest framework takes care of by itself.

But since the main idea of writing this blog is to describe the mechanism of how to do the same with Spring-Security in place, not discuss MVC frameworks.

Spring Security provides, 
org.springframework.security.web.authentication.SimpleUrlAuthenticationSuccessHandler,  the main idea of having this class is basically any post operations we want to perform once authentication is successful.


My sample spring-security http-config :



  
  
  
  
  
  
  
  
 


Now as you see, I have used authentication-success-handler-ref="authenticationSuccessRedirecthandler" inside the <form-login> tag in the security configuration. This is how I can wire the authentication handler into the security workflow.

public class CustomAuthenticationHandler extends SimpleUrlAuthenticationSuccessHandler {

 @Override
 public void onAuthenticationSuccess(HttpServletRequest request, HttpServletResponse response, Authentication authentication) throws ServletException, IOException {
      String userTargetUrl = "/user/profile.jsp";
      String adminTargetUrl = "/admin/dashboard.jsp";
      Set roles = AuthorityUtils.authorityListToSet(authentication.getAuthorities());
      if (roles.contains("ROLE_ADMIN")) {
         getRedirectStrategy().sendRedirect(request, response, adminTargetUrl);
      } else if (roles.contains("ROLE_USER")) {
         getRedirectStrategy().sendRedirect(request, response, userTargetUrl);
      } else {
         super.onAuthenticationSuccess(request, response, authentication);
         return;
      }
   }
}

onAuthenticationSuccess is a callback method, which gets invoked once the user is successfully logged-into the application, to perform any post-operations.

Once the web-application is built and deployed on the server, as soon as we enter a URL to access the application. We will be shown the login screen to authenticate the user.

Say we have two users configured,

Username Password Role
sam sam ROLE_USER
admin admin ROLE_ADMIN

User logs in with sam:

Since sam is of type ROLE_USER, hence as per the method onAuthenticationSuccess of CustomAuthenticationHandler , the user will be redirected to /user/profile.jsp on successful login.



User logs in with admin:

Since admin is of type ROLE_ADMIN, hence as per the method onAuthenticationSuccess of CustomAuthenticationHandler , the user will be redirected to /admin/dashboard.jsp on successful login.