[PD] [psql] object hand-holding

[Hans-Christoph Steiner]

On Dec 10, 2007, at 4:21 AM, Jamie Bullock wrote:

>
> On Sun, 2007-12-09 at 21:47 -0500, Mathieu Bouchard wrote:
>> On Sun, 9 Dec 2007, Jamie Bullock wrote:
>>
>>> Then I persuaded him that passing the queries as a list to the inlet
>>> would be more flexible. It also greatly reduces the number of  
>>> objects
>>> required to send a query, if you have more than one query.
>>
>> I don't understand the latter part. How does it work? I'm talking  
>> about
>> putting any number of queries together in a single object and  
>> passing the
>> arguments of those queries all together in a list. How can you  
>> reduce the
>> number of objects more than that?
>
> The way you are suggesting always requires at least 2 objects per  
> query:
> an object to build the query and a message to send it. So if you  
> have 5
> different queries (I mean with different statements not just different
> data), then you would need at least 10 objects. This would be the case
> even if there was no variable data in the queries. Using the [psql]  
> way
> of doing things, provided that the queries have no variable atoms,  
> only
> 6 objects would be required, one for the database connection, and 5
> containing the queries, which when passed to the connection object  
> also
> trigger the sending.

>> Your way takes at least two objects
>> instead of one and it does not provide any protection against SQL
>> injection because it can't distinguish between a symbol passed as  
>> a SQL
>> argument and a symbol representing part of the statement syntax  
>> itself.
>
> 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.

For a place where you are expecting a number, you can protect against  
a SQL injection attack by merely putting a [float] before the message  
box with the SQL in it.  In other situations, I think that Perl has a  
pretty decent idea: a "SQL quote" function.  This could easily be a  
Pd object, [sqlquote], which would work with all of the SQL objects.  
This would probably be the easiest to implement.

SQL Placeholders seem like a good idea, and SQL already has a  
defined, documented, and  supported syntax for SQL placeholders.   
Ruby, Perl, Java, PHP and others use it, and I think we should use it  
too. Since we already have an inlet that is designed to accept only  
SQL.  There seems to be two defined as part of SQL, "?" for generic,  
and ":name" for named placeholders.

- the "?" could be supported by sending lists to the hot inlet. So if  
there were three "?" in the SQL statement, then you'd send a [5 Joe  
1239.2( to the hot inlet.

- the names ones could be supported as selectors to the hot inlet:

[insert into table (name,age) values (:name,:age) (     <--- cold inlet
[name Lila(                                             <--- hot inlet
[age 12(                                                <--- hot inlet

Then the SQL quoting would be handled internally to the Pd object.  I  
added examples of this to the interface sketch, it's attached.  This  
part could be safely implemented later, I think.

-------------- next part --------------
A non-text attachment was scrubbed...
Name: sql-interface.pd
Type: application/octet-stream
Size: 7072 bytes
Desc: not available
URL: <http://lists.puredata.info/pipermail/pd-list/attachments/20071210/acbeda65/attachment.obj>
-------------- next part --------------


This way, the object represents the database itself, the object's  
arguments can represent the connection to the database, then  
different queries are represented using messages sent to the database  
object.  That seems to mirror conceptually what's actually happening.

Lastly, I know that this is getting more complicated to implement,  
but I think it'll pay off in the end.  I'll happily help out with the  
implementation if either of you want me too.  Also, once this is  
ironed out, I'd like to port the Max/MSP [mysql] object to whatever  
interface we come up with.

.hc


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

Computer science is no more related to the computer than astronomy is  
related to the telescope.      -Edsger Dykstra