Escape special characters

Certain characters such as “_” and “%” are not interpreted literally because they have special meaning within databases.

Few databases like Oracle and DB2 allows the assignment of special escape characters to tell database that the character is interpreted literally.

For example consider the below table:
EMPLOYEE
--------------------------------------------
ID           NAME           LOCATION
--------------------------------------------
1             Raj_esh          Bangalore
2             Pravin             Mumbai
3             Kiran              Pune
4             Rahul              Chennai

From the above table if we would like to fetch all employees whose contains “_” then the query and result is:
select * from EMP_TEMP where name like '%_%'
--------------------------------------------
ID           NAME           LOCATION
--------------------------------------------
1             Raj_esh          Bangalore
2             Pravin             Mumbai
3             Kiran              Pune
4             Rahul              Chennai

So we didn’t get what we expected instead we got all records. As a solution to this Oracle or DB2 supports an ESCAPE clause to tell data base that the character is interpreted literally.

The modified query is
select * from EMP_TEMP where name like '%¼_%' ESCAPE ‘¼’
The character symbol ¼ is used because it can rarely occur in search criteria.

The result of the modified query is
--------------------------------------------
ID           NAME           LOCATION
--------------------------------------------
1             Raj_esh          Bangalore

List of vulnerabilities in any web application

1. Cross-Site Request Forgery (CSRF)
Through CSRF attacker can by-pass authentication protocols and manipulate a user’s browser into submitting valid HTTP requests to a target site.For example a user logs in to particular application and also opens another browser and navigates to some malicious site. The site can contain hidden iframe with some script that auto-posts a form back to the application.

2. Cross Site Scripting (CSS)
User attempts to embed script into the request/post parameters

3. Debug Error message enabled in production environment
Path, version and stack trace information can be gathered and used for further attacks. Attacker will be able to view SQL executed which could lead to SQL injection attacks.

4. SQL injection
Avoid the use of dynamic SQL (use setXXX() method on the prepared statement than appending it dynamically to the query)

5. Use of Java Script eval function
The attacker can inject any arbitrary code to eval function.

6. Sensitive data input fields autocomplete active
Set autocomplete=”off” to sensitive input fields. If this feature is turned on the information will be stored in plain text somewhere on the computer (in the registry, or elsewhere)

Escape special characters in hibernate criteria

This is the continuation of my previous post. The method mentioned in previous post will help in case of direct SQL and HQL. We will see how to implement the same thing with hibernate criteria.

We will extend the default functionality of org.hibernate.criterion.IlikeExpression and override
public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) method
to include ESCAPE clause at the end of sql query.

So we need to generate sql out of hibernate criteria in the flowing pattern in order to tell data base that special characters are interpreted literally..
select * from EMP_TEMP where name like '%¼_%' ESCAPE ‘¼’

Two need to do two things in order to achieve this.
1. Append escape character with search value containing special character.
2. Append ESCAPE clause at the end of the generated query.

The first step appending escape character with search value is as follows
public static String HIBERNATE_ESCAPE_CHAR = "¼";
String searchValue = “%_%”;
//In the simillar fashion we can escape “%” also
searchValue = searchValue.replaceAll(“_”,HIBERNATE_ESCAPE_CHAR + "_");

The second step is to append ESCAPE clause with sql. For that first we will create custom expression by extending IlikeExpression as specified below

public class EscapedILikeExpression extends IlikeExpression {
public static final String ESCAPE_CHAR = " ESCAPE '¼' ";

/**
* @param propertyName
* @param value
*/
public EscapedILikeExpression(String propertyName, Object value) {
super(propertyName, value);
// TODO Auto-generated constructor stub
}

/**
* @param propertyName
* @param value
* @param matchMode
*/
public EscapedILikeExpression(String propertyName, String value,MatchMode matchMode) {
super(propertyName, value, matchMode);
// TODO Auto-generated constructor stub
}

public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery)
throws HibernateException {
String sql = super.toSqlString(criteria, criteriaQuery);
sql = sql + ESCAPE_CHAR;
return sql;
}
}

And add EscapedRestrictions to use EscapedIlikeExpression
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.MatchMode;
public class EscapedRestrictions {
          public static Criterion ilike(String propertyName, String value) {
                    return new EscapedILikeExpression(propertyName, value);
          }

          public static Criterion ilike(String propertyName, String value, MatchMode matchMode) {
                    return new EscapedILikeExpression(propertyName, value, matchMode);
          }
}

Now the criteria code will look like this which makes use of EscapedRestrictions.java
Session session = hibernateTemplate.getSessionFactory().openSession();
Criteria criteria = session.createCriteria(EmpTemp.class)
.add(EscapedRestrictions.ilike("name", searchValue));
List<EmpTemp> list = criteria.list();
if(list != null && !list.isEmpty()){
for(EmpTemp employee : list){
System.out.println(employee.getName());
System.out.println(employee.getLocation ());
}
}
session.close();

This would result the SQL query same as below and provide results that we expected.
select * from EMP_TEMP where name like '%¼_%' ESCAPE ‘¼’