sqlite on SunOS 5.6 (Solaris Sparc 6)

classic Classic list List threaded Threaded
21 messages Options
12
Reply | Threaded
Open this post in threaded view
|

sqlite on SunOS 5.6 (Solaris Sparc 6)

rpyne
Has anyone sucessfully built sqlite 3.2.7 on a SunOS 5.6 (Solaris
sparc 6) box?

I had no problems with sqlite 2, but can't seem to get sqlite3 to
build.

Using the ./configure ; make route, it dies with:

./lemon -DSQLITE_OMIT_CURSOR           parse.y
cat parse.h ./src/vdbe.c | awk -f ./mkopcodeh.awk >opcodes.h
awk: syntax error near line 36
awk: illegal statement near line 36
awk: syntax error near line 37
awk: illegal statement near line 37
awk: syntax error near line 42
awk: illegal statement near line 42
awk: syntax error near line 103
awk: illegal statement near line 103
make: *** [opcodes.h] Error 2


Trying to use the generic Makefile, I get the same results.

Thanks,

--Richard

Reply | Threaded
Open this post in threaded view
|

Documentation is slightly out-of-date

Arjen Markus
Hello,

I noticed that the documentation on the www.sqlite.org site
is slightly out-of-date: The list of #defines does not
include SQLITE_ROW and a couple of others.

Regards,

Arjen

Reply | Threaded
Open this post in threaded view
|

FYI: Fortran interface to SQLite

Arjen Markus
In reply to this post by rpyne
Hello,

my (almost) first experience with SQLite was the sample
code that Al Danial published to show how to use SQLite
in a FORTRAN program.

I was inspired by his work to develop a more comprehensive
interface, this time in Fortran 90/95. I am pleased to
say that this was fairly easy to do and that it is
nearing a stage where I dare release it in the wild.

The idea:
- Provide a library that hides as much as possible
  the detail of Fortran-C interfacing
- Provide low-level routines for flexibility and
  some high-level routines for common tasks and
  ease of use.

It has been tested on Windows with Compaq Visual Fortran
and MS VC/C++ only so far, but it should not be
a problem to get to work with other common
compiler combinations.

Anyone who is interested, just drop a note.

Regards,

Arjen

Reply | Threaded
Open this post in threaded view
|

Re: sqlite on SunOS 5.6 (Solaris Sparc 6)

Arjen Markus
In reply to this post by rpyne
[hidden email] wrote:

>
> Has anyone sucessfully built sqlite 3.2.7 on a SunOS 5.6 (Solaris
> sparc 6) box?
>
> I had no problems with sqlite 2, but can't seem to get sqlite3 to
> build.
>
> Using the ./configure ; make route, it dies with:
>
> ./lemon -DSQLITE_OMIT_CURSOR           parse.y
> cat parse.h ./src/vdbe.c | awk -f ./mkopcodeh.awk >opcodes.h
> awk: syntax error near line 36
> awk: illegal statement near line 36
> awk: syntax error near line 37
> awk: illegal statement near line 37
> awk: syntax error near line 42
> awk: illegal statement near line 42
> awk: syntax error near line 103
> awk: illegal statement near line 103
> make: *** [opcodes.h] Error 2
>
> Trying to use the generic Makefile, I get the same results.
>

I get the very same result, apparently awk on Sun does not
like the mkopcodeh.awk script.

It does not like the sub command and it does not like:

      nopush[i] = nopush[i] + (2^j)


I tried with "nawk" instead: this works without any
problem!

Regards,

Arjen

Reply | Threaded
Open this post in threaded view
|

Re: sqlite on SunOS 5.6 (Solaris Sparc 6)

Kurt Welgehausen
In reply to this post by rpyne
This looks like a problem that has come up several
times before on the list. If I remember correctly,
it's usually that the awk shipped by Sun doesn't
support the extended syntax that Sqlite expects.

Try installing gawk if it's not there already, and
do whatever is required to use it in place of awk.

Regards
Reply | Threaded
Open this post in threaded view
|

Re: sqlite on SunOS 5.6 (Solaris Sparc 6)

Christian Smith
In reply to this post by rpyne
Inline.

