Dynamic SQL Builder Form

Has anyone out there built a form that builds dynamic SQL by allowing a user to specify conditions using different operators, parenthases using both AND & OR, and to store the criteria in a DB table? This is relatively straightforward work, so if someone has something to share, I'd appreciate the time savings.

Rob

Find more posts tagged with

BPM

Comments

Jerome_Pearce

Rob,

Check out my 'IN Clause' example that pretty much does what you are after:

http://metastorm.processmapping.com.au/post/How-to-manage-an-IN-clause-4887375

Robert Vignerot

Thanks Jerome. I expected a response from you! I don't think that is quite what I'm looking for. I'm looking for something more along the lines of the Expression Builder does for conditions in the BPM Designer.

Brian Mellert

One thing to consider allowing users to specify any, or nearly so, condition they want ... the potential for SQL injection. Just something to consider if your firm is security conscious.

Jerome_Pearce

OK, then we have not done that, no. We did consider it once, although not in exactly the same way, but the idea of building a set of dynamic rules. The concept is sound, the main difficulty was the ides of precedence and brackets. Both are awkward, both in managing execution, and conceptually for the users.

I also agree with BMellert. Once you allow free text, you are wide open to SQL injection, and no decent security audit would let it pass.

Robert Vignerot

I certainly agree about SQL injection, but for my purposes, the statements entered will not be via text fields, but will likely be validated entry using dropdowns. I guess I'll just end up building my own.

Tony Wheeler

Rob,

Yahoo UI framework has a JS library called QueryBuilder that may be worth looking into. I think they've figured out a lot of this already: http://jafl.github.com/yui3-gallery/querybuilder/

There's also a jQuery plugin that does something similar but it didn't support IE last I checked.