MySQL CASE with user parameter in WHERE clause

I am trying to obtain all days to a month in my MySQL database. This query is being called in one of my Grafana dashboards that has a variable for the year, the month and the day. This query is supposed to get all the days there are to the selected month. The user can select a written month, but the months in my table are numbers. Since Grafana doesn't seem to allow you to value map the variables, I'll have to do that in my SQL query. Sadly the query doesn't seem to work and only returns this error: [42S22][1054] Unknown column 'January' in 'where clause' Of course the month depends on what the user has chosen. This is my SQL query:

SELECT DISTINCT DAY(measurement_timestamp) AS timestamp
FROM my_table
WHERE EXTRACT(YEAR FROM measurement_timestamp) = ${Year}
  AND EXTRACT(MONTH FROM measurement_timestamp) = CASE
                            WHEN ${Month} = 'January' THEN 1
                            WHEN ${Month} = 'February' THEN 2
                            WHEN ${Month} = 'March' THEN 3
                            WHEN ${Month} = 'April' THEN 4
                            WHEN ${Month} = 'May' THEN 5
                            WHEN ${Month} = 'June' THEN 6
                            WHEN ${Month} = 'July' THEN 7
                            WHEN ${Month} = 'August' THEN 8
                            WHEN ${Month} = 'September' THEN 9
                            WHEN ${Month} = 'October' THEN 10
                            WHEN ${Month} = 'November' THEN 11
                            WHEN ${Month} = 'December' THEN 12
    END
ORDER BY timestamp DESC;

Solution

Your Grafana client seems to be doing interpolation, not parameters.

There's a difference between interpolating a value into an SQL query before it is parsed, and sending a parameter to a prepared query after it is parsed.

The difference is important when the value you are sending is a string.

...
WHERE EXTRACT(YEAR FROM measurement_timestamp) = 2023
  AND EXTRACT(MONTH FROM measurement_timestamp) = 
  CASE
   WHEN March = 'January' THEN 1
...

You can see that if we use interpolation, it's okay to interpolate an integer value like 2023. There's no syntax problem.

But if we interpolate a string value like March, it ends up looking like a column name or SQL keyword, because it's not inside single-quotes, as string literals must be.

This would work:

...
  CASE
   WHEN 'March' = 'January' THEN 1
...

A true SQL parameter would send the value after the query is parsed by the server as a prepared SQL statement. In that case, you don't have to use the single-quotes, because a parameter is always treated as a scalar value, regardless of whether it's a string or an integer. It won't be ambiguous to the SQL parser, because the value isn't seen by the parser. It's combined with the prepared SQL statement during execution.

I'm not sure if Grafana can do true SQL parameterized statements. That's something for you to investigate in the Grafana documentation, if you want.

In the meantime, you could solve this problem with interpolation simply by putting the variable inside single-quotes:

...
  CASE
   WHEN '${Month}' = 'January' THEN 1
...

Be warned that this is in fact a potential SQL injection vulnerability. If you can't ensure that $Month is always a safe string, one of the 12 month names and never anything else, then it's not a secure way of running queries.

By the way, you commented that you don't like the repetitive expression. You should know that CASE expressions can be written with another syntax like this:

...
  CASE '${Month}'
   WHEN 'January' THEN 1
   WHEN 'February' THEN 2
...

So you only need to write the variable once, and the WHEN clauses only have the value to compare to (this works only if you would compare with =, and the left side of the comparison is the same for every case). This is standard syntax in every implementation of SQL.