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 _______________________________________________ [email protected] mailing list UNSUBSCRIBE and account-management -> http://lists.puredata.info/listinfo/pd-list
