Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Re: Building SQL Query on the fly

by snopal (Pilgrim)
on Oct 08, 2007 at 02:20 UTC ( #643356=note: print w/replies, xml ) Need Help??

in reply to Building SQL Query on the fly

It is absolutely essential that you separate query selection from SQL assembly. If there is any chance that a query can be hand crafted by a user through any form of transmission methodology, you must validate the selections and the value matches explicitly. To do otherwise is courting disaster by failing to control your data.

In every system that I have used, I have found that the program must take explicit logic to control the passage of that request. You can automate the validation, limit the query set by value, and/or provide free text fields throught text processing. I just don't assume that my or corporate best interests are shared by the user.

Well thought out query formulation on the back-end is just a matter of good design. My processes have usually been designed around separate variables for the fields to return, the from/join tables and associations, and each of the where/group/order/limit components. With simple if blocks I control sets of fields and their associated query requirements.

So, for a certain set of each field groups, inclusion of the explicit components to satisfy the query are appended.

I've also found it most useful to control the actual report returned by reading the query response and transmitting the data using the order and field filtrations appropriate for the results. Things like date format, currency/decimal format, internal security conversions (protecting privacy/security information) usually benefit from post query conversion.

There is no getting past security concerns at all levels. No matter who you are serving, it is necessary to assume that abuse will be tried. No simple conversion from user to result is going to give you that protection.

Replies are listed 'Best First'.
Re^2: Building SQL Query on the fly
by hallikpapa (Scribe) on Oct 08, 2007 at 02:30 UTC
    Thanks for the replies guys. I am not too concerned about abuse or security for this first run thru. I would just like to be able to do the things I mentioned above. The front end is built in ruby on rails, so I am not sure I can use template toolkit.
        Actually I was suggesting using it for the xml output, but using it for the sql generation is in interesting angle too. probably a bit overkill though.
    A reply falls below the community's threshold of quality. You may see it by logging in.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://643356]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (11)
As of 2020-10-30 12:34 GMT
Find Nodes?
    Voting Booth?
    My favourite web site is:

    Results (278 votes). Check out past polls.