no ability to escape variable when navigating between dashboard ( or even with text variable containing quote)

[TH-HA]

When using variable with a text box ( which can be entered manually or thru the navigation api ) , the plugin does not escape the content of the variable.

When using predicate such as a='$variable' ( or some more advanced one with ngram regexp ...) , CH may refuse the request. It should be nice to extend interpolateQueryExpr to also escape strings or at least to add an operator to escape the variable in such case.

To give a simple example:
If i store log in clickhouse and i want to filter the log values with something very simple
define a variable filter of type textbox
select log from log.logs where log like '%$filter%'
If the user enter something like < the system 'nifi' is overloaded > , then the query is no more valid as the text is not escaped.

I think Escaping does not need to be complex and can be limited to replace quote by quote/quote , /with / and also CR+LF management.
To avoid any regression an escape function can be better.

Regards

[hagen1778]

Hi @TH-HA

Have you tried $unescape - unescapes variable value by removing single quotes. Used for multiple-value string variables: "SELECT $unescape($column) FROM requests WHERE $unescape($column) = 5" ?

[TH-HA]

Hello @hagen1778
Yes i've seen this , but this solution in not acceptable in my case. I need to search for exact values in certain context and simply removing the quote lead to wrong result.
Having said that , i have a dirty workaround. As the search string is injected by navigating from a dashboard to another , i'm escaping the string using sql ( replace(string ,'''','''''') ) put the value in hidden column , luckily for me the source where the drill/nav is implemented is a table , so i can put hidden additional data containing this escaped string and use it when building the url of the target dashboard ).

It works , but it's not nice to see and as i want to reuse the same target dashboard to let the user enter the search string manually , i cannot hide the variable.

But thanks for the suggestion

[Slach]

@TH-HA thanks for reportings
latest version of Grafana allow following

SELECT *
FROM $table

WHERE $timeFilter AND $adhoc
$conditionalTest(AND content ILIKE ${filter:sqlstring},$filter)

look https://grafana.com/docs/grafana/latest/variables/advanced-variable-format-options/
for details

[Slach]

Also look 45cd6fa
this commit allows us to use something like %Inf'o% in text box, and variable will properly escape to single quoted string