How is the data explorer secured?

Question

I understand that the SQL user probably doesn't have any sort of permission to modify the database, and that very heavy caching is probably used in addition to the fact that this is on a data dump instead of the live database, but how the heck is the data explorer not lagging like crazy from the fact that any user on the internet at all can run any select query he or she desires? I'd imagine the server load would be enormous. Is there any sort of filtering going on to ensure malicious queries are not run? Is it even possible to run a query that could be considered malicious? I know the database itself is safe, but surely all these arbitrarily run queries would slow the server down to a crawl?

Pardon my curiousity, I've just never been allowed to run arbitrary SQL on someone else's server with absolutely no inherently visible limits excluding the fact that the database is read-only.

EDIT: It seems the access we're allowed goes deeper than I thought. I also noticed people's queries like this one making tables and inserting data into those tables. Are these tables cross-question? How is abuse prevented with these? Are the tables cached for that question only so the queries run more quickly?

Answer 1

First and foremost it has the benefit of having relatively dedicated hardware, sharing its space exclusively with the chat platform. I don't have any figures on how much traffic the Data Explorer sees, but it seems to be reasonable enough that current demand doesn't appear to have any negative impact.

Obviously even the best hardware can buckle under pressure, of course. To account for that in the case of non-malicious users, the Data Explorer caches result sets to mitigate performance hits for common queries as you expected, which works especially well for queries that don't take parameters since the cached results can be returned for every request.

As far as queries go, no filtering is done on the SQL you submit, so as long as the database user has permissions to execute it you can run it. With respect to the temporary tables created in your example query, they only exist for the duration of the SQL Server session, which will terminate after the query is run. That is, you're only creating those tables for yourself momentarily while the query is executed and then they're automatically removed.

The Data Explorer does try to limit your query from getting too unwieldy by setting CommandTimeout to two minutes. Malicious queries in this case would likely be those that required a long running time or a lot of memory, and the Data Explorer keeps a log of executed queries to detect such abuse cases.

The Data Explorer also makes use of rate-limiting to automatically prevent users from excessively burderning the server with queries within a given amount of time, which in addition to the query log mentioned previously allows for the blacklisting of abusive users. Non-authenticated users also are required to complete a captcha to limit the potential for abuse by automated bots.

There's likely many other details I've overlooked or am not aware of, but hopefully waffles will come along and share some additional insight.