Using hibernate criteria, is there a way to escape special characters?

Question

For this question, we want to avoid having to write a special query since the query would have to be different across multiple databases. Using only hibernate criteria, we want to be able to escape special characters.

This situation is the reason for needing the ability to escape special characters:

Assume that we have table 'foo' in the database. Table 'foo' contains only 1 field, called 'name'. The 'name' field can contain characters that may be considered special in a database. Two examples of such a name are 'name_1' and 'name%1'. Both the '_' and '%' are special characters, at least in Oracle. If a user wants to search for one of these examples after they are entered in the database, problems may occur.

criterion = Restrictions.ilike("name", searchValue, MatchMode.ANYWHERE);
return findByCriteria(null, criterion);

In this code, 'searchValue' is the value that the user has given the application to use for its search. If the user wants to search for '%', the user is going to be returned with every 'foo' entry in the database. This is because the '%' character represents the "any number of characters" wildcard for string matching and the SQL code that hibernate produces will look like:

select * from foo where name like '%' 

Is there a way to tell hibernate to escape certain characters, or to create a workaround that is not database type specific?

Churchill

Answer 1

LikeExpression's constructors are all protected, so it's not a viable option. Also, it has problems of its own.

A colleague and I created a patch which works pretty well. The gist of the patch is that for the LikeExpression constructor which consumes a MatchMode, we escape the special characters. For the constructor which consumes a Character (the escape character), we assume the user escapes the special characters on their own.

We also parameterized the escape character to ensure that it can't corrupt the SQL query if they use something like \ or a quote character.

package org.hibernate.criterion;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.dialect.Dialect;
import org.hibernate.engine.TypedValue;

public class LikeExpression implements Criterion {
    private final String propertyName;
    private final String value;
    private final Character escapeChar;

    protected LikeExpression(
            String propertyName,
            Object value) {
        this(propertyName, value.toString(), (Character) null);
    }

    protected LikeExpression(
            String propertyName,
            String value,
            MatchMode matchMode) {
        this( propertyName, matchMode.toMatchString( value
                .toString()
                .replaceAll("!", "!!")
                .replaceAll("%", "!%")
                .replaceAll("_", "!_")), '!' );
    }

    protected LikeExpression(
            String propertyName,
            String value,
            Character escapeChar) {
        this.propertyName = propertyName;
        this.value = value;
        this.escapeChar = escapeChar;
    }

    public String toSqlString(
            Criteria criteria,
            CriteriaQuery criteriaQuery) throws HibernateException {
        Dialect dialect = criteriaQuery.getFactory().getDialect();
        String[] columns = criteriaQuery.getColumnsUsingProjection( criteria, propertyName );
        if ( columns.length != 1 ) {
            throw new HibernateException( "Like may only be used with single-column properties" );
        }
        String lhs = lhs(dialect, columns[0]);
        return lhs + " like ?" + ( escapeChar == null ? "" : " escape ?" );

    }

    public TypedValue[] getTypedValues(
            Criteria criteria,
            CriteriaQuery criteriaQuery) throws HibernateException {
        return new TypedValue[] {
                criteriaQuery.getTypedValue( criteria, propertyName, typedValue(value) ),
                criteriaQuery.getTypedValue( criteria, propertyName, escapeChar.toString() )
        };
    }

    protected String lhs(Dialect dialect, String column) {
        return column;
    }

    protected String typedValue(String value) {
        return value;
    }

}

If you're wondering what the lhs and typedValue methods are for, the new IlikeExpression should answer those questions.

package org.hibernate.criterion;

import org.hibernate.dialect.Dialect;

public class IlikeExpression extends LikeExpression {

    protected IlikeExpression(
            String propertyName,
            Object value) {
        super(propertyName, value);
    }

    protected IlikeExpression(
            String propertyName,
            String value,
            MatchMode matchMode) {
        super(propertyName, value, matchMode);

    }

    protected IlikeExpression(
            String propertyName,
            String value,
            Character escapeChar) {
        super(propertyName, value, escapeChar);
    }

    @Override
    protected String lhs(Dialect dialect, String column) {
        return dialect.getLowercaseFunction() + '(' + column + ')';
    }

    @Override
    protected String typedValue(String value) {
        return super.typedValue(value).toLowerCase();
    }

}

After this, the only thing left is to make Restrictions use these new classes:

public static Criterion like(String propertyName, Object value) {
    return new LikeExpression(propertyName, value);
}

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

public static Criterion like(String propertyName, String value, Character escapeChar) {
    return new LikeExpression(propertyName, value, escapeChar);
}

public static Criterion ilike(String propertyName, Object value) {
    return new IlikeExpression(propertyName, value);
}

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

public static Criterion ilike(String propertyName, String value, Character escapeChar) {
    return new IlikeExpression(propertyName, value, escapeChar);
}

Edit: Oh yeah. This works for Oracle. We're not sure about other databases though.

Drivein

Hi , I know this is very old post .Would you mind to post complete example if possible. – Baulk

Answer 2

It's not a very clean way to do it but a sqlRestrinction should be easier:

criterions.add(Restrictions.sqlRestriction(columnName+ " ilike '!%' escape '!'"));

You can even do a start with search using the same principle:

criterions.add(Restrictions.sqlRestriction(columnName+ " ilike '!%%' escape '!'"));

Magnesia

I started with this solution and it worked well for me on simple queries, but a later encountered an issue with it - if you need to use the {alias} placeholder in columnName, this placeholder always refers to the table that the root entity is mapped to. If you are using any joins in your query, Hibernate cannot insert the aliases for the join tables. In this case, I had to resort to subclassing LikeExpression. – Throughway

That helps: I tried criterions.add(Restrictions.sqlRestriction(columnName+ " like '!%' escape '!'")); ilike doesn't work for me – Jibe

Answer 3

if you use LikeExpression directly, it enables you to specify the escape character. i assume that should be all you need.

Moonmoonbeam

Thanks. Hopefully I can find the time to try this later today. I will update after I try it. – Churchill

IlikeExpression does not have it though. – Shum

All of LikeExpression's constructors are protected, you would need to subclass and make a public constructor. – Throughway

Answer 4

If you use Hibernate 3.2+, you can subclass LikeExpression, and then create factory like/ilike methods:

import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.LikeExpression;
import org.hibernate.criterion.MatchMode;

public class EscapedLikeRestrictions {
    private EscapedLikeRestrictions() {}

    public static Criterion likeEscaped(String propertyName, String value, MatchMode matchMode) {
        return likeEscaped(propertyName, value, matchMode, false);
    }

    public static Criterion ilikeEscaped(String propertyName, String value, MatchMode matchMode) {
        return likeEscaped(propertyName, value, matchMode, true);
    }

    private static Criterion likeEscaped(String propertyName, String value, MatchMode matchMode, boolean ignoreCase) {
        return new LikeExpression(propertyName, escape(value), matchMode, '!', ignoreCase) {/*a trick to call protected constructor*/};
    }

    private static String escape(String value) {
        return value
                .replace("!", "!!")
                .replace("%", "!%")
                .replace("_", "!_");
    }
}

Pilsudski

Answer 5

If you use sqlRectrictions, the correct way to do it is the following:

criterions.add(Restrictions.sqlRestriction(columnName+" LIKE '!%' ESCAPE '!'"));

Is like a sql query, doesn't work ilike => use LIKE using Oracle 12i.

Yorick