如何转义LIKE子句? [英] How do I escape a LIKE clause?

问题描述

在搜索查询的这一部分中，我们使用的代码很简单:

The code we're using is straightforward in this part of the search query:

myCriteria.Add(
    Expression.InsensitiveLike("Code", itemCode, MatchMode.Anywhere));

这在生产环境中很好用.

And this works fine in a production environment.

问题是我们的一位客户的商品代码包含此查询需要匹配的％符号.此代码产生的SQL输出类似于:

The issue is that one of our clients has item codes that contain % symbols which this query needs to match. The resulting SQL output from this code is similar to:

SELECT ... FROM ItemCodes WHERE ... AND Code LIKE '%ItemWith%Symbol%'

这清楚地说明了为什么他们在商品搜索中得到一些奇怪的结果.

Which clearly explains why they're getting some odd results in item searches.

是否可以使用编程的Criteria方法启用转义?

Is there a way to enable escaping using the programmatic Criteria Methods?

附录:

我们使用的是稍旧版本的NHibernate 2.1.0.4000(撰写本文时的最新版本是2.1.2.4853)，但是我检查了发行说明，但没有提及针对此问题的修复程序.我也没有在他们的bugtracker中找到任何未解决的问题.

We're using a slightly old version of NHibernate, 2.1.0.4000 (current as of writing is 2.1.2.4853), but I checked the release notes, and there was no mention of a fix for this. I didn't find any open issue in their bugtracker either.

我们正在使用SQL Server，因此我可以很轻松地在代码中转义特殊字符(％，_，[和^)，但是我们使用NHibernate的要点是使应用程序独立于数据库引擎而成为

We're using SQL Server, so I can escape the special characters (%, _, [, and ^) in code really easily, but the point of us using NHibernate was to make our application database-engine-independent as much as possible.

Restrictions.InsensitiveLike()和HqlQueryUtil.GetLikeExpr()都不会转义其输入，并且删除MatchMode参数对转义没有任何影响.

Neither Restrictions.InsensitiveLike() nor HqlQueryUtil.GetLikeExpr() escape their inputs, and removing the MatchMode parameter makes no difference as far as escaping goes.

我发现其他人想要做同样的事情(三年前)，并且解决方法是将escapeChar重载添加到我上面提到的方法中(在2.0.0.3347版本中已修复").我对此问题添加了评论，要求进一步解决.

I found someone else wanting to do this same thing (three years ago), and the resolution was to add the escapeChar overloads to the methods I've mentioned above (this was "fixed" in version 2.0.0.3347). I added a comment to that issue asking for further resolution.

推荐答案

我能找到的实现数据库独立性的唯一方法是转义搜索字符串中的每个字符，并像上一个答案中那样在LikeExpression上调用适当的构造函数.您可以手动执行此操作或扩展LikeExpression:

The only way I can find to achieve database independence is to escape every character in the search string and call the appropriate constructor on LikeExpression as in my previous answer. You could do this manually or extend LikeExpression:

    public class LikeExpressionEscaped : LikeExpression
    {
        private static string EscapeValue(string value)
        {
            var chars = value.ToCharArray();
            var strs = chars.Select(x => x.ToString()).ToArray();
            return "\\" + string.Join("\\", strs);
        }

        public LikeExpressionEscaped(string propertyName, string value, MatchMode matchMode, bool ignoreCase)
            : base(propertyName, EscapeValue(value), matchMode, '\\', ignoreCase)
        {}
    }

毫无疑问，创建转义字符串的更有效方法(请参见

There is no doubt a more efficient way to create the escaped string (see the answers and comments to this question). Usage is:

var exp = new LikeExpressionEscaped("Code", itemCode, MatchMode.Anywhere, true);
myCriteria.Add(exp);

这篇关于如何转义LIKE子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！