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.

Friday, April 26, 2013

Implementing Captcha Control with JSF 2.0

This post is in continuation with my previous post related to custom controls. Now comes the part where I will make a practical use of my knowledge.

JSF is all about rich web development. And in today's www world to differentiate between a human and a bot is very difficult. So in order to avoid spam and unnecessary traffic comes into picture Captcha.

I will start by first introducing on how to get started with Captcha:


  1. First of all the user who wants to use captcha service need to have a Google Account.
  2. Visit the site: reCAPTCHA website. 
  3. Login into your google account.
  4. Once the reCaptcha signup form is submitted, a page will be shown saying that:






This screen shows that you have successfully setup you captcha account.

Now in order to integrate captcha with your JSF-2.0 application.

It is required to do atleast one of the following:

  1. Add the HTML code as is, and then turn you head up and down, to work it in sync with the JSF view.
  2. Write a custom control which will do the part of generating the Captcha HTML for me, so I have a clean views to maintain and is easily manageable. (Winner for me :))

So in order to begin with this integration into a JSF web-application following steps are required:

  • Get the required library for captcha integration:
  • Create a custom Faces Component for captcha rendering:
  • Adding the component in faces-config.xml
  • Register the control in tag in the taglib.
  • Register the taglib in web.xml
  • Activate the taglib in faces.
  • Create a validator to actually validate the captcha with Google API for Captcha
  • Create a sample test page.

 So now I will take one step at a time:

Get captcha dependency:

For maven users, simply add this dependency in your pom.xml:

<dependency>
     <groupId>net.tanesha.recaptcha4j</groupId>
     <artifactId>recaptcha4j</artifactId>
     <version>0.0.7</version>
</dependency>

For non-maven users the jar is available for download here.

Once the jar is included in the classpath. We are ready to move to step-2.

Create custom Faces Component:

For the same I have posted the code below in the file, RecaptchaComponent.java
Don't foget to have a look at the overriden method public void validate(FacesContext ctx);
I will discuss the details later once, I have written the validator.

Adding the component in faces-config.xml

No we do not need to do it here, because the component class is already being annotated with @FacesComponent, so no need to do in faces-config.xml. Both the approaches are alternatives of each other.

Register the control in tag in the taglib:

Since we need to use this control as a tag inside the XHTMLs so it is required that we register the same. For this refer to taglib.xml, as shown in the code below.


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE facelet-taglib PUBLIC
"-//Sun Microsystems, Inc.//DTD Facelet Taglib 1.0//EN"
"http://java.sun.com/dtd/facelet-taglib_1_0.dtd">

<facelet-taglib>

    <namespace>http://himanshu/jsf-custom-components/</namespace>
    <tag>
    <tag-name>recaptcha</tag-name>
    <component>
    <component-type>com.himanshu.jsf.custom.recaptcha</component-type>
    </component>
    </tag>
</facelet-taglib>


Register the taglib in web.xml and Activate the taglib in faces:

Both these steps can be merged into a single step, with the following tags in web.xml.


<context-param>
<param-name>javax.faces.FACELETS_LIBRARIES</param-name>
<param-value>/WEB-INF/marquee-taglib.xml</param-value>
</context-param>



Create a validator to actually validate the captcha with Google API for Captcha:

We have created a custom validator which will capture the request, get the input entered in the captcha control. And will validate it using Google API for captcha (RecaptchaValidator.java)

And we are done.

Now its time to test: 


<ui:composition xmlns="http://www.w3.org/1999/xhtml"
xmlns:h="http://java.sun.com/jsf/html"
xmlns:f="http://java.sun.com/jsf/core"
xmlns:ui="http://java.sun.com/jsf/facelets"
xmlns:customJSF="http://himanshu/jsf-custom-components/">

<f:view>

<h:head></h:head>

    <h:form id>

              
<customJSF:recaptcha id="rc" publicKey="abc" privateKey="xyz">

<f:validator validatorId="com.himanshu.jsf.captcha.validator" />

      </customJSF:recaptcha>

      <h:message for="rc" />

              
              <h:commandButton type="submit" id="login" value="Login" action="#{helloWorld.submitForm}" />


        </h:form>

</f:view>

</ui:composition>


Build the application and deploy in AS.
This will render the captcha control once the corresponding view is opened.

Here is the sample captcha generated.



But wait a minute, my validator is not getting called. Did I miss something?

Now let me rollback to the point where I had to override validate method in RecaptchaComponent.java.

