Question

If I allow a group of users to submit "explain $whatever" to mysql (via Perl s DBI using DBD::mysql), is there anything that a user could put into $whatever that would make any database changes, leak non-trivial information, or even cause significant database load? If so, how?

I know that via "explain $whatever" one can figure out what tables / columns exist (you have to guess names, though) and roughly how many records are in a table or how many records have a particular value for an indexed field. I don t expect one to be able to get any information about the contents of unindexed fields.

DBD::mysql should not allow multiple statements so I don t expect it to be possible to run any query (just explain one query). Even subqueries should not be executed, just explained.

But I m not a mysql expert and there are surely features of mysql that I m not even aware of.

In trying to come up with a query plan, might the optimizer actual execute an expression in order to come up with the value that an indexed field is going to be compared against?

explain select * from atable where class = somefunction(...)

where atable.class is indexed and not unique and class= unused would find no records but class= common would find a million records. Might explain evaluate somefunction(...)? And then could somefunction(...) be written such that it modifies data?

Answer 1

"Explain" can take an arbitrarily long time to execute, and use an arbitrary amount of server resources, including causing it to crash if some things are exhausted (for example, a stack overflow caused by too many nested subqueries).

"Explain" can easily exhaust temporary disc space, server address space (on 32-bit systems, virtual memory on 64-bit systems) or the thread stack (for queries constructed deliberately maliciously).

In general, you cannot allow entirely untrusted users to submit any part of any SQL at all. Even without access to a single table, they can probably still crash the server if they try hard enough.

EDIT: further info

A query which uses an anonymous view / materialised subquery, often executes the entire inner query upon EXPLAIN, into a temporary table.

So queries of the form

SELECT * FROM (
  SELECT h1.*, h2.* FROM huge_table h1, huge_table h2) AS rediculous

will take forever to explain and consume the disc space in tmpdir.

Answer 2

I was able to use explain to get an accurate count of rows matching precise queries. So one could use

explain select * from user where name= tye  and secret like  %a%

to quickly determine the letters of whatever secret and then move on to determining the order of the letters, eventually revealing the value of secret .

Answer 3

If you want users to be able to connect to the database but not changing it, you should enforce that using user privileges-- if they only have SELECT privileges, they should not be able to change anything.

友情链接