Re: SQL injection

Jim C. Nasby wrote:
> On Tue, Nov 01, 2005 at 08:27:21PM +0200, Yonatan Ben-Nes wrote:
>
>>Won't that create a performance penalty to extremly dynamic sites cause
>>the plan will be planned only once and the data may vary alot?
>>Beside that I still won't have a solution to places where I create a
>>query which can vary alot (JOIN diffrent tables, diffrent WHERE etc...),
>>it doesn't seem logical to me to start and create all of the diffrent
>>possibilites of queries when I create such an option at a site.
>
>
> Yes, when you start getting into dynamically generated SQL you quickly
> loose the performance benefit of prepared statements just because odds
> are good that nothing else will use it. But you still have the benefit
> of bound parameters and protection from injection.

My problem with the dynamically generated SQL is that I'll have to
create and maintain lots of prepared statements and be sure that I dont
miss any available option, and also every time that ill have to do basic
changes at the queries I'll have to update each one of those prepared
statements.... it seems to me like of extra work for sites which can
create many dynamic queries.

And about the performance penalty, I don't really care about losing the
benefit of prepared statements, I'm actually more afraid of receiving
penalty of using them... the following is quoted from the manual:
"In some situations, the query plan produced for a prepared statement
will be inferior to the query plan that would have been chosen if the
statement had been submitted and executed normally. This is because when
the statement is planned and the planner attempts to determine the
optimal query plan, the actual values of any parameters specified in the
statement are unavailable. PostgreSQL collects statistics on the
distribution of data in the table, and can use constant values in a
statement to make guesses about the likely result of executing the
statement. Since this data is unavailable when planning prepared
statements with parameters, the chosen plan may be suboptimal."

Thanks again,
Yonatan Ben-Nes