SQLite Forum

Timeline
Login

20 forum posts by user aloys

2021-08-03
12:55 Reply: Fail to calculate long expression (artifact: d063c8179c user: aloys)

Calculating 8 mod 0 is undefined.

That's why SQLite returns a NULL value.

Shouldn't that be captured when the statement is parsed before it's executed?

How? The operand 0 is an intermediate result while executing resp calculating the long expression. The intermediate result for the right operand of the % operator is -0.0317193865612212. Since this value is not an integer, it is truncated to 0 by SQLite.

At least the SQL-99 standard does not define the operator % - I have not checked newer standard versions. Therefore the implementor decides how the operator works, and SQLite made the decision to cast the operands to integer.

2021-08-02
21:35 Reply: Fail to calculate long expression (artifact: 1e3b5bdc50 user: aloys)

The reason that the computation fails is how SQLite handles the modulo operator %. The SQLite documentation describes the operator as follows:

The % operator casts both of its operands to type INTEGER and then computes the remainder after dividing the left integer by the right integer.

So, as soon as the right operand is smaller than | 1 | it is truncated to 0, and alas a division by zero takes place, resulting in a NULL value.

In your long expression you have the subexpression 617%29%(operand) = 617%29%(-0.0317193865612212). That is, the third operand in this partial expression will be truncated to 0, and you have 617%29%0 = 8%0 = NULL. And this forces the whole expresssion to become NULL.

2021-07-26
11:42 Reply: Compile error when symbols SQLITE_OMIT_SHARED_CACHE and SQLITE_USER_AUTHENTICATION=1 are both defined (artifact: 0bfc5888a3 user: aloys)

After inspecting the code in more detail, I get the impression that the fix for the compile issue I have is rather simple. If SQLITE_OMIT_SHARED_CACHE is not defined, then no tables will be locked and therefore the member nTableLock would be always 0. That is, lines 187 ff in build.c would not be executed at all:

#if SQLITE_USER_AUTHENTICATION
    if( pParse->nTableLock>0 && db->init.busy==0 ){
      sqlite3UserAuthInit(db);
      if( db->auth.authLevel<UAUTH_User ){
        sqlite3ErrorMsg(pParse, "user not authenticated");
        pParse->rc = SQLITE_AUTH_USER;
        return;
      }
    }
#endif

That is, adding a check for symbol SQLITE_OMIT_SHARED_CACHE should fix the issue:

#ifndef SQLITE_OMIT_SHARED_CACHE
#if SQLITE_USER_AUTHENTICATION
    if( pParse->nTableLock>0 && db->init.busy==0 ){
      sqlite3UserAuthInit(db);
      if( db->auth.authLevel<UAUTH_User ){
        sqlite3ErrorMsg(pParse, "user not authenticated");
        pParse->rc = SQLITE_AUTH_USER;
        return;
      }
    }
#endif
#endif

It would be nice if the SQLite developer would consider to apply this modification for the next SQLite version. Thanks in advance.

2021-07-23
07:36 Reply: Compile error when symbols SQLITE_OMIT_SHARED_CACHE and SQLITE_USER_AUTHENTICATION=1 are both defined (artifact: 450632c85d user: aloys)

I'm building from the amalgamation. I'm aware of the fact that not all _OMIT_ options work as expected when building from the amalgamation. However, the option SQLITE_OMIT_SHARED_CACHE has no influence on the lemon-generated parser. Therefore I suppose that building from the amalgamation with this option enabled should work.

In the light of DRH's comment regarding shared cache mode about a year ago I would almost call it good practice to omit shared cache mode.

As far as I can tell using option SQLITE_OMIT_SHARED_CACHE seems to work properly when compiling from the amalgamation.

However, it's not a question of working at runtime. If the combination of the options SQLITE_OMIT_SHARED_CACHE and SQLITE_USER_AUTHENTICATION is used the code does not compile, because the compiler complains about the missing definition for nTableLock (in line 188 of build.c). That is, the problem exists also when building from canonical sources. The reason is simply that the use of the member nTableLock is not guarded by some #ifndef SQLITE_OMIT_SHARED_CACHE preprocessor instruction, as it most likeley should.

