Thread: binds only for s,u,i,d?

Why are only select, insert, update, and delete supported for $X binds?
Why can't preparation be used as a global anti-injection facility?

Example using the backend protocol for binds:
PREPARE TRANSACTION $1;
bind $1 ['text']
-->syntax error at $1

Why am I able to prepare statements with the backend protocol that I
can't prepare with PREPARE:
agentm=# prepare gonk as prepare transaction $1;
ERROR:  syntax error at or near "prepare" at character 17
LINE 1: prepare gonk as prepare transaction $1;

whereas the backend protocol only emits an error when the statement is
executed [and the binds are ignored].

-M


¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
AgentM
agentm@themactionfaction.com
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬

On Mon, 2006-07-03 at 23:28 -0400, Agent M wrote:
> Why are only select, insert, update, and delete supported for $X binds?

This is a property of the way prepared statements are implemented.
Prepared statement parameters can be used in the place of expressions in
optimizeable statements (the actual parameter substitution is done by
the executor). Hence you can only have parameters in places where you
can have expressions.

> Why can't preparation be used as a global anti-injection facility?

Well, you can't reasonably allow parameters to appear just anywhere in a
statement, if you want to have a hope of parsing the statement: consider
"PREPARE foo AS $1; EXECUTE foo("SELECT 1");", for example.

It would be somewhat more reasonable to allow parameters to be used in
the place of identifiers, but even then, you wouldn't be able to do very
much meaningful analysis or optimization when the statement was prepared
(for example, adding new relations to a SELECT query at EXECUTE-time
could change the semantics of the query). All that work would need to be
deferred to EXECUTE-time, which would largely defeat the purpose of
server-side prepared statements, no?

-Neil

Neil Conway <neilc@samurai.com> writes:

> On Mon, 2006-07-03 at 23:28 -0400, Agent M wrote:
> 
> > Why can't preparation be used as a global anti-injection facility?
> 
> All that work would need to be deferred to EXECUTE-time, which would largely
> defeat the purpose of server-side prepared statements, no?

It would also defeat the anti-injection purpose. If you can use parameters to
change the semantics of the query then you're not really protected any more.
The whole security advantage of using parameters comes from knowing exactly
what a query will do with the data you provide.

-- 
greg

Greg Stark wrote:

>Neil Conway <neilc@samurai.com> writes:
>
>  
>
>>On Mon, 2006-07-03 at 23:28 -0400, Agent M wrote:
>>
>>    
>>
>>>Why can't preparation be used as a global anti-injection facility?
>>>      
>>>
>>All that work would need to be deferred to EXECUTE-time, which would largely
>>defeat the purpose of server-side prepared statements, no?
>>    
>>
>
>It would also defeat the anti-injection purpose. If you can use parameters to
>change the semantics of the query then you're not really protected any more.
>The whole security advantage of using parameters comes from knowing exactly
>what a query will do with the data you provide.
>
>  
>
Exactly. In particular, the suspect data should never hit the parser. 
You can defeat that with a function call, of course, but you have to 
work at it.

cheers

andrew

On Wed, 2006-07-05 at 06:55 -0400, Agent M wrote:
> Like you said, it would make sense to have binds anywhere where there 
> are quoted strings- if only for anti-injection. There could be a "flat" 
> plan which simply did the string substitution with the proper escaping 
> at execute time.

I don't see the point of implementing this in the backend. Perhaps what
you're really asking for is basically PQescapeIdentifier()?

> Escaping vulnerabilities would then be taken care of by server updates.

Escaping vulnerabilities are hardly the common case; in any case,
implementing this in libpq would allow a similar upgrade path.

-Neil