I am trying to generate a MySQL query dynamically using a variable. The query runs fine without the variable and returns 7 results when I just type the column name (clicks.LPID) but when I use a variable with string value "clicks.LPID" it fails to GROUP BY and returns single row.
It also doesn't work when I use a variable with integer value "3". It works fine if I just type 3.
I tried switching off the prepared statements, then the query returns no results.
Example query ->
SELECT clicks.name, sales.SUM(sales), clicks.LPID, sales.click_id
FROM sales
JOIN clicks
ON sales.click_id= clicks.click_id
AND sales.campaign_id = {{ url_params.value["camp_id"] }}
WHERE CAST(sales.sale_date AS Date) BETWEEN {{daterangepicker1.startFormattedString}} AND {{daterangepicker1.endFormattedString}}
GROUP BY {{ state1.value }}
By default, all of our SQL queries are converted to prepared statements to prevent SQL injection, meaning that table/database names and SQL functions aren't able to be defined using a string created dynamically. The main reason we currently convert all statements into prepared statements, is so that users can't enter malicious syntax (like DROP TABLE) into the variable fields.
You can disable this setting in the resource setup, but keep in mind the potential of submitting dangerous SQL through any of the variables referenced in a query. Disabling prepared statements can also break other existing queries. If that's something you'd like to explore, I often recommend setting up another copy of a resource with that setting enabled to help limit the surface area that you have to keep in mind SQL injection for.
