Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Never Let Them Read From Your Database 3 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • Why not give them a dump of your whole database and let them load it on their own server? It won't keep up to date, but if ad hoc queries are all they need then that won't matter. Reinventing the wheel by implementing your own home-grown query language instead of SQL may make sense in some cases, but it's not necessarily the best way.
    --
    -- Ed Avis ed@membled.com

    • Thought about that, but they need live data. Our data changes rapidly and being even one day out of date is like playing the stock market by reading a day old newspaper (well, ok, not quite that severe :). It would be good to have a series of read-only slave servers, but that still puts us in the position of them insisting that we can't make that important database change just yet. We've had that happen enough times that we have nasty hacks in our code and database [perl.org] to work around these issues.

      • How about an interface that lets them submit arbitrary SQL queries, but checks them against a whitelist first. So for example your customer might say 'we need to SELECT COUNT(*) FROM FOO' and you would say 'that seems fine, I will add it to the list'. The next day they ask for 'SELECT FRED FROM BAR' and you decide no, the FRED column is an implementation detail I don't want to support forever, so I will not allow them to make that query. That way you have control over what's happening.

        If they want a particular query, it's then your call whether to permit it, do the work to add it to your RESTful interface instead, or pick some compromise like making a view for them to use. Or, indeed, deny the request. This gives you more options than allowing or disallowing SQL queries as a whole.

        If you want to be especially evil, the SQL gateway can have a mortality rule so that ad-hoc queries are allowed only for one week after they're added, and after that automatically disabled unless re-requested. This could sometimes be better than adding a new documented interface to your API just for a very temporary need.

        --
        -- Ed Avis ed@membled.com
Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.