sql_sub_vars needs to escape formatstring characters.

[itsme@…]

in report.py, in the function sql_sub_vars before substituting variables, the whole string should be format-string escaped

• '\' should be replaced with '\\'
• '%' should be replaced with '%%'

this will make it possible to write things like: field LIKE '%$PARAM%' in reports.

old tickets that relate to this subject: #1418 and #2536 and #2568

[Christian Boos]

Milestone 1.0 deleted

[Remy Blank]

This issue is specific to LIKE-style operators, where wildcards can be used but should not necessarily be visible to the user.

While it would be possible to escape wildcards in the variable values automatically, doing so would require parsing enough of the SQL to find LIKE-style operators (REGEXP, GLOB, MATCH, …) and escaping each occurrence properly, which is tricky to get right cross-backend. Moreover, it would prevent writing reports where the user is supposed to be able to use wildcards.

So I suggest doing the escaping in the SQL itself (if desired), by using REPLACE() to escape the wildcards (example for SQLite):

SELECT id, summary, cc FROM ticket
WHERE cc LIKE '%' ||
              REPLACE(REPLACE(REPLACE($CC, '/', '//'), '_', '/_'), '%', '/%')
              || '%' ESCAPE '/'