[PD] [psql] object hand-holding

[Jamie Bullock]

On Mon, 2007-12-10 at 12:18 -0500, Mathieu Bouchard wrote:

> 
> This almost *never* happens. As long as what you do in Pd is regular 
> read-write activities involving tables that contain data that you only 
> want to see a small part of at a time, you need variable atoms in almost 
> any query:
> 
>    select person from attendance where chatroom=?
>    select chatroom from attendance where person=?
>    select * from users where user=?
>    select * from chatrooms where chatroom=?
> 
> apps more likely to have invariable queries are apps that started small, 
> stayed small, and will never be expanded.

I take your point, but I think you are exaggerating slightly. I just
looked at the code for a database driven web app I worked on recently.
It uses 30 queries, 3 of which have no variables. Out of these three
only one doesn't 'overlap' with other queries and therefore can't be
refactored. Maybe 1 in 30 is almost never? Anyhow - I agree that I
probably can't argue the case for the [psql]-style of operation on the
grounds of reduced object count :-|

> > True, this is a good argument for the [expr]-style SQL object. Although
> > there may be other ways to provide some protection against injection
> > like allowing the user to lock the number of statements in the query.
> 
> checking the number of statements is not enough: what about a value like: 
> (including quotes)
> 
>    'or'1
> 
> put inside this statement:
> 
>    delete from users where username='$1'
> 
> this query will delete the complete table.

Good point! I think Hans' recent suggestion addresses the problem. It
also occurs to me that for Postgres at least, we have the PREPARE
statement, which addresses the optimisation and injection issues you
have raised. Technically [psql] already supports PREPARE except that
PREPARE uses the '$' character as its placeholder identifier, and '$1'
can't be passed around as a symbol in Pd. I think it might be
interesting to use the '?' notation currently under discussion as an
interface to PREPARE though.

Jamie

-- 
www.postlude.co.uk