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.

In reply to Re: Building SQL Query on the fly by snopal
in thread Building SQL Query on the fly by hallikpapa

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.