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