Variable formatted as regex & MySQL double-escaping of backslashes

I’ve found a regexp escaping issue when using Grafana + a MySQL datasource. Let’s assume a simple dashboard including a variable location that can take one or more values: madrid, london or whatever(42). Using that value we can build the name of the metrics to be retrieved from the metrics table (i.e. foo.madrid.value, foo.london.value, foo.whatever(42).value, etc.). We want to do that using the REGEXP SQL operator:

  ...
  WHERE
    metrics.name REGEXP '^foo\\.${location:regex}\\.value$'
  ...

If madrid and london are selected that SQL string will be expanded to ^foo\\.(madrid|london)\\.value$. No problem with that. But if the value including the parentheses is selected, the SQL string will be expanded to ^foo\\.whatever\(42\)\\.value$, which is not ok because in MySQL strings backslashes need to be double-escaped. In other words, a metric named foo.whatever42.value would match the generated regexp string, but not foo.whatever(42).value.

We’ve considered adding NO_BACKSLASH_ESCAPES to sql_mode before the query is executed and restore it to previous value just after that, but it’s ugly and I don’t think is even doable. Any tricks to workaround this?

Hi @carlosabalde

Not sure but maybe this solution will be of some use?

Thanks for the idea @mattabrams, but I’m afraid is not useful here.

We use the :raw thing for uses cases like the one described in that thread and it’s a reasonable workaround. That’s because when not using any special interpolation, the interpolation implementation is datasource dependent, ant that means it’s going to do the right thing. This is not 100% true because we’ve also checked escaping in the MySQL datasource is incomplete, but that’s a different story reported as a bug: [Bug] Escaping issues with MySQL datasource · Issue #36800 · grafana/grafana · GitHub.

In any case, here we’re using the REGEXP operator, so we need the :regex interpolation and proper escaping.