[development] SQL and connection strings

[Larry Garfield]

For the connection string in D6, I believe you can muck with $GLOBAL['db_url'] 
at any time to add connections to it.  You may need $db_url to already be 
using the array format; not sure.  (In D7 you absolutely can add connections 
at any time, although there's a dedicated API for it.)

That said, if you're talking about totally arbitrary SQL strings without 
variable components you may be better off bypassing the D6 DB layer entirely 
and just using PDO directly.  Or, for that matter, use the D6 backport of the 
D7 DB layer, which is far more robust: http://drupal.org/project/dbtng

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().  

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.

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. :-)

On Thursday 15 July 2010 10:42:19 pm David Metzler wrote:
> I'm working on a contrib module  that will be used as an enterprise report
>  writer for drupal and  other external databases and XML feeds.   Some of
>  my users are interested in the capability to store ad-hoc queries and then
>  write reports off of them, (kind of like Crystal Reports).
> 
> My question is two fold:
> 1.  Do people think its untenable to store ad hoc sql queries either in the
>  drupal site files structure, or in the db?   Right now my module assumes
>  that all SQL that will be used for reporting is stored on the file system.
>   I did this because for most enterprise reporting systems I feel it would
>  be advantageous to be able to version control the SQL that is used in
>  these reports.  But ad hoc reporting really means writing your own
>  queries, right?  So do you think it's wrong to store them on file system
>  or in db? Is it irresponsible to provide such a feature?
> 
> 2.  The connection string (with passwords).  Right now I have the site
>  admins hack in connection strings for external databases into settings.php
>  in the sites folder.  That probably won't work for a true ad hoc reporting
>  solution, since we may want users to be able to connect to databases from
>  the UI?  Is it untenable to store these using variable_set?  Are there any
>  ideas for securing them inside the drupal DB?
> 
> I realize that there will be many opinions on this topic, but I really want
>  to get a feel for the opinions and the why of seasoned developers. 
>  Opinions that talk about risk trade offs would be much more welcome than
>  simple, "no you should never do that" statements.
> 
> More info on the project can be found at:
> 
> http://drupal.org/project/forena
> 
> Thanks for any input you all are willing to provide.
> 
> Dave
>