[development] SQL and connection strings

[Ivan Sergio Borgonovo]

On Fri, 16 Jul 2010 00:16:48 -0500
Larry Garfield <larry at garfieldtech.com> wrote:

> As far as the queries themselves, it depends on how much you trust
> your users. You certainly could setup a system quite easily where
> users can enter SQL into a node body or CCK text field and then a
> formatter or something like that takes that query, runs it against
> the DB, and shows the results in a table.  That's not at all hard
> to do, and much more robust and extensible than a variable_set().  

That's pretty nice since you can use revisions.
Of course it is not going to work as a full fledged rcs.

> The question is security.  Anyone who can write a data
> modification query (insert, update, delete) has root access to
> your site.  End of story.  I presume you don't want that.  In your
> case you're hitting a DB other than the main Drupal DB so that's
> the DB that would be at risk.

> So first off, you'd need to whitelist SELECT queries and disallow
> anything else. Then you'd need to be extremely careful to make
> sure the queries run only against the 3rd party database.  Then
> you need to determine if there are any tables in that DB that
> users shouldn't have access to and whitelist/blacklist to protect
> those.  That will likely take some interesting regular expressions.

Unfortunately if he is going to support multiple engines that's not
going to be easy.

Filtering SELECT may not be enough:
The SQL standard should forbid stuff like
select * from (insert into pippo values('pluto') returning topolino);
Still:
- I wouldn't blindly trust all the implementations. Maybe they offer
  some "feature" that let circumvent the standard. (dynamic
  definition of statement assigned to a variable that get "EXEC",
  "PERFORM")
- functions that produce side effects eg. PostgreSQL has nextval()
  but other engines may have others (that shouldn't be a problem if
  you can use GRANT)

For engines that support GRANT and REVOKE it is easier to enforce who
can do what in the DB.
Then he could associate roles with connection strings (different
users with different access levels already enforced in the DB).

Unfortunately not all the DB engine supported by PDO support GRANT
and REVOKE.
If he wants to offer some layer of security for those DB he'll
actually have to filter SELECT on his own.
Maybe in real cases where you don't have GRANT the DB engine won't
make it hard to prevent side effects of SELECT.
He'd be able to find pre-packaged php snippet to filter SQL and
avoid to forget all things other people thought before (UN/*
*/ION -- etc...).


Anyway I don't think programs like Crystal Report solve this problem.
So people plugging a reporting engine in a DB shouldn't expect it to
take care of their security concern.

It may be useful for reporting to be able to insert and execute
multiple statement (many things get faster and even easier to
think with temporary tables). That's something that is not generally
permitted through drupal abstraction layer.

He'd think if Drupal DB abstraction layer will mostly come into
his way or still be useful. I'd keep using Drupal DB AL mainly to
reuse Views for the most simple queries.

I'd say that the most challenging part of emulating something like
Crystal Report would be the UI and the WYSWYG part, and if he is
planning to go that route then... Views is going to be just a
Pre-view.

Summing it up he may have roles that:
- map to different connections, db users with different GRANT
- have different SQL filters:
  - GUI only
  - SELECT only
  - Full SQL

And add a paragraph to the module documentation that warns about
security of letting people enter unfiltered SQL.

I'd warn not only about data modification but also about resource
exhaustion. Maybe hiding too well the DB behind a nice UI will make
them forget there is still a DB sweating behind ;)

> All doable, but touchy depending on the details.  And it requires
> having intimate knowledge of SQL and that particular DB to write a
> query, and if you make a syntax error you have to re-edit the
> node, which means your query runner cannot possibly crash the
> request if there is a syntax error because you'll make it
> impossible to use the site.

?

> Alternatively, what type of SQL DB are we talking about here?  If
> it's MySQL... Views can support 3rd party SQL databases just fine,
> even in Views 2. Just throw Views at it, give selected users
> access to the Views UI, and call it a day. :-)

If he is going after the same users of Crystal Report he will have
to let people insert SQL.
There are things that simply can't be done with aggregates and join
only and most of them are the real juice of reporting.


-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it