SQL injection in /api/tsdb/query in grafana

1

Hi Team,

In Below example, how can restrict only select query in “rawSql” parameter?
Now i can able to append any Sql query like drop, insert, delete and select.
this is kind of injection.need to avoid this.
how to make only read (means SELECT)?

POST /api/tsdb/query HTTP/1.1
Accept: application/json
Content-Type: application/json

{
“from”: “1420066800000”,
“to”: “1575845999999”,
“queries”: [
{
“refId”: “A”,
“intervalMs”: 86400000,
“maxDataPoints”: 1092,
“datasourceId”: 86,
“rawSql”: “SELECT\n time,\n sum(opened) AS “Opened”,\n sum(closed) AS “Closed”\nFROM\n issues_activity\nWHERE\n $__unixEpochFilter(time) AND\n period = ‘m’ AND\n repo IN(‘grafana/grafana’) AND\n opened_by IN(‘Contributor’,‘Grafana Labs’)\nGROUP BY 1\nORDER BY 1\n”,
“format”: “time_series”
}
]
}

Thanks,
Karun

2

It is mentioned in the doc. For example for PostgreSQL datasource:

Database User Permissions (Important!)

The database user you specify when you add the data source should only be granted SELECT permissions on the specified database and tables you want to query. Grafana does not validate that the query is safe. The query could include any SQL statement. For example, statements like DELETE FROM user; and DROP TABLE user; would be executed. To protect against this we highly recommend you create a specific PostgreSQL user with restricted permissions.

1 Like
3

even set a read-only permission. The hacker can query what they want inside the database. Why need to pass the rawSQL as a request. it is a common sense of sql injection. Don’t simply put the responsibility to database admin

4

I agree with leochan, creating a user is not a proper solution because even if with only SELECT permissions, users can still wreck havoc in your database. What if they use SELECT count(*) FROM users WHERE username = 'a' AND password = 'b'; as a way to brute force accounts? This makes Grafana a stupid decision if you want to have a public dashboard. (Which is what I was going to do, but this is a huge deal breaker)

I’m pretty sure there isn’t any reason to keep the query on the client side. Why can’t Grafana (on the server side) keep the query stored there and just replace the variables (time, etc) on the server side before querying? Keeping it on the client side seems a stupid idea that I can’t understand why it was done like that.

And it is kinda sad how team members keep downplaying this issue because “you didn’t read the docs” when it is actually a big issue. For me at least the docs are saying “yeah create a user with only select because queries can contain bad sql statements!1!!!”" which I thought it was “If you write a bad SQL statement in your widget it isn’t our fault”, not “anyone can send any query to the DB by editing requests”.

I’m not using Grafana until this issue is fixed, this is a big issue and it is “Avoiding Malicious Users 101”, having Grafana allow arbitrary query execution is a big mistake.

And of course, this affects all data sources, not only SQL data sources.

1 Like
5

There is a reason: some datasources can by queried from the browser (they support also client mode, not just server mode).

I don’t understand why paranoid SQL users don’t solve it on the db level - create views, materialized views, whatever used DB provides and let Grafana user to access only those and not tables directly.

1 Like
6

I don’t understand why paranoid SQL users don’t solve it on the db level - create views, materialized views, whatever used DB provides and let Grafana user to access only those and not tables directly.

Then try solving the issue that users can still create malicious queries to grind your SQL server (or any other data source for that matter) to a halt, then I would agree with you. :slight_smile:

Anyway, here’s a workaround to everyone that wants to do this: You can use the GrafanaJsonDatasource plugin, then you can code your own web server to do the queries.

The backend URL is not exposed because it is proxied via your Grafana server and, because you have full control on what will be queried, you won’t have issues with data leakage! This is also useful if you want to connect Grafana to other data sources that aren’t supported in Grafana yet. (And very useful if you want to workaround Grafana’s crappy ElasticSearch support)

So in my case, I set a custom type in my panels with the JSON API plugin (example: pending-requests, http-requests, etc etc etc) then, when Grafana queries the data source, the data source queries my http server, then my http server checks what is the type that is being requested, retrieves the data from the database (PostgreSQL, ElasticSearch, Prometheus, etc) and returns the data accordingly.

This also has another huge advantage for public dashboards: Users aren’t able to query gigantic time ranges with the intent of crashing your dashboard, because you have full control on what is going to be queried, you can just coerce the input time to be something less malicious, yay!

Of course, users can still spam (DoS attack) your Grafana server to grind your database to a halt, but the attack surface is so small and the fix is so simple (throttle requests or cache requests) that this is a huge advantage compared to the current solution. (Which is: “lol just create a new user 4head”)

And yes, you still need to implement a bunch of other stuff in your web server to have the JSON API plugin behave exactly how you would expect compared to Grafana’s default data sources. But at least for me, this is a good workaround without any big downsides and a lot of big advantages.

1 Like
7

Grafana 9.1 has a new feature that allows you to create Public Dashboards without allowing users to inject arbitrary queries!

2 Likes
8

This is great - i’ve had a look at the request sent on influx/sqlite/postgres and none has query to the DB - thanks a lot for pointing this out!

9

You mean the user not able to see the raw query ?