On Dec 13, 2007, at 3:50 PM, Mike McGonagle wrote:

On Dec 13, 2007 1:59 PM, Mathieu Bouchard <matju@artengine.ca> wrote:

On Mon, 10 Dec 2007, Hans-Christoph Steiner wrote:

> The other somewhat common style that I saw in my searches was printf patterns
> (%s, %f, etc). In Pd, [makefilename], [makesymbol], [sprintf], and perhaps
> others use this syntax. The single ? notation seems to be supported by at
> least these, if you want to call that "specific": Qt, PerlDBI, Perl's
> DBD::Pg, RubyDBI, PHP PDO, Java JDBC, MySQL, Oracle.

Well, maybe I shouldn't have said "specific", but when I look at any PHP
code that I find, it seems that they haven't discovered what's a
placeholder yet, for example. So, it seems that it's not so universal.

As someone who has never really used Placeholders, the only sorts of things that I can see them being useful for are when you need to do a lot of inserts or deletes, or for other statements that will be executed repeatedly. From what I am gathering by these discussions is that the useage of placeholders allows the SQL statement to be "compiled" and then with each execution of the statement, the values of the placeholders are substituted.

This might be one reason you don't see them all that often in PHP, I would imagine that PHP doesn't really do a whole bunch of repetitive stuff.

> I think it is quite important to reuse existing syntax rather than
> introducing new syntax. Minimal syntax is really one of Pd's biggest
> strengths. Since these lines would be pure SQL, I think it would be
> appropriate to use a common SQL syntax.

If you wanted to reuse existing Pd syntax, you could abstract out SQL
syntax completely and make a database interface that fully feels like Pd.
The Rails web framework has something like that.

I don't know about you guys, but my original goal on this was to basically allow a user to input SQL and it would return the result sets. I just wanted to keep it simple. But I can see a use for using Placeholders, especially when you have a lot of data to store (and it also kind of "vindicates" my original idea of putting the SQL directly in the creation args... [wink, wink, nudge...]).

That's the idea of the [sql_query] object, so we can have both possibilities, and also support Matju's idea for a expr syntax for placeholders.

This idea of doing this to make this more PD-like I think would be a waste of time, as SQL is pretty simple and a LOT of people already know it. Why create another "language"?

I think there is room for both ideas. I like your idea for [sqlite], and if someone else wants to make a Pd database syntax, they can do that also. I don't think there is any conflict in having both.

> I just had a thought, SQL injection relies on being able to send semi-colons
> in text fields.

This is not true. I have already posted an example in this thread on how
to delete a whole table using SQL injection without a semicolon.

At the same time, should our external be on the look out for these sorts of things? One of the original ideas was to not give the external any, if at all, knowledge of SQL. Meaning, it wouldn't "parse" the SQL, nor would it try to do any generation of SQL. It just expects that the user is HONEST (that is what these concerns over Injection are, right), and the SQL they entered is what they meant.

These things being said, I am not adverse to the new design model, and I hope to get something up over the weekend.

While we can try to protect against various things, those that want to be malicious will do so anyway.

Of course, we could eliminate these problems altogether, and just use an embedded database ONLY... (just kidding...)