.net 在数据表过滤器表达式中转义字符的正确方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/386122/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Correct way to escape characters in a DataTable Filter Expression
提问by Ady
I would like to know if there is a function to correctly escape string literals for filter expressions. e.g.:
我想知道是否有一个函数可以正确转义过滤器表达式的字符串文字。例如：
DataTable.Select(String.Format("[name] = '{0}'", MyName))
If MyName contains ' or a number of other key characters an exception is generated. The Microsoft documentationindicates that these charaters should be correctly escaped, however there is a bit of confusion on how this is to be done.
如果 MyName 包含 ' 或许多其他关键字符，则会生成异常。在微软的文档表明，这些charaters应正确逃脱，但没有对如何，这是做了一些混乱。
I have tried replacing ' with \' and also ['] as indicated in the documentation, however the query still fails.
我已经尝试将 ' 替换为 \' 和 [']，如文档中所示，但是查询仍然失败。
Many Thanks
非常感谢
回答by Rory
Escape the single quote ' by doubling it to ''. Escape * % [ ] characters by wrapping in []. e.g.
通过将单引号 ' 加倍到 '' 来转义它。通过在 [] 中换行来转义 * % [ ] 字符。例如
private string EscapeLikeValue(string value)
{
StringBuilder sb = new StringBuilder(value.Length);
for (int i = 0; i < value.Length; i++)
{
char c = value[i];
switch (c)
{
case ']':
case '[':
case '%':
case '*':
sb.Append("[").Append(c).Append("]");
break;
case '\'':
sb.Append("''");
break;
default:
sb.Append(c);
break;
}
}
return sb.ToString();
}
public DataRow[] SearchTheDataTable(string searchText)
{
return myDataTable.Select("someColumn LIKE '"
+ EscapeLikeValue(searchText) + "'");
}
Thanks to examples here
感谢这里的例子
回答by Ady
If I replace ' with two single ' the query works.
如果我用两个单个 ' 替换 ' ，则查询有效。
回答by Soenhay
/// <summary>
/// <para>If a pattern in a LIKE clause contains any of these special characters * % [ ], those characters must be escaped in brackets [ ] like this [*], [%], [[] or []].</para>
/// <para>If the pattern is not in a like clause then you can pass valueIsForLIKEcomparison = false to not escape brackets.</para>
/// <para>Examples:</para>
/// <para>- strFilter = "[Something] LIKE '%" + DataTableHelper.EscapeLikeValue(filterValue) + "%'";</para>
/// <para></para>
/// <para>http://www.csharp-examples.net/dataview-rowfilter/</para>
/// </summary>
/// <param name="filterValue">LIKE filterValue. This should not be the entire filter string... just the part that is being compared.</param>
/// <param name="valueIsForLIKEcomparison">Whether or not the filterValue is being used in a LIKE comparison.</param>
/// <returns></returns>
public static string EscapeFilterValue(string filterValue, bool valueIsForLIKEcomparison = true)
{
string lb = "~~LeftBracket~~";
string rb = "~~RightBracket~~";
filterValue = filterValue.Replace("[", lb).Replace("]", rb).Replace("?*", "[*?]").Replace("%", "[%]").Replace("'", "''");
if (valueIsForLIKEcomparison)
{
filterValue = filterValue.Replace(lb, "[").Replace(rb, "]");
}
else
{
filterValue = filterValue.Replace(lb, "[[]").Replace(rb, "[]]");
}
return filterValue;
}
