Custom plugin injects SQL

I’d like to create a plugin that allows me to:

  1. Endpoint to create/update users with a company_id field;
  2. Inject this company_id field into the query builder to do something like this:
SELECT * FROM orders WHERE company_id = $__company_id

I don’t want to allow users to change their company_id field and also this data injection onto the query builder must be safe. Any suggestions?

PS:
3. Is there a way to create custom forms? Where do I find these kind of documentation?

2 Likes

There is no safe way to do this with the current sql datasources. Grafana doesnt understand the SQL that gets send to the database it is just a string. Grafana does have macros to help you build the query but thats just for your convenience. All grafana cares about is that the resultset that is returned is structured in a certain way how this resultset gets build is up to the database.

To implement this grafana would need to understand sql to make sure that your company constraint isnt ignored in some part of the query.
Like in those examples:

SELECT * FROM orders WHERE 1 = 1 OR company_id = $__company_id
SELECT * FROM orders WHERE company_id = $__company_id UNION SELECT * FROM users

If you want to implement this safely its probably best to utilize the authorization features your database already supports and implement a custom sql datasource.

1 Like

Thank you so much for your feedback. In our case it is safe because we are the one creating the queries and allowing view-only users. Do you have any idea on creating custom macros or access to the query builder in order to replace these $_company_id variables from the user that is logged in?

2 Likes