Hi all,

I'm using the nssqlite3 driver for my latest experiment and found a 
serious limitation in it: you cannot execute a select statement that 
returns no rows. (at least not with select or 0or1row; you could 
probably hack it with exec but that would be annoying and is supposed to 
  be unnecessary)

The problem is straightforward - the driver checks how many rows are 
returned after executing a statement; 0 returns NS_DML and >0 returns 
NS_ROWS.  'ns_db select' and '0or1row' expect NS_ROWS results, even if 
there are no rows to be read (which is a perfectly valid result).

So how to fix it?  There's no direct way to tell what kind of statement 
was parsed in the sqlite api (unlike say, OCI) and checking how many 
rows were changed will give bad results for an update affecting no rows. 
    The dumb thing to do would be to compare the start of the passed sql 
with "select" to determine if it is a select or dml statement.  But 
there may be a cleverer way!

sqlite allows you to set up an authorization callback that is called 
when parsing the sql.  Among other things this callback can allow or 
deny operations like select, insert, delete, drop, and so forth.  That 
makes it easy, just create an authorizer to allow select (and read) and 
deny everything else, and use that for select operations only, and for 
dml or exec leave the authorizer null to allow everything.  The nsdb 
driver allows for a different selectProc and execProc, so this should be 
straightforward.

Fortunately I read the code before implementing this, for had I not I 
would surely have slammed my head into the wall several times trying to 
figure out what was going wrong.  nsdb does allow you to register a 
different selectProc and execProc, and one would think that a select 
operation would use the more specific function if it was available, and 
fall back to the generic one if not.  One would be mistaken;  if an 
execProc is registered it will be used and the selectProc is only used 
when there is no execProc.  Not only is this backward IMHO, it thwarts a 
potentially useful protection against sql-injection attacks - if the 
select method really only let the database execute select statements 
then adding in a drop table or other nastiness would be ineffectual.

However, even if this protection wasn't thwarted at the nsdb level, the 
database-specific drivers that exist are no help.  As far as I can tell, 
even when a separate selectProc is provided is it just a wrapper around 
execProc, meaning they all execute whatever sql is passed in and only 
use the different initial call to check what is returned and raise an 
error if it is wrong.  I gather that these different interfaces were not 
intended as a security measure, but as a programmer convenience.

So after all this I guess I'll shortly be writing a patch for this 
driver which checks if the statement begins with "select" and forces it 
to return NS_ROWS for that case.  To whom should I send it?  (Or Dossy 
could grant me cvs access.)

And then there's nsdb - I think the more specific selectProc should be 
tried first for select operations, but since it's been this way for a 
while, would changing this break some other drivers (where the 
selectProc has never been called, or tested)?  The postgres driver is at 
least aware of this judging by a comment that the select function is 
never called by the server, but how would the other drivers fare?

-J