In one of my apps user entered [ (open bracket) in the search field and the query started to give errors. Unterminated pattern
Is there a way around this
His reply went to a ticket, but indicated that he's using LIKE, so for anyone else who runs into this...
The pattern-matching in the LIKE function has a few special characters. % matches on any number of any characters. _ matches any single character. And [abc] matches on any of the characters inside the brackets--in this case a b or c. So, if a [ is used by itself, the pattern can fail to parse or can otherwise behave differently than the user intended or expected.
There are a couple ways to work around the special meaning of [. You could use the ESCAPE keyword (part of the LIKE function) to define an escape character and then use it to escape the left-bracket (using Replace), such as: WHERE vendor LIKE 'BETTS\[' ESCAPE '\' (Note that the argument to the ESCAPE keyword must be a string literal, not a variable, from what I'm seeing in the code.)
Or you could try using [ as the escape character as in: WHERE vendor LIKE 'BETTS[[' ESCAPE '[' and then use a Replace to replace each [ with [[ in the pattern argument. However, in testing this approach I think we've found a bug where the double-escape sequence will also incorrectly escape whatever character follows the [[ instead of only escaping the second [ character. This would also affect attempts to include any other escape character by escaping itself such as \\. If you tried to use a special character like % or _ (or even [ ) intentionally immediately after a double-escape sequence, then the special character would likely be treated as literal rather than special. If you're going to escape all special characters this may be less of a problem.
The other possible approach would be to use the interpretation of [ ] themselves to escape any left-bracket characters (not using the ESCAPE keyword). If you use patternString.Replace("[", "[") then it should convert the user's use of any [ into a pattern that will match specifically on a [ character. This approach is also not without potential pitfalls, but it appears to be safer. Assuming you also want to escape use of _ and % characters you could do patternString.Replace("[", "[").Replace("_", "[_]").Replace("%", "[%]") to replace all three--and make sure to replace [ before adding more of them in replacing the others. If you add your own % to the start or end, make sure to do so only after escaping any from the search field itself.
You're going to have to give more information about how the search field is used within a query.
Are you using LIKE or CONTAINS with the value of the search field used directly as the pattern argument?