Re: ORDER BY in prepared statements

>
>
> On Wed, Jan 21, 2015 at 4:09 PM, Bryn Jeffries <
> bryn(dot)jeffries(at)sydney(dot)edu(dot)au> wrote:
>
>>
>> Maybe what we need in ODBC libs and the like is a "protected
>> statement" that follows the same construction as a prepared statement but
>> additionally checks catalogs to validate identifiers.
>>
>
​I'm not sure whether this would actually be a feasible solution to the
problem. Note that most frameworks (well, the format solution I outlined
at least) for doing identifier replacement safely require that you actually
tell the system what is expected to be an identifier and what is expected
to be a data value.​ The general implementation is that, in the case of
PostgreSQL, double-quotes will be added to the identifier value if required
to make it a valid identifier. Since any injection would rely on supply
mandatory quote identifiers this solves the problem quite neatly.

​The one part I am not positive on is dealing with case-folding when using
format's %I placeholder; this seems to be a documentation deficiency though
I may just not have found it yet...or reasoned out the logical outcome
(which I shouldn't need to do)...

Catalog lookups would be expensive to do pro-actively. The goal is to form
a safe query for the parser and let the planner deal with any identifiers
that end up being invalid either through attempted injection or simply
usage errors.

David J.