spring-projects
Escaper does not escape the escapeCharacter itself
Escaper escaper = Escaper.DEFAULT;
String escaped = escaper.escape("a\\b%c");
will produce a string a\b\%c, where "%" is escaped but escape character itself is not escaped.
It leads to repository methods: "...StartsWith...", "...EndsWith...", "...Contains..." to return invalid results if search value contains escape character.
For the table:
| id | title |
|---|---|
| 1 | ab%cdef |
| 2 | a\b%cdef |
running:
repository.findAllByTitleStartsWith("a\\b%c")
will generate:
Executing prepared SQL statement [... WHERE "entity"."title" LIKE ?]
Setting SQL statement parameter value: column index 1, parameter value [a\b\%c%], value class [java.lang.String], SQL type 12
and will return
| id | title |
|---|---|
| 1 | ab%cdef |
Instead of:
| id | title |
|---|---|
| 2 | a\b%cdef |
which is returned by the query ... WHERE "entity"."title" LIKE 'a\\b\%c%'
You're right, this is indeed something we have to consider. As per the docs of various databases:
Posgres:
To match a literal underscore or percent sign without matching other characters, the respective character in pattern must be preceded by the escape character. The default escape character is the backslash but a different one can be selected by using the ESCAPE clause. To match the escape character itself, write two escape characters.
MySQL:
MySQL uses C escape syntax in strings (for example, \n to represent the newline character). If you want a LIKE string to contain a literal , you must double it.
Oracle:
You can also search for the escape character itself by repeating it. For example, if @ is the escape character, then you can use @@ to search for @.
DB2:
The pattern must not contain the escape character except when followed by the escape character, '%' or ''. For example, if '+' is the escape character, any occurrences of '+' other than '++', '+', or '+%' in the pattern is an error.
SQL Server:
If the character after an escape character isn't a wildcard character, the escape character is discarded and the following character is treated as a regular character in the pattern. These characters include the percent sign (%), underscore (_), and left bracket ([) wildcard characters when they are enclosed in double brackets ([ ]). Escape characters can be used within the double bracket characters ([ ]), including to escape a caret (^), hyphen (-), or right bracket (]).
SQL Server seems to follow the same rule.
