On Fri, 16 Jul 2010 00:16:48 -0500 Larry Garfield <larry@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