0

Is it possible to use variables as parts of the query?

Somewhat like this:

SELECT UNIX_TIMESTAMP(time) as time_sec, sample_value as value

FROM SOMETHING + VARIABLE_HERE+ SOMETHING ELSE

WHERE adb.patients_patient_id = $pid

Atle Kristiansen
  • 707
  • 7
  • 28
  • [Google search "mysql user variables"](https://www.google.com/search?q=mysql+user+variabes) .. Besides there is a (possible) duplicate on Stackoverflow [How to declare a variable in MySQL?](https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql) – Raymond Nijland Nov 30 '18 at 14:05
  • Maybe some clarification is needed, the query is sent through grafana, not directly to the database. – Atle Kristiansen Nov 30 '18 at 14:07
  • `WHERE adb.patients_patient_id = $pid` i hope this is pseudo code if not also look for information on how protect against SQL injections. – Raymond Nijland Nov 30 '18 at 14:08
  • I will reiterate, this is _from_ grafana. – Atle Kristiansen Nov 30 '18 at 14:12

1 Answers1

1

Yes it is possible, from the documentation. In the section "Using Variables in Queries" they show how to use variables inside of a MySQL query from Grafana. $hostname here is the variable:

SELECT
  UNIX_TIMESTAMP(atimestamp) as time,
  aint as value,
  avarchar as metric
FROM my_table
WHERE $__timeFilter(atimestamp) and hostname in($hostname)
ORDER BY atimestamp ASC
Blokje5
  • 4,763
  • 1
  • 20
  • 37
  • and how is hostname defined? – Raymond Nijland Nov 30 '18 at 14:10
  • As a variable in grafana. However this isn't really what I am looking for, as the variable is only part of the tablename. 123+variable+456. – Atle Kristiansen Nov 30 '18 at 14:14
  • sound dangerous @AtleKristiansen MySQL's prepare protocol and or [PREPARE clause](https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html) does not support prepared statements in identifiers like table name.. So i doubt frameworks can implement this feature safe if it is even possible in grafana.. besides it sounds like you have alot of the same tables because why otherwise would you want to query tablename. `123+variable+456.` – Raymond Nijland Nov 30 '18 at 14:22
  • The value assigned to the value cannot be changed from the front-end, like one could if it was communicating directly with the database. However if it was possible to change the value of the variable, I agree with you that it would be vulnerable to sql injection. – Atle Kristiansen Nov 30 '18 at 14:26
  • Yes, there are alot of tables with similarties in their name, as the data is aggregated, hence we found it logical to name it according to its source. – Atle Kristiansen Nov 30 '18 at 14:28
  • logical? using "metadata" as table name is a bad idea in fact it's a common pitfail @AtleKristiansen you should have a table `source_type: id, source_name` and use that id in a table `source : id, source_type_id ` ... if you worry about table size or performance you can use [partitions](https://dev.mysql.com/doc/refman/8.0/en/partitioning.html) which is supported by MySQL since version 5.5 – Raymond Nijland Nov 30 '18 at 14:47
  • That is a fair point. As this is just a project, and will never be used in production I wouldn't change it here, but I will definitely do that in the future. – Atle Kristiansen Nov 30 '18 at 14:51