Re: [Chicken-users] sql.egg: incompatible change

[Hans Bulfone]

hi,

On Sun, Aug 19, 2007 at 02:38:09PM -0700, Matthew Welland wrote:

> Just a thought: would this work?
> 
> (sql:select what from where #!optional optional-modifiers) 
> 
> Where optional-modifiers is a list of modifiers such as:
> 
> ((limit 1)(order foo desc))

this is not much different from and imho would look more contrived than
using keyword arguments.

> > I have the opposite - please switch to keyword arguments.
> 
> What would this look like? I'm concerned that nested queries would look a 
> bit contrived with keyword arguments. Also, select (and most other sql 
> commands) seem too regular to necesitate keywords. I think the sql egg 
> approach would lose elegance with keywords.

imho supporting all parts of a select statement without using keyword
arguments would make sql:select expressions harder to read because
it wouldn't be obvious which subexpression serves what purpose.

> 1. Positional arguments
>  ;; Know SQL fairly well? Then this is easy to read and understand (IMHO)
> 
>  (sql:exec db "SELECT id,name FROM people WHERE name=?" who)

even with positional arguments available the sql egg can still be very useful
to construct queries that consist of multiple optional conditions.

> By the by I suspect that option 2. doesn't protect against SQL injection 
> threats to the same degree that option 1. does.

imho the sql egg should protect against sql injection as long as you don't
pass untrusted data as symbols.  everything you pass as a string will be
escaped properly.

> Also, I believe that there 
> are optimizations that can be done with postional arguments for when a 
> query is called multiple times with different arguments. Can the sql egg 
> approach ever take advantage of those type of optimizations?

not by itself, this must be supported by the database api.
if supported, you can of course use this facility together with the sql.egg:

(let ((stmt ((db:prepare (sql:select '(id name) from: '(people) where: '(= name 
?))))))
  (db:exec stmt "foo")
  (db:exec stmt "bar")
  ...)

bye,
hans.