Escape characters in Like expressions

Description

I have a Criteria where I try to find a record by using an InsensitiveLike("Tittel", "[email template] testtemplate", MatchMode.Exact)

executing this the where statement looks like this :

WHERE
lower(this_.Tittel) like '[email template] testtemplate'

this will not find my record event if it exists, but this will :

WHERE
lower(this_.Tittel) = '[email template] testtemplate'

as it turns out, in SQL server the [] actually have a meaning in a like statement, and if they are not supposed to have a meaning, they should be escaped. This actually goes for the other special characters as well - at least when using MatchMode.Exact, in my humble opinion.

Actually this is also partially caused by not having an InsensitiveEq expression. One way to solve this might also be to interpret a MatchMode.Exact as an = (like the second statement above) - that would have solved the problem as well - that actually was what I wanted when using this...

Environment

None

Activity

Show:

Jon Seigel March 23, 2010 at 6:55 AM

This fix does not solve the original problem, which was to escape a LIKE pattern in a database-independent way, based on the selected dialect. All it does is wrap the ESCAPE keyword, which still requires the programmer to manually escape all the necessary characters.

What was the design decision behind implementing the functionality in this way?

Solution:

  • Add an overload to Restrictions.Like and .InsensitiveLike with signature (string, string, MatchMode, bool escapeInput)

  • Add similar overloads from the previous point to the constructors of LikeExpression and InsensitiveLikeExpression

Fabio Maulo July 8, 2008 at 11:02 PM

Was fixed in r3347 (NH2.0.0Alpha1)
You have two way to do it
1) using Restrictions.Like(string, string, MatchMode, char?) (case sensitive)
2) using directly one constructor of NHibernate.Criterion.LikeExpression

Former user March 8, 2007 at 9:58 PM

Okay, I'm changing this to Improvement to be implemented sometime.

JensJ March 8, 2007 at 9:06 AM

I partially agree, but when one uses MatchMode.Exact Nhibernate doesn't use any special characters either, and not having any other way of making an Insensitive comparison I thought that might be the way.

Perhaps a MatchMode.Custom could be used for NHibernate to not touch the statement if someone actually wanted the characters there and then 'Exact' actually could be "Exact"ly that - an InsensitiveEq. In order to be database independent I believe the possibility of making an "InsensitiveEq" (whether that is a Like with MatchMode.Exact or not) is important.

Former user March 8, 2007 at 8:48 AM

Or you could manually escape the necessary characters. Otherwise, how can NHibernate know that you really intended for brackets to match literally and not to have their SQL Server-specific meaning?

Fixed

Details

Assignee

Reporter

Components

Fix versions

Affects versions

Priority

Who's Looking?

Open Who's Looking?
Created March 8, 2007 at 8:28 AM
Updated March 23, 2010 at 6:55 AM
Resolved July 8, 2008 at 11:02 PM
Who's Looking?