Allow '?' bind parameters for more things in SQL statements

[GoogleCodeExporter]

In a past life, there were a couple useful idioms I added to a
database-access layer to help guard against injection attacks.

---

Bind parameters cannot be used to specify structural things.  You can't say:

  var tableName = "MyTable";
  var rs = db.execute("SELECT rowid FROM ? WHERE a = b", [tableName]);

This isn't a super-common problem to need solved, but it _does_ come up
frequently enough that it might be worth solving.  The system I was
building allowed a printf-style replacement "%N", mnemonic "name".  You
could use it wherever you needed a table or column name, and it arranged
for appropriate quoting so that whatever you passed in couldn't turn into
an injection attack.

---

In general, conditional clauses for WHERE statements are the bane of
database access libraries.  You _should_ write:

  var rs;
  if (y) {
    rs = db.execute("SELECT rowid FROM MyTable WHERE a = ?", [x])
  } else {
    rs = db.execute("SELECT rowid FROM MyTable WHERE a = ? AND b = ?", [x, y]);
  }

Unfortunately, the temptation is strong to write:

  var crap = "";
  var p = [x];
  if (!y) {
    crap = " AND b = ?";
    p[1] = y;
  }
  var rs = db.execute("SELECT rowid FROM MyTable WHERE a = ?"+crap, p);

Indeed, for suitably complex dynamic queries, it can become
combinatorically problematic to code things the first way.  Unfortunately,
this is a potential injection trap, again, because the developer may decide
to forgo the parallel construction of the query and bind parameter array. 
The solution is to provide some explicit way to accumulate a query and its
bind parameters such that there is no room for an injection attack.

---

OR and IN are a common area where you end up constructing queries. 
Usually, it's something where you have a set of values, and you want to
delete or update all of the rows where those values match.  So you end up
with something like:

   db.execute("DELETE FROM MyTable WHERE rowid IN ("+rowids.join(",")+")");

A thing I surfaced in a Perl API modified that to look like:

  db.execute("DELETE FROM MyTable WHERE rowid IN (?@)", [rowids]);

In the above, rowids is an array, and ?@ (or maybe I used ??) is a special
bind notation which knows how to construct the contents of the IN from the
array.  Making up some code, it would be as if you could say:

  db.execute("DELETE FROM MyTable WHERE rowid IN ("+mapcar(db.quote,
rowids).join(",")+")");

Read that as "Apply the function db.quote to each element of rowids,
creating an array of quoted rowids, then join that with commas."  It makes
more sense if rowids is a bunch of strings, of course.  The nice thing
about pushing this into the API is that it makes the quoting automagic.

Original issue reported on code.google.com by gears.te...@gmail.com on 11 Jun 2007 at 5:05

[GoogleCodeExporter]

See also http://code.google.com/p/google-gears/issues/detail?id=50 about 
exposing
sqlite3_quote().

Original comment by Scott.Hess on 11 Jun 2007 at 5:53

[GoogleCodeExporter]

Original comment by gears.te...@gmail.com on 28 Jun 2007 at 11:30

• Changed title: Add support for SQL injection prevention idioms.
• Changed state: New
• Added labels: Milestone-M3
• Removed labels: Priority-Low

[GoogleCodeExporter]

Original comment by gears.te...@gmail.com on 5 Sep 2007 at 12:23

• Changed title: Allow '?' bind parameters for more things in SQL statements

[GoogleCodeExporter]

Except that '?' bind parameters have specific meaning relating to context, and 
this
feature would have a different meaning entirely.  So it shouldn't be using the 
same
'?' bind parameter notation.

Original comment by Scott.Hess on 5 Sep 2007 at 2:37

[GoogleCodeExporter]

Original comment by othman@gmail.com on 5 Sep 2007 at 7:48

• Added labels: Version-later
• Removed labels: Milestone-M3

[GoogleCodeExporter]

Original comment by gears.te...@gmail.com on 21 Jan 2008 at 5:47

• Changed state: Accepted