On Tue, 25 Oct 2005 [hidden email] wrote:

>Has anyone sucessfully built sqlite 3.2.7 on a SunOS 5.6 (Solaris
>sparc 6) box?
>
>I had no problems with sqlite 2, but can't seem to get sqlite3 to
>build.
>
>Using the ./configure ; make route, it dies with:
>
>./lemon -DSQLITE_OMIT_CURSOR           parse.y
>cat parse.h ./src/vdbe.c | awk -f ./mkopcodeh.awk >opcodes.h
> ...
>make: *** [opcodes.h] Error 2
>


Put /usr/xpg4/bin in your PATH before /usr/bin. On Solaris, the tools in
/usr/bin are from the ark, and those in /usr/xpg4/bin are the more recent
standards compliant tools. In this case, you need a nawk compatible awk.

Christian


--
    /"\
    \ /    ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
     X                           - AGAINST MS ATTACHMENTS
    / \
Reply | Threaded
Open this post in threaded view
|

Re: FYI: Fortran interface to SQLite

Hamid Benhocine
In reply to this post by Arjen Markus
Hello,
Very interesting, I planned to do some work (not done yet) to
provide my division with some examples using sqlite with fortran 90/95
(to replace a home pivot format in  numerical computing : we use f90/95).
So, if you have a release, you want to share, I will be happy to test it
on our Unix platforms(IBM , SGI, Linux) with the Fortran 90/95 compilers
- IBM XL Fortan Compiler ( 64/32 bits Mode),
- SGI Mips Pro F90/95 Compiler (64/32 bits).,
- PGF f90/95 (Portland Group)

Regards.

Hamid

Arjen Markus wrote:

>Hello,
>
>my (almost) first experience with SQLite was the sample
>code that Al Danial published to show how to use SQLite
>in a FORTRAN program.
>
>I was inspired by his work to develop a more comprehensive
>interface, this time in Fortran 90/95. I am pleased to
>say that this was fairly easy to do and that it is
>nearing a stage where I dare release it in the wild.
>
>The idea:
>- Provide a library that hides as much as possible
>  the detail of Fortran-C interfacing
>- Provide low-level routines for flexibility and
>  some high-level routines for common tasks and
>  ease of use.
>
>It has been tested on Windows with Compaq Visual Fortran
>and MS VC/C++ only so far, but it should not be
>a problem to get to work with other common
>compiler combinations.
>
>Anyone who is interested, just drop a note.
>
>Regards,
>
>Arjen
>
>
>  
>

Reply | Threaded
Open this post in threaded view
|

Re: sqlite on SunOS 5.6 (Solaris Sparc 6)

rpyne
In reply to this post by Kurt Welgehausen
Thanks, changing my path order and replacing awk with gawk solved the
(first) problem. Now I am getting:

gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./src -DNDEBUG -
DTHREADSAFE=0 -DSQLITE_OMIT_CURSOR -DHAVE_READLINE=1 -
I/usr/local/include/readline -o .libs/sqlite3 ./src/shell.c  
./.libs/libsqlite3.so -lreadline -lcurses -Wl,--rpath -
Wl,/usr/local/lib
./.libs/libsqlite3.so: undefined reference to `fdatasync'
collect2: ld returned 1 exit status
make: *** [sqlite3] Error 1


Can anyone guide me to where "fdatasync" should be?

Thanks.

--Richard



On 25 Oct 2005 at 7:06, Kurt Welgehausen wrote:

> This looks like a problem that has come up several
> times before on the list. If I remember correctly,
> it's usually that the awk shipped by Sun doesn't
> support the extended syntax that Sqlite expects.
>
> Try installing gawk if it's not there already, and
> do whatever is required to use it in place of awk.
>
> Regards
>


Reply | Threaded
Open this post in threaded view
|

Re: sqlite on SunOS 5.6 (Solaris Sparc 6)

Kurt Welgehausen
> Can anyone guide me to where "fdatasync" should be?

<www.mail-archive.com/[hidden email]/msg10336.html>

(maybe)


Regards
Reply | Threaded
Open this post in threaded view
|

Re: FYI: Fortran interface to SQLite

Arjen Markus
In reply to this post by Hamid Benhocine
Hamid Benhocine wrote:

>
> Hello,
> Very interesting, I planned to do some work (not done yet) to
> provide my division with some examples using sqlite with fortran 90/95
> (to replace a home pivot format in  numerical computing : we use f90/95).
> So, if you have a release, you want to share, I will be happy to test it
> on our Unix platforms(IBM , SGI, Linux) with the Fortran 90/95 compilers
> - IBM XL Fortan Compiler ( 64/32 bits Mode),
> - SGI Mips Pro F90/95 Compiler (64/32 bits).,
> - PGF f90/95 (Portland Group)
>
>

Hello Hamid,

that sounds great. Right now I have solved most obvious bugs,
which of course leaves the stupid ones and the not-so-obvious ones.

One big issue is the lack of proper documentation ;).
I will send you what I have right now via a private mail.

Regards,

Arjen

Reply | Threaded
Open this post in threaded view
|

Re: FYI: Fortran interface to SQLite

Ulrich Telle
In reply to this post by Arjen Markus
Hi Arjen,

I would like to test your Fortran SQLite interface with the Lahey LF95
Windows Fortran compiler. Please let me know where I can get your code.

Regards,

Ulrich Telle
Reply | Threaded
Open this post in threaded view
|

Re: Request for comment: Proposed SQLite API changes

Ulrich Telle
> > Suppose this where to change in version 3.3.0 so that the
> > actual error code was returned by sqlite3_step().

That would be a good thing, since it would allow appropriate reaction at the
right time and the right place.

> > Then when a schema change occurred, the statement was automatically
> > recompiled and rebound.  There would no more SQLITE_SCHEMA errors.
>
> This change should be done.  SQLITE_SCHEMA is all about
> an internal implementation detail in SQLite and shouldn't
> really be exposed to the users of SQLite.  There is only
> action that people take on getting it - rerun the query.
> Pretty much every wrapper does that anyway so it makes
> even more sense to make that the standard code in SQLite.

Sure, it would be nice if SQLITE_SCHEMA errors could be handled internally
by SQLite, but I think it's a non-trivial task to handle this kind of error.

When I created my SQLite C++ wrapper wxSQLite3 I thought about handling
SCHEMA errors, but decided against it due to the complexities involved. For
example which reaction is appropriate depends on the kind of the SQL
statement:

For INSERT, UPDATE or DELETE a simple retry might be a good choice. But how
often should the retry take place? The SQLite FAQ code example contains an
endless loop! I didn't want to introduce such kind of code into wxSQLite3. -
Additionally the number of columns in a table used in these statements might
have changed, that is the statement would probably fail again.

In case of a SELECT statement the situation is still more complex. The
SCHEMA error could happen after reading several result rows. If you would
then redo the query automatically it would start from scratch delivering the
already read rows again. If your application code gathers the result set in
an array for example you would get duplicate rows.

When retrying a query another problem arises if the SQL statement contains
bind variables. You would have to rebind the variables. To handle this
automatically would induce a lot of extra house keeping, wouldn't it?

If SQLite would be able to handle all these issues transparently, it would
be ok for me.

> If you are looking at API changes, the most beneficial to
> me would be a unification of sqlite3_value_TYPE and
> sqlite3_column_TYPE.  I have to write identical code to
> do my own type conversion when calling these function
> and duplicate it.  Similar story with sqlite3_result_TYPE
> and sqlite3_bind_TYPE.

Since C++ variables are typed my wrapper wxSQLite3 needs no type conversion.
It's the user's responsibility to use variables of the right type.

There is only one place where I would appreciate to have type information at
hand. sqlite3_get_table returns all values as character strings and there is
no way to find out the original value types.

Regards,

Ulrich Telle

--
Ulrich Telle
E-Mail privat: mailto:[hidden email]
E-Mail Studium: mailto:[hidden email]
Homepage: http://www.stud.fernuni-hagen.de/q1471341
Fax: +49-(0)89-1488-203070
Reply | Threaded
Open this post in threaded view
|

Re: Re: Request for comment: Proposed SQLite API changes

Paolo Vernazza
Ulrich Telle wrote:

>In case of a SELECT statement the situation is still more complex. The
>SCHEMA error could happen after reading several result rows. If you would
>then redo the query automatically it would start from scratch delivering the
>already read rows again. If your application code gathers the result set in
>an array for example you would get duplicate rows.
>
I think that a DB schema change can't happend while a transaction
(explicit or implicit) is being executed.
So it's no possible that a "select * from table" fails for a shema error
while you didn't closed the query..

Paolo
Reply | Threaded
Open this post in threaded view
|

Re: Request for comment: Proposed SQLite API changes

Igor Tandetnik
In reply to this post by Ulrich Telle
Ulrich Telle <[hidden email]> wrote:

> Sure, it would be nice if SQLITE_SCHEMA errors could be handled
> internally by SQLite, but I think it's a non-trivial task to handle
> this kind of error.
>
> For INSERT, UPDATE or DELETE a simple retry might be a good choice.
> But how often should the retry take place? The SQLite FAQ code
> example contains an endless loop! I didn't want to introduce such
> kind of code into wxSQLite3. - Additionally the number of columns in
> a table used in these statements might have changed, that is the
> statement would probably fail again.

Only if the schema change affects the table you are manipulating. Even
if this is the case, the statement might indeed fail again - but with a
_different_ error (probably a syntax error), at which point you drop out
of the loop and handle this error as you normally would. The only way
for you to stay in that "endless" loop for a long time is if some other
thread keeps changing the schema on you, in which case your application
has worse problems to worry about.

> In case of a SELECT statement the situation is still more complex. The
> SCHEMA error could happen after reading several result rows.

No it can't. A schema error can only occur between sqlite3_prepare or
sqlite3_reset, and the first sqlite3_step.

> When retrying a query another problem arises if the SQL statement
> contains bind variables. You would have to rebind the variables. To
> handle this automatically would induce a lot of extra house keeping,
> wouldn't it?

SQLite maintains sufficient information already. See
sqlite3_transfer_bindings - it is intended specifically to allow a
wrapper to transparently recover from SQLITE_SCHEMA error.

Igor Tandetnik

Reply | Threaded
Open this post in threaded view
|

Re: Request for comment: Proposed SQLite API changes

D. Richard Hipp
In reply to this post by Ulrich Telle
"Ulrich Telle" <[hidden email]> wrote:
>
> Sure, it would be nice if SQLITE_SCHEMA errors could be handled internally
> by SQLite, but I think it's a non-trivial task to handle this kind of error.
>
> When I created my SQLite C++ wrapper wxSQLite3 I thought about handling
> SCHEMA errors, but decided against it due to the complexities involved.

Isn't that really the whole point of a wrapper - to deal with complexities
so that the user doesn't have to.  If you are passing all of the
complexities up to the user, why use you wrapper at all?  Just call
SQLite directly.

>
> For INSERT, UPDATE or DELETE a simple retry might be a good choice. But how
> often should the retry take place? The SQLite FAQ code example contains an
> endless loop!

Not.  OK, I guess in theory, if another process were updating the
schema at just the right rate so that the schema was different every
times you retried, you could get an infinite loop.  But in practice,
the loop never runs more than twice - 3 times in the extreme.

> Additionally the number of columns in a table used in these statements might
> have changed, that is the statement would probably fail again.

If the number of columns changes, you will get a syntax error, not
a schema error.  At that point you exit the loop.

>
> In case of a SELECT statement the situation is still more complex. The
> SCHEMA error could happen after reading several result rows.

No.  SCHEMA errors happen prior to reading any data.

>
> When retrying a query another problem arises if the SQL statement contains
> bind variables. You would have to rebind the variables. To handle this
> automatically would induce a lot of extra house keeping, wouldn't it?

See the sqlite3_transfer_bindings() API.

------------------------ A Digression --------------------------

To all writers of wrappers, my I please call your attention to
the TCL wrapper for SQLite.  In that wrapper I have attempted
to hide as many details of the interface as possible from the user and
to make everything automatic.  This allows the user to focus on their
application and not worry so much about the details of the interface
to the database.  I and everybody else I have talked to find this to
be a very refreshing approach.

In the TCL wrapper, you run SQL statements using this template:

     DBOBJECT eval SQL-STATEMENT CODE-TO-HANDLE-EACH-RESULT-ROW

The DBOBJECT is the object that is created when you open the database.
On this object you call the "eval" method with two arguments, the
text of the SQL you want to process and a lambda procedure that runs
once for each row of output.  (The lambda is optional and is usually
omitted for non-query statements.)  The bindings automatically process
named parameters within the SQL statement by binding them with the
value of TCL variables of the same name.  So for example, if you
say:

    db eval {UPDATE table1 SET x=$var1 WHERE y=$rownum}

The $var1 and $rownum named parameters are bound to the values of
the var1 and rownum variables in TCL.  This is all automatic.

Within the lambda procedure that runs once for each row of the result,
local variables are created to contain the value of each column -
the names of the local variables are chosen to match the column names.
That way the user does not have to remember any method calls or
other such complication to get at the results.

Note the complete absence of prepared statements.  Prepared statements
are handled automatically by the wrapper.  The wrapper keep a cache
of recently used SQL statements and reuses them if the reappear.
Statements are cleared from the cache using the LRU algorithm.  There
are methods on the database object to changes the size of the prepared
cache or flush the cache.  But those methods never get used in practice
because the cache just seems to work without any problems.

Note also that the user never needs to worry about how to bind
values.  Bindings all happens automatically and transparently.

SQLITE_SCHEMA errors are also handled automatically.

The end results it that the user of the TCL binds does not need to
remember much about how the wrapper work.  If they can just remember
how to create a database object (hint: use the sqlite3 constructor)
and how to run the eval method, then they have access to the full
power of the SQLite interface without having to know or remember
ny of the details.

There is an important principle at work here:  keep it simple.
An interface with less complication, with fewer choices and options,
with less to remember and think about, is a better interface.  I
want to encourage all authors of wrappers and bindings for SQLite
and for other libraries to keep this principle in mind.

--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Request for comment: Proposed SQLite API changes

Ulrich Telle
In reply to this post by Ulrich Telle
D. Richard Hipp wrote:

> "Ulrich Telle" wrote:
>> Sure, it would be nice if SQLITE_SCHEMA errors could be handled
>> internally by SQLite, but I think it's a non-trivial task to handle
>> this kind of error.
>>
>> When I created my SQLite C++ wrapper wxSQLite3 I thought about handling
>> SCHEMA errors, but decided against it due to the complexities involved.
>
> Isn't that really the whole point of a wrapper - to deal with
> complexities so that the user doesn't have to.

Of course a wrapper should hide as much of the complexities as possible. And
be assured my wrapper wxSQLite3 hides a lot of them.

> If you are passing all of the complexities up to the user,
> why use you wrapper at all? Just call SQLite directly.

Have I written somewhere my wrapper passes *all* complexities up to the
user??? Definitely not!

If I understood it right each of
sqlite3_prepare/sqlite3_step/sqlite3_finalize may return a SQLITE_SCHEMA
error. At least for a SELECT statement sqlite3_prepare is called *once*
_before_ the data of *each row* are fetched using sqlite3_step and
sqlite3_finalize is called *once* after all rows have been processed. A
wrapper will seldom hide this kind of processing. So the wrapper can not
know whether the application is processing the data of each row or
collecting the data for later processing elsewhere.

If the SQLITE_SCHEMA error occurs for example after already 10 rows were
processed. What should the wrapper - or SQLite (if you decide to handle
SCHEMA errors internally) - do? If you recompile the statement calling
sqlite3_prepare wouldn't you refetch *all* rows again (unless you have
counted the number of rows already fetched and now skip this number of rows
before returning)?

And what does it mean if the SQLITE_SCHEMA error occurs for the first time
when calling sqlite3_finalize? Then you have already processed all rows.

How on earth a wrapper could hide this from the user?

If it is trivial to handle SQLITE_SCHEMA errors then SQLIte should do it. If
not, why and how should a wrapper do it?

>> For INSERT, UPDATE or DELETE a simple retry might be a good choice. But
>> how often should the retry take place? The SQLite FAQ code example
>> contains an endless loop!
>
> Not.  OK, I guess in theory, if another process were updating the
> schema at just the right rate so that the schema was different every
> times you retried, you could get an infinite loop.  But in practice,
> the loop never runs more than twice - 3 times in the extreme.

Coding infinite loops should be avoided even if there is only a theoretical
possibility it will ever loop forever.

>> In case of a SELECT statement the situation is still more complex. The
>> SCHEMA error could happen after reading several result rows.
>
> No.  SCHEMA errors happen prior to reading any data.

You mean a SQLITE_SCHEMA error can only occur when you try to read the data
of the *first* row of a SELECT query? And if you were able to read the first
row you will be able to read *all* rows?

Hard to believe but if that is truly the case then definitely SQLite should
handle this error internally. At least the documentation should be more
explicit about when a SQLITE_SCHEMA error may occur.

>> When retrying a query another problem arises if the SQL statement
>> contains bind variables. You would have to rebind the variables. To
>> handle this automatically would induce a lot of extra house keeping,
>> wouldn't it?
>
> See the sqlite3_transfer_bindings() API.

If SQLite already keeps track of all bindings it should keep a copy of the
SQL statement string, too.

Regards,

Ulrich Telle
Reply | Threaded
Open this post in threaded view
|

Re: Request for comment: Proposed SQLite API changes

D. Richard Hipp
In reply to this post by Ulrich Telle
Dennis Cote <[hidden email]> wrote:

> [hidden email] wrote:
>
> >Dan Kennedy <[hidden email]> wrote:
> >>>
> >>The authorization callback
> >>would have to be reinvoked from within sqlite3_step() too.
> >>    
> >>
> >
> >Yikes!  I didn't think of that.
> >  
> >
>
> I don't see why this should have any impact. If you pass the
> SQLITE_SCHEMA error back to the user, they will have to re-prepare the
> statement, which will also re-invoke the authorizer. Aren't the
> authorizer callbacks an automatic part of the compilation process? If
> the authorizer allowed you to compile the statement successfully the
> first time, isn't it almost certain that it would allow you to compile
> the statement again?
>
> Furthermore, I don't believe that most users are using an authorizer
> anyway (but I could definitely be wrong about that). In that case all
> the authorizer callbacks become no-ops don't they?
>

The authorizer is used to protect against SQL injection attaches
when the SQL text originates from user input.  Typically an
application will turn the authorizer on when preparing user-supplied
SQL then turn it right back off again so that its own internal
SQL can run unfiltered.  Example:

    sqlite3_set_authorizer(db, ignore_passwd_column);
    stmt1 = sqlite3_prepare(db, zSqlFromUser)
    sqlite3_set_authorizer(db, 0);
    stmt2 = sqlite3_prepare(db, zInternalSql);
    sqlite3_step(stmt1);  --  Oops!  Might try to recompile!

Note also that the authorizer will not necessary throw an error
the first time.  It might just cause certain columns in a table
to be ignored.  For example, in the CVSTrac system (used for
bug tracking on SQLite as well as elsewhere) user-generated
ticket reports can query any table in the database.  But if
the report requests the USER.PASSWD field, the authorizer causes
that field to return a NULL rather than the actual user password.
No error is generated so there is nothing to signal a problem
the first time the authorizer is run.  But if the statement
is then recompiled automatically with the authorizer turned
off, then the PASSWD information might leak through.
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Request for comment: Proposed SQLite API changes

Dennis Cote
[hidden email] wrote:

>The authorizer is used to protect against SQL injection attaches
>when the SQL text originates from user input.  Typically an
>application will turn the authorizer on when preparing user-supplied
>SQL then turn it right back off again so that its own internal
>SQL can run unfiltered.  Example:
>
>    sqlite3_set_authorizer(db, ignore_passwd_column);
>    stmt1 = sqlite3_prepare(db, zSqlFromUser)
>    sqlite3_set_authorizer(db, 0);
>    stmt2 = sqlite3_prepare(db, zInternalSql);
>    sqlite3_step(stmt1);  --  Oops!  Might try to recompile!
>
>Note also that the authorizer will not necessary throw an error
>the first time.  It might just cause certain columns in a table
>to be ignored.  For example, in the CVSTrac system (used for
>bug tracking on SQLite as well as elsewhere) user-generated
>ticket reports can query any table in the database.  But if
>the report requests the USER.PASSWD field, the authorizer causes
>that field to return a NULL rather than the actual user password.
>No error is generated so there is nothing to signal a problem
>the first time the authorizer is run.  But if the statement
>is then recompiled automatically with the authorizer turned
>off, then the PASSWD information might leak through.
>  
>
I see. So in order to re-prepare an SQL statement you would need to keep
a copy of the authorizer callback pointer that was used when it was
originally prepared. Couldn't this be done automatically as well?

Perhaps it could be made more explicit by adding an authorizer callback
parameter to a new sqlite3_prepare_v2() function. This function could
also include an SQL string destructor argument that could be used to
eliminate most SQL string copies as well.

Dennis Cote
Reply | Threaded
Open this post in threaded view
|

Re: Request for comment: Proposed SQLite API changes

Austin Ziegler
In reply to this post by D. Richard Hipp
On 11/4/05, [hidden email] <[hidden email]> wrote:

> The authorizer is used to protect against SQL injection attaches
> when the SQL text originates from user input.  Typically an
> application will turn the authorizer on when preparing user-supplied
> SQL then turn it right back off again so that its own internal
> SQL can run unfiltered.  Example:
>
>     sqlite3_set_authorizer(db, ignore_passwd_column);
>     stmt1 = sqlite3_prepare(db, zSqlFromUser)
>     sqlite3_set_authorizer(db, 0);
>     stmt2 = sqlite3_prepare(db, zInternalSql);
>     sqlite3_step(stmt1);  --  Oops!  Might try to recompile!

Well, obviously, part of the sqlite3_stmt structure, then, is the
authorizer that is used on that particular statement ;) So change it
so that *if* you decide to do this, sqlite3_stmt also has its
authorizer as well as the original SQL statement.

Better might be to have an alternative data structure (sqlite3_stmt2)
with calls as appropriate.

-austin
--
Austin Ziegler * [hidden email]
               * Alternate: [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Request for comment: Proposed SQLite API changes

Rob Lohman
In reply to this post by Dennis Cote
>>The authorizer is used to protect against SQL injection attaches
>>when the SQL text originates from user input.  Typically an
>>application will turn the authorizer on when preparing user-supplied
>>SQL then turn it right back off again so that its own internal
>>SQL can run unfiltered.  Example:
>>
>>    sqlite3_set_authorizer(db, ignore_passwd_column);
>>    stmt1 = sqlite3_prepare(db, zSqlFromUser)
>>    sqlite3_set_authorizer(db, 0);
>>    stmt2 = sqlite3_prepare(db, zInternalSql);
>>    sqlite3_step(stmt1);  --  Oops!  Might try to recompile!
>>
>>Note also that the authorizer will not necessary throw an error
>>the first time.  It might just cause certain columns in a table
>>to be ignored.  For example, in the CVSTrac system (used for
>>bug tracking on SQLite as well as elsewhere) user-generated
>>ticket reports can query any table in the database.  But if
>>the report requests the USER.PASSWD field, the authorizer causes
>>that field to return a NULL rather than the actual user password.
>>No error is generated so there is nothing to signal a problem
>>the first time the authorizer is run.  But if the statement
>>is then recompiled automatically with the authorizer turned
>>off, then the PASSWD information might leak through.
>>  
>>
> I see. So in order to re-prepare an SQL statement you would need to keep
> a copy of the authorizer callback pointer that was used when it was
> originally prepared. Couldn't this be done automatically as well?
>
> Perhaps it could be made more explicit by adding an authorizer callback
> parameter to a new sqlite3_prepare_v2() function. This function could
> also include an SQL string destructor argument that could be used to
> eliminate most SQL string copies as well.

This all starts to feel a bit "unsafe". What would happen if a program
would load a library that houses the callback and it gets unloaded
after the first prepare? Or is it a rule you need to have the callback
available for the entire run of the virtual machine?

Rob
12