RE: [sqlite] SQL syntax possibilities
> Original Message > Subject: Re: [sqlite] SQL syntax possibilities > From: Darren Duncan <[EMAIL PROTECTED]> > Date: Wed, November 16, 2005 10:41 pm > To: sqlite-users@sqlite.org > > At 1:34 AM -0700 11/16/05, [EMAIL PROTECTED] wrote: > >uSQLite does not (generally) enter into the details of the query it is > >passed. There is however an exception for security. uSQLite requires a > >login and (depending on the user and network) assigns the user a level: > > > >0: No access > >1: Select only > >2: Update/Insert only > >3: Select/Update/Insert only > >4: Power user > > Maybe your list wasn't complete, but I notice that 'Delete' is > conspicuously absent, yet it is necessary to do many common tasks. > Yes, you could reason that delete should be at level 3. I started of with Read only or read/write and added 2 because it is appropriate for remote automated data insertion, which is a prime application of the protocol. I did 3 and 4 to seperate nervous users form confident users:-) Perhaps it is just the way I define databases, but I find delete little use in normal operations, I keep everything :-) Having said that, I also kept things simple to mimimise the complications of parsing the SQL, but now I realise I can leave that to SQL by means of the authorise callback, I may let a few extra levels creep in or more likely do a bit field. Keeping remote access within necessary limits is just good sense. I agree with you about the damage causable by UPDATE however. I have long felt that SQL should make WHERE clauses obigatory. The UPDATE is particularly terrible as it is very easy to hit return prematurly after completing the values. IMHO, if you want to operate on all records then it should be necessary to put a WHERE ALL clause or something. Would a PRAGMA FORCE_WHERE option in SQLite be considered feature creep? Perhaps some notion allready exists, I am no expert on SQL syntax and standards.
Re: [sqlite] SQL syntax possibilities
At 1:34 AM -0700 11/16/05, [EMAIL PROTECTED] wrote: uSQLite does not (generally) enter into the details of the query it is passed. There is however an exception for security. uSQLite requires a login and (depending on the user and network) assigns the user a level: 0: No access 1: Select only 2: Update/Insert only 3: Select/Update/Insert only 4: Power user Maybe your list wasn't complete, but I notice that 'Delete' is conspicuously absent, yet it is necessary to do many common tasks. And before you say that it was left out from all but Power User because of its possible destructiveness, I would say that Update is just as destructive; an Update can blow away anything a Delete can, if you say "update ... set foo = 0" for all fields and rows. I suggest that levels like this make more sense from a security standpoint: 0: No access 1: Select only (read-only) 2: Insert only (non-destructive drop-box) 3: Select/Insert only (fully non-destructive read and write) 4: Select/Insert/Update/Delete only (full read-write data, no ddl) 5: Power User -- Darren Duncan
RE: [sqlite] SQL syntax possibilities
> Original Message > Subject: Re: [sqlite] SQL syntax possibilities > From: Eric Bohlman <[EMAIL PROTECTED]> > Date: Wed, November 16, 2005 10:54 am > To: sqlite-users@sqlite.org > > [EMAIL PROTECTED] wrote: > > uSQLite does not (generally) enter into the details of the query it is > > passed. There is however an exception for security. uSQLite requires a > > login and (depending on the user and network) assigns the user a level: > > > > 0: No access > > 1: Select only > > 2: Update/Insert only > > 3: Select/Update/Insert only > > 4: Power user > > > > These rules are enforced by looking at the first word in a query, the > > code is at the bottom of this post. My problem is that I allow multiple > > queries to be concatented, and this is a useful feature for bulk inserts > > and blocks of queries that must be executed atomicaly (Major hangup is > > level 2 for remote updates inserts). However, with the present routine > > I could do eg: > > I think you should look into using SQLite's authorizer callback > mechanism; that way you don't have to parse syntax. Yup. Will do. Thanks. (Repeat after me RTFM, RTFM,RTFM...)
Re: [sqlite] SQL syntax possibilities
[EMAIL PROTECTED] wrote: uSQLite does not (generally) enter into the details of the query it is passed. There is however an exception for security. uSQLite requires a login and (depending on the user and network) assigns the user a level: 0: No access 1: Select only 2: Update/Insert only 3: Select/Update/Insert only 4: Power user These rules are enforced by looking at the first word in a query, the code is at the bottom of this post. My problem is that I allow multiple queries to be concatented, and this is a useful feature for bulk inserts and blocks of queries that must be executed atomicaly (Major hangup is level 2 for remote updates inserts). However, with the present routine I could do eg: I think you should look into using SQLite's authorizer callback mechanism; that way you don't have to parse syntax.
[sqlite] SQL syntax possibilities
uSQLite does not (generally) enter into the details of the query it is
passed. There is however an exception for security. uSQLite requires a
login and (depending on the user and network) assigns the user a level:
0: No access
1: Select only
2: Update/Insert only
3: Select/Update/Insert only
4: Power user
These rules are enforced by looking at the first word in a query, the
code is at the bottom of this post. My problem is that I allow multiple
queries to be concatented, and this is a useful feature for bulk inserts
and blocks of queries that must be executed atomicaly (Major hangup is
level 2 for remote updates inserts). However, with the present routine
I could do eg:
Select 1;Drop table foo
With user level 1. AFAIK it would be sufficient that I modify the
ChekLevel routine such that it will check the first word and each word
that follows a semicolon (but I must also skip quoted semicolons). Is
this correct? Or can anybody think of any scenarios where this would
not work?
int ChekLevel(char *query,int uselevel){
char *cur,*dur;
cur=query;
// strip lead
while((*cur<' ')&&(*cur))cur++;
// strncasecmp is the same as strncmp except that
// it ignores case. It is a GNU extension to the
// Clib which many other libs have.
if(!strncasecmp(cur,"SELECT",6))
return (uselevel<1)||(uselevel==2)?0:1;
if(!strncasecmp(cur,"UPDATE",6))
return uselevel<2? 0:1;
if(!strncasecmp(cur,"INSERT",6))
return uselevel<2? 0:1;
return uselevel<4? 0:1;
}
