http://qs321.pair.com?node_id=643356


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.