[whatwg] Couple comments on Database storage spec.

[Scott Hess]

On 10/17/07, Ian Hickson <ian at hixie.ch> wrote:
> On Fri, 5 Oct 2007, Scott Hess wrote:
> > It may be worthwhile for Database to export a quote(arg) function, which
> > will quote the argument in the appropriate manner for use in
> > constructing a statement.  This is useful for cases where it is
> > challenging to reduce something to a static SQL statement with bind
> > parameters.  [A common case for this is something like "SELECT rowid
> > FROM t WHERE c IN (...)", and you want to replace ... with an
> > appropriately quoted comma-separated array.]
>
>    var q = "";
>    for each (var i in array)
>      q += (q == "" ? "" : ", ") + "?";
>    executeSql('SELECT rowid FROM t WHERE c IN (' + q + ')', array, ...);

Honestly, something like quote() is not necessary.  It's just that
constructing SQL statements via concatenation is a hole I often see
people falling into.  Having quote() allows you to construct safer SQL
statements, but people who construct statements directly rather than
constructing a parallel statement and arg array may be beyond saving.

Other cases I've seen where quote() could be used are dynamic
construction of WHERE clauses, something like:

   var sql = "SELECT rowid FROM t WHERE name = ?"
   if (color) {
     sql += " AND color = " + color;
   }
   ...

and comparable constructs for INSERT and UPDATE.  As mentioned, these
can generally be handled by constructing the statement with bind
parameters in parallel to the arg array.  Depending on control flow,
this can be pretty annoying, but my experience with this is mostly in
C/C++, where it's much more annoying to change the types of things.

Thanks,
scott