Re: Easy power-user ad-hoc tool

[Quoted] Unless you parse the input VERY carefully, a malicious user could truly trash your server... Any Sql with a HOST command in it could access the OS and do anything....

Robert Malos <bob.malos_at_csu.mnscu.edu> wrote:

>If you're just looking for a query tool:
>
>I created a web-based ad-hoc query tool back in 1995 that has been in use
>at the University of Minnesota (basically unchanged) ever since. It has
>enabled many hundreds of users to build and run hundreds of thousands of
>queries. I'm not trying to sell anything (and I don't own it anyway) but
>it would be very easy to duplicate.
>
>The key is that all it does is generate SQL (or let a user just type some
>in) that simply gets executed as a batch via that database's command line
>tool (isql for MS or SQL*Plus for Oracle). That means that it can accept
>absolutely anything the the command-line tool accepts - anything. And the
>results are returned to the web server as a simple text file (and all the
>security is handled by the database). Then I found a third-party tool
>called DBMS/Copy that lets you convert text files to many other formats
>"on the fly" (with no need to create a "conversion" beforehand). But both
>isql and SQL*Plus let you return basic comma-separated files.
>
>A very basic web form that just presents a text box and sends that text
>through the command-line tool is very easy to do. For the
>Query-By-Example I just wrote a series of scripts that present first a
>list of tables then a list of columns and then a form to generate
>conditions. It can easily generate Joins and Group-By's by reading the
>database schema and acting accordingly.
>
>So this tool has been used by everyone from true beginners who don't know
>anything about SQL to expert SQL writers to DBAs (it will also accept
>administrative commands and stored procedures and PL-SQL, etc --
>*anything* that isql or PL*SQL will accept).
>
>True - this is *not* a report writer. But over the years I (and others)
>have tried to replace it with something much fancier but our users
>continue to prefer this "primitive" tool (it was finally changed from CGI
>scripts to Active Server Pages but it still works the same way). I have
>experience with both Cognos and Brio (and have looked at a lot of other
>tools). If a tool is truly "thin client" then all the work is done at the
>web server (or application server) level and that's fine if the result
>sets will always be small. But our users sometimes brought back thousands
>(or every tens of thousands) of rows and then that server's memory got
>used up very quickly. If a tool is "fat client" then you have a plug-in
>or Java applet or something else which has to be downloaded to each
>end-user and performance depends on the power of that user's desktop.
>
>And - best of all - if you make your own simple query tool then you can
>add some little custom features that no off-the-shelf tool would have
>(over the years we've done things like 'saved queries' for each user, a
>query log of every bit of SQL that has ever been run, row and time
>limits, etc.).
>
>The best off-the-shelf tool I've seen lately is Hotquery -
>www.hotquery.com (written with Cold Fusion). The only problem I had with
>it is that it *is* truly thin-client so my web-server's memory got
>swamped pretty quickly (but they may have some way to deal with that
>now).
>
>Bob Malos
>
>
>Jared wrote:
>
>> Hi, all -
>>
>> I am looking to replace Access within my user base. With one
>> exception there is no VBA to worry about (and I will take care of the
>> exception). Basic requirements are: ability to access Oracle and SQL
>> Server (2000), output Excel files. I am trying to avoid the tools
>> that require catalogs in teh middle (we have Cognos Query in house if
>> I want to go that route).
>>
>> I have tried Discoverer and consider it slow, btw, but in terms of
>> user interface it is OK.
>>
>> Without igniting a flame war, I would appreciate some feedback fro
>> people who have implemented such a tool within their user base with
>> good results.
>>
>> Thank you!
>>
>> Regards,
>> jh
Received on Tue Dec 17 2002 - 22:17:45 CET