[PD] [psql] object hand-holding

[Hans-Christoph Steiner]

On Dec 10, 2007, at 1:42 PM, Jamie Bullock wrote:

>
> 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.

So PostgreSQL doesn't support placeholders in normal SQL queries?   
That's a bummer.  It seems that those languages that I sited chose  
the Oracle standard for placeholders (? and :name).  According to  
this, MySQL uses ?name and MS SQL Server uses @name.  MySQL can use  
@name if $old_syntax is turned on.  MySQL should use plain "?" but I  
don't know about MS SQL Server (who's really going to use that with  
Pd anyway ;)

http://forums.mysql.com/read.php?38,122041,122187#msg-122187

So the syntax seems to be less standard than I thought.  Man, SQL is  
a mess.

.hc

------------------------------------------------------------------------ 
----

"[W]e have invented the technology to eliminate scarcity, but we are  
deliberately throwing it away to benefit those who profit from  
scarcity."        -John Gilmore