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
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]>
with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject:
field of your email blank.
