Building a dynamic query

[jbird5k]

I have a need to build a truly dynamic query, that would be able to take online form input and create a query that would handle data requests like, How many employees earn less than #some salary, have a masters degree, and speak chinese.  or show me all the employees in org A that are vets.

A true ad hoc query tool. 

the back end is oracle 11g, the database  contains about 8 or 9 tables all total there about 500 records that will be searched. we are running CF7.

I just started kicking this around today so I am open for Ideas.  add filters in the where clause, i got that,   start with  1=1 and use conditional statements to add filters as necessary.  setting up the Select and From statements, need help there.

Since this is a small db, I thout about, but scrapped, making a super-view to query from.

your thoughts and suggestions would be greatly appreciated.

thanks

jb

[Joshua_Cyr]

One important item to consider is security.  If you are not already familiar with sql injection, you should do a bit of research on it, and cfqueryparam tag.  Can't stress that enough.  Beyond the cfqueryparam tag there is more to do though, regarding sorting, grouping etc.  If you pass in parameters to power that you have to protect that section as well. http://www.coldfusionmuse.com/index.cfm/2008/7/21/SQL-injection-using-order-by  basically any cf variable directly put into a query needs to be protected or abstracted, or compared against known value options.

This should be done regardless if the report you are building is public facing or not.

As for the approach, there are lots of options.  Some of which you are already considering.  Sometimes it is easier to grab a bunch of data in a small db, then to lots of query of queries on it for reporting, though this really depends a lot on how the data is related, number of records, etc.  For example you can do couple queries, then join them via code.  Or loop over them and run sub queries.  Again this is all based on your db and reporting needs.

If you think that one dynamic query is getting too complicated, you can have a couple.  Check the params coming in, if a certain combo of them exist, then try this simple (er) dynamic query, else this other different kind of dynamic query, etc. 

[ilssac]

The biggest question is how much are you hiding the SQL from the end users?

IF you have a SMALL, well DEFINED, and SQL knowledgeable user base, and you can GUARANTEE that ONLY this user base will have access to the tool, your interface could be as simple as a single text box for these users to type in their SQL.

That is a lot of conditions that almost never exist, but it has once for me.

Otherwise, your main concern is how friendly and fool proof your user interface needs to be.  Does it have to walk the users through each and every step of selecting various criteria or can it expect to be used by users that have at least some basic understanding of the data scheme and how to query it to get the desired output.

After that, it is pretty easy, you have some level of data that represents something from a full SQL statement to a set relevant filters and criteria.  You then just utilize basic string building functions to create the full SQL statement and pass it to the database through a <cfquery...> and|or <cfstoredproc...> tag.

The most IMPORTANT thing to make priority number one, is that if you are poviding ANY means for users to provide input into SQL, you had better use EVERY trick in the book to protect the data in your database from accidental or malicious SQL code that would damage or destroy your data.

• Scrub the input to an inch of its life
• Use a database user that has one the barest minimum permissions to the data needed for their required functionality.
• Tune the ColdFusion database driver to only allow the necessary SQL commands. (Not very secure, but why not)
• Back-up, the data often and thoroughly.

Just for starters

[Dan_Bracuk]

My suggestion is to write down exactly what you want to be able to do, and what you don't want to be able to do before coding anything.  I've done things like this before, and you have to be very methodical.

[jbird5k]

Thanks every one for your input,

<cfqueryparam>, and row level security on the tables is already inplace as a coding standard here. However, running it through its own data source with limited SQL permission is a good idea.

as for UI, i want to go with something that allows them to pick options where ever possible as there are a few employees around here who would reak havoc given the oportunity to write their own SQL.  The primary users of this functionality is going to be executives, and a few assistants, so less than fifteen.  During our last design meeting i asked what kind of outputs are you looking for from this and their response [with straight face] "oh I don't know, but I want to be able to see all of it"

thanks again for your input

JB

[insuractive]

What about something like this:

1) Determine what tables are going to be involved (visual GUI/drag & drop interface for the users?)

2) Have a table that defines the relationship between the other tables in your database - once the table selection is made by the user, you can query this table to determine what columns you are going to join on.  Obviously this won't allow you complete customization but it would work for the scenarios you describe.

3) Use your database system properties to retrieve the field names from your included tables

4) Allow your users to add "filters" based on the fields retrieved in (3)

e.g. You could use an interface to allow users to add a filter, where you display a list of fields from your tables and allow them to apply a simple filter to them - keep it simple (=, !=, >, <)

for bonus points, you could limit the options based on the field type (checkboxes for bit fields, etc)

sanitize the user input - find a SQL-safe reg exp script to handle any unwanted characters (I'm looking at you apostrophe).

5) Don't try an support aggregates if you don't need to - users can load the results into excel and manipulate them to their hearts content.

6) Build your SQL using the selections made by the user.  Import all records from your joined tables.  Use the table from (2) to determine which fields they join on.  and build your where clause using the filters the user created in steap (4).  Again, be sure anything not explicitly set by the system is sanitized prior to making its way into the SQL.

7) Execute() your SQL inside a CF query.  Expect pretty poor performance, since most SQL databases won't optimize a query plan from a dynamic SQL evaluation.

You could build this as a multi-step wizard in CF, but it would be pretty flashy as a jQuery/web service based application.

[Scott_thornton]

Hello,

There is  a product named Business Objects WebIntelligence XI, now owned by SAP, that would perfectly match your requirements depending if you have a bit of budget ( Probably a BIG budget).

it has a drag and drop interface and allows the creation of runtime parameters. it will produce the sql required and present the information ( exports, charts, diagrams etc)

For example if you had tables similiar to Product and Orders, you create what they refer to as a Universe, and within it you design your Product Object, and Product paramters etc, and within the reporting module ( web accesible of course) you might drag onto a new report say "Product Name", and Count of Orders, drag out your "orders in period" parameter and press run. Automagically the required SQL is written, executed, ad the report is formatted neatly on the page.

BO ( Business Objects) is typically used by Corporations\Enterprise, hence the big budget.

I have found in my experience however, that there are very few truly ad-hoc requests, and most reporting can be met by a few or more standard reports with selectable parameters.

[jbird5k]

Scott,

Thanks for your input,

I am familiar with Business Objects, I used it on a previous project, and the main issue is budget, don't have one,  this all to be done in house with available tools.

Thanks again

JB