2021-07-22
11:59 Post: Compile error when symbols SQLITE_OMIT_SHARED_CACHE and SQLITE_USER_AUTHENTICATION=1 are both defined (artifact: 6c29d57e79 user: aloys)

If the preprocessor symbol SQLITE_OMIT_SHARED_CACHE is defined, the member nTableLock is not defined in the structure Parse.

If the preprocessor symbol SQLITE_USER_AUTHENTICATION=1 is also defined, the compiler emits an error message for the line

    if( pParse->nTableLock>0 && db->init.busy==0 ){

in function sqlite3FinishCoding (line 112602 in the SQLite amalgamation for version 3.36.0), because the undefined member nTableLock is referenced in this statement.

It would be nice if this issue could be fixed for the next SQLite version.

Thanks in advance.

2021-04-25
21:40 Reply: VACUUM fails on a database with enabled user authentication extension (artifact: 670e959ad7 user: aloys)

Yes, I use the authorizer callback API. In principle, it allows me to perform fine-grained authorization of the database access.

However, I also need some sort of user management, where users authenticate themselves with a password. I could have used a multi-user database system like MySQL or PostgreSQL, but in fact the application typically runs on a single computer and is used by a small number of different persons. A SQLite databae is much simpler to administrate in this case than a full-blown multi-user database system.

The user authentication extension seemed to be a good starting point, although the concept of access rights is currently rather limited with only 2 levels of access rights (admin and user). At the moment I have an additional separate table that I use to manage a list of roles per user, and the list of assigned roles of the currently logged-in user is passed on to the authorizer callback.

20:34 Reply: VACUUM fails on a database with enabled user authentication extension (artifact: 28d60a3933 user: aloys)

In the meantime I inspected the code of the user authtentication extension a bit closer. I have the impression that there is a small flaw in the implementation of the internal function userAuthCheckLogin (line 84 ff in ext/userauth/userauth.c). According to the comment above the function the function should Check to see if database zDb has a "sqlite_user" table. However, actually the function checks whether the table sqlite_user exists in the "main" database - see lines 93 ff:

  if( !userTableExists(db, "main") ){
    *peAuth = UAUTH_Admin;  /* No sqlite_user table.  Everybody is admin. */
    return SQLITE_OK;
  }

Later on table sqlite_user in database zDb is queried for the user id and password. If the table sqlite_user does not exist, this query fails, of course.

Due to this flaw attaching an empty new persistent database fails, and I think that is wrong behaviour. One should be able to attach a new empty database in any case, because it is not (yet) under user authentication control.

IMHO lines 93 ff in ext/userauth/userauth.c should read:

  if( !userTableExists(db, zDb) ){
    *peAuth = UAUTH_Admin;  /* No sqlite_user table.  Everybody is admin. */
    return SQLITE_OK;
  }

With this change the VACUUM command works as expected.

2021-04-18
09:10 Reply: VACUUM fails on a database with enabled user authentication extension (artifact: 1176d6862d user: aloys)

Thanks for your thorough analysis.

In the meantime I tested with an older SQLite version (version 3.8.7.4 December 2014). VACUUM doesn't work. So this supports your conclusion that VACUUM most likely never worked with enabled user authentication.

IMHO allowing to attach an empty temporary database for performing the VACUUM operation should do no harm, if an admin user was authorized before executing VACUUM.

However, this is probably not enough, because VACUUM has to create the table sqlite_user in the temporary database and later on to copy the content of this table. If I understand it correctly the table sqlite_user is read-only for admin users, so that inserting the values from the main database into the table in the temporary database probably will fail. I'm not sure whether this can be handled in the user authentication extension alone.

2021-04-16
21:01 Edit: VACUUM fails on a database with enabled user authentication extension (artifact: ee8f177c56 user: aloys)

I compiled SQLite 3.35.4 and the SQLite shell with enabled user authentication extension. Then I used the SQLite shell to create a new database. I added a user with the shell command

.user add admin 12345 1

I inserted, updated and deleted data for a while, and then later on I entered the SQL command

VACUUM;

The command failed. With logging turned on I got the following error messages:

(1) no such table: vacuum_db.sqlite_user in "INSERT INTO vacuum_db.'sqlite_user' SELECT*FROM"main".'sqlite_user'"
(1) statement aborts at 1: [vacuum;] no such table: vacuum_db.sqlite_user
Error: no such table: vacuum_db.sqlite_user

Thereafter I tried to perform a user login before executing the VACUUM command:

.user login admin 12345
VACUUM;

The error messages look a bit different, but the VACUUM command still fails:

(1) no such table: vacuum_db.sqlite_user in "SELECT pw=sqlite_crypt(?1,pw), isAdmin FROM "vacuum_db".sqlite_user WHERE uname=?2"
(279) statement aborts at 4: [ATTACH '' AS vacuum_db] unable to open database:
(23) statement aborts at 1: [vacuum;] unable to open database:
Error: unable to open database:

I didn't find any hint in the documentation of the user authentication extension that this behaviour were to be expected.

Am I doing anything wrong? Or is there some sort of flaw in the user authentication extension?

Thanks in advance for any hint how to overcome this problem.

21:00 Post: VACUUM fails on a database with enabled user authentication extension (artifact: 939e3c71fd user: aloys)

I compiled SQLite and the SQLite shell with enabled user authentication extension. Then I used the SQLite shell to create a new database. I added a user with the shell command

.user add admin 12345 1

I inserted, updated and deleted data for a while, and then later on I entered the SQL command

VACUUM;

The command failed. With logging turned on I got the following error messages:

(1) no such table: vacuum_db.sqlite_user in "INSERT INTO vacuum_db.'sqlite_user' SELECT*FROM"main".'sqlite_user'"
(1) statement aborts at 1: [vacuum;] no such table: vacuum_db.sqlite_user
Error: no such table: vacuum_db.sqlite_user

Thereafter I tried to perform a user login before executing the VACUUM command:

.user login admin 12345
VACUUM;

The error messages look a bit different, but the VACUUM command still fails:

(1) no such table: vacuum_db.sqlite_user in "SELECT pw=sqlite_crypt(?1,pw), isAdmin FROM "vacuum_db".sqlite_user WHERE uname=?2"
(279) statement aborts at 4: [ATTACH '' AS vacuum_db] unable to open database:
(23) statement aborts at 1: [vacuum;] unable to open database:
Error: unable to open database:

I didn't find any hint in the documentation of the user authentication extension that this behaviour were to be expected.

Am I doing anything wrong? Or is there some sort of flaw in the user authentication extension?

Thanks in advance for any hint how to overcome this problem.

2021-01-06
13:36 Reply: Precompiled sqlite3.dll with sqlite3_user_authenticate (artifact: 76839ba570 user: aloys)

The precompiled binaries of the project SQLite3 Multiple Ciphers include also the user authentication extension you are referring to. Yes, the project offers database encryption, too, but if you don't need encryption, just don't use it.

2020-10-17
19:00 Reply: SQLite encryption with SEE (artifact: c4ebce7b47 user: aloys)

In fact, SQLCipher suffers from the same problem as System.Data.SQLite 1.0.113: the (unofficial) SQLite encryption API has been removed from the publicly available SQLite source code. And hence, SQLCipher supports only an older SQLite version, that is, not the latest one. I heard that work is in progress to adapt SQLCipher for newer SQLite versions, but that the adapted version will be available only to paying customers.

AFAIK the .Net version of SQLCipher is also only available as a commercial product at a price of 499 $/developer. However, one could probably build the .Net package with SQLCipher support on ones own.

2020-06-30
09:59 Reply: Using a VFS shim together with another user-specified VFS (artifact: 19674a9956 user: aloys)

You don't need unique names.

I doubt that that is true. The registered VFSes are members of a linked list, and function sqlite3_vfs_find uses this list to identify a VFS by name. If you have 2 or more VFSes with the same name, only the first in the list can be found.

You unregister the original VFS (putting its VFS object in a private list), then give your shim clone the same name before registering it.

Ok, if the original VFS is no longer registered this will work. However, if there are other VFS shims involved, things could possibly become nasty.

I keep thinking there is a supported way to get the list of all current VFS's, but it escapes me right now.

The SQLite shell command vfslist allows to walk through the list of registered VFSes.

It seems that all sorts of automatism in combining a VFS shim with existing VFSes could lead to weird effects. Most likely I will be better off to allow a user/developer to explicitly combine my VFS shim with a certain other underlying VFS and assign a new name of his/her choice to the combined VFS.

2020-06-29
20:11 Reply: Using a VFS shim together with another user-specified VFS (artifact: 3cc400ac9b user: aloys)

Thanks, David, for the sketch how the problem could be possibly tackled.

The approach sounds rather tricky, especially, if a user has several database connections open, possibly using different VFSes. If I understood it correctly, the VFS names need to be unique. That is, I would have to create a clone of the VFS shim with a different name for each different underlying VFS.

The main problem I see is that I would have to create VFS shim clones for all potential underlying VFSes in advance, because the SQLite code analyzing the URI parameters already calls sqlite3_vfs_find. And this can't be easily intercepted.

I get the impression that this approach isn't going to work in the general case, when I do not know in advance which underlying VFSes the user might want to use.

16:55 Reply: Using a VFS shim together with another user-specified VFS (artifact: 024baa2560 user: aloys)

Well, I know that vfs shims can be nested. However, the problem here is that if a user specifies a different vfs using the vfs URI parameter, the parameter is handled by SQLite internally.

For example, if "vfstrace" is enabled in the SQLite shell, the tracing vfs shim is set as the default vfs, typically using the previous default vfs as the underlying vfs. However, if the user now uses the vfs URI parameter to select a different vfs, e.g. "unix-excl", tracing will no longer occur.

My question is whether there is a way to let the user select a vfs via URI, but still keep the vfs shim enabled. In the example, the effect should be that "trace" vfs shim uses "unix-excl" as the underlying vfs, if the user specifies the URI parameter "vfs=unix-excl". Currently, "unix-excl" would be used without "trace".

11:36 Post: Using a VFS shim together with another user-specified VFS (artifact: fbfa3927ae user: aloys)

I know that it is possible to register a new default VFS, which is in my case a VFS shim forwarding all VFS commands to the previous default VFS, but performing some additional actions.

However, a user can overwrite the default VFS by specifying the parameter "vfs=" in the URI on opening a database.

What I want to accomplish now, is to respect the "vfs=" parameter, but adding the special VFS shim on top of the user-specified VFS nevertheless. Is that somehow possible? How could that be accomplished?

Thanks for any hints.

2020-05-04
07:41 Reply: Week numbers in queries (artifact: 420eb9b708 user: aloys)

My guess is that the OP quoted from strftime man page or a similar source.

Maybe SQLite could adopt Arnold Robbins' strftime. The code is in the public domain. So there wouldn't be a license issue.

2020-05-01
14:07 Reply: Detect whether database was opened in shared cache mode (artifact: 08ac221447 user: aloys)

Ok, I understand. Thank you very much for the explanation.

However, if you want to discourage the use of shared cache, you could maybe change the build logic for future SQLite versions. That is, instead of having the option SQLITE_OMIT_SHARED_CACHE the option SQLITE_ENABLE_SHARED_CACHE could be introduced.

13:28 Reply: Detect whether database was opened in shared cache mode (artifact: 58b8ff1551 user: aloys)

Is this on purpose that such an interface is missing? For almost all options and features there are functions or pragma statements to query the current status. Why not for the shared cache mode?

I guess that internally SQLite must be able to determine this to take the right actions. Maybe function sqlite3_db_status could be extended to return this information?

2020-04-30
21:27 Post: Detect whether database was opened in shared cache mode (artifact: 871b908584 user: aloys)

From the documentation I see that function sqlite3_enable_shared_cache allows to globally enable or disable shared cache mode, and that a user can select shared cache mode on opening a database file either by setting an open flag or the URI parameter cache - overriding the global setting.

However, I didn't find a function to query the state of shared cache mode, neither the global setting nor whether a database was opened in shared cache mode or not.

Did I overlook anything? Is there a method to find out whether a database file was opened in shared cache mode or not?

Thanks in advance for any hints.