Why did we do that, this was done basically because:
Validators are by default or by configuration not invoked when the value is empty. For that, either the required attribute should be used, or the javax.faces.VALIDATE_EMPTY_FIELDS context param should be set with a value of true.

That was the case here. So overriding the validate method and calling the validator registered with the RecpatchaComponent serves the purpose here.

Now we are done with developing the captcha component for JSF-2.0 and ready to integrate in our application.


Tuesday, April 23, 2013

Writing Custom components for JSF-2.0

With the introduction of JSF-2 along came some feature which were simplified. One such feature is creation of custom JSF components.


Why is it required?

Well one reason for it is that is wrapping up the tag which is complex in itself. To make it more simple to use and user-friendly.

Consider a simple case:
I need to have a marquee component, in which I will just mention the string which will be floated in my HTML and the rest will be done component renderer itself.

How is it done?

So first of all in order to make this work, we need to understand what all files we need to create or update.

(MarqueeComponent.java) First of all we need to extend "javax.faces.component.UIComponentBase", extending this class gives us the methods which are used to translate a tag into its HTML equivalent. Look at the annotation @FacesComponent, using this we declare the component type, which is then used in faces-config.xml for declaring the component.


(faces-config.xml) After extending UIComponentBase class, it was required to communicate to JSF that a custom component is created the configuration for the same is mentioned below in the code snippets.

NOTE: You need to configure component in faces-config.xml, iff @FacesComponent is not being used to register the component. @FacesComponent and <component> tag in faces-config.xml are alternatives of each other. @henk, Thanks for pointing out.


(marquee-taglib.xml) And the next part is to create a taglib for the same so that we can use the tags JSF files. As once taglibs are loaded in web-application then only we can use those tags.


(web.xml - snippet) But still one part is missing, we still haven't mentioned the part where we are supposed to load the taglibs in the web-application. For the same, we need to mention the taglib file in web.xml, under context param javax.faces.FACELETS_LIBRARIES 

(sample.xhtml) A sample to demonstrate the custom tag.

Code

Saturday, April 20, 2013

How to write a Custom Codec in Apache MINA?

Whenever we have to develop a socket based enterprise application. Few frameworks come to our mind. To name a few:


If I go by the book,


Netty:
Netty is a NIO client server framework which enables quick and easy development of network applications such as protocol servers and clients. It greatly simplifies and streamlines network programming such as TCP and UDP socket server.

'Quick and easy' doesn't mean that a resulting application will suffer from a maintainability or a performance issue. Netty has been designed carefully with the experiences earned from the implementation of a lot of protocols such as FTP, SMTP, HTTP, and various binary and text-based legacy protocols. As a result, Netty has succeeded to find a way to achieve ease of development, performance, stability, and flexibility without a compromise.


Whereas,


Apache MINA:
Is a network application framework which helps users develop high performance and high scalability network applications easily. It provides an abstract event-driven asynchronous API over various transports such as TCP/IP and UDP/IP via Java NIO.

Data Packets:
If I talk about sending packets over network there are mainly two approaches being used:

  1. Connect - Send - Process - ByeBye
    • Client make a socket connection with server 
    • Sends data over the channel 
    • Server Processes it
    • Send results or acknowledgement
    • Objective complete socket closed
  2. Connect - Send - Process - Send ... and so on
    • Client establishes the connection for the first time with server
    • Sends data over the channel
    • Server Processes it
    • Send results or acknowledgement
    • Channel still remains open for further communication.

Now we have the question which approach to go for, remember both approaches have its pros and cons.

Lets first discuss:
There's always an overhead of socket connections establishment. If same client keeps on sending a large number of packets, then we will be wasting considerable CPU cycles in this process. So if we have certain specific number of client only then we can think of moving to approach#2

But if we have large number of clients then we can say approach#2 will easily become the cause of bottleneck.

Now lets come to the main reason why this blog is written, there can be a number of times that while doing socket connections, developer have to create their own protocol for the clients. This gives independence of customization.

But if I merge approach#2 for socket communication with custom protocol, well that's definitely something to look at.

I am going to explain the method of writing a custom codec with Encoder and Decoder. This is somewhat I did though I changed the actual protocol because of some reasons I can't disclode ;) .

The code appears to be self explanatory, but I would still like to put up few points.
Here I have used cumulative protocol, because I might required more packets for merging in order to get complete information. 

I am open to inputs as well as if anyone want more details.