unsafe use of virtual table

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

unsafe use of virtual table

Tom Bassel
Hi,

I have noticed a change between 3.30 and 3.31.1 and searched for more
info on "unsafe use of virtual table" on sqlite.org but could not find
anything relevant.

In 3.30:

SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create view somestats as select name, pgoffset from dbstat;
sqlite> select * from somestats;
sqlite_master|0


But in 3.31.1:

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create view somestats as select name, pgoffset from dbstat;
sqlite> select * from somestats;
Error: unsafe use of virtual table "dbstat"

Could someone point me to where I can find more info on unsafe use of
virtual tables?

Thanks
Tom

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: unsafe use of virtual table

Keith Medcalf

This is part of the trusted schema.  

Virtual Tables and Functions can be labeled as DIRECT_ONLY, INNOCUOUS, or unlabeled.  

INNOCUOUS virtual tables and functions can be used anywhere they are allowed including in the schema and views and indexes and so forth (provided that they would otherwise be permitted in that location).  DIRECT_ONLY virtual tables and functions can only be used from top-level SQL, ever.

Unlabeled virtual tables and views depend on whether or not the schema is trusted.  If the schema containing those things (in views, the schema definitions, etc) is untrusted, then those unlabeled virtual tables and functions are treated as DIRECT_ONLY.  If the schema is trusted, then there are no restrictions on the use of unlabeled virtual tables and functions.  The TEMP schema is always trusted since it must have always been created by the application/user and cannot have been a "crafted part" of the database.

The dbstat virtual table is DIRECT_ONLY meaning that since 3.30.0 it can only be used in top-level (directly issued) SQL and not in a view, even a view created in the temp database.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of [hidden email]
>Sent: Tuesday, 4 February, 2020 14:35
>To: [hidden email]
>Subject: [sqlite] unsafe use of virtual table
>
>Hi,
>
>I have noticed a change between 3.30 and 3.31.1 and searched for more
>info on "unsafe use of virtual table" on sqlite.org but could not find
>anything relevant.
>
>In 3.30:
>
>SQLite version 3.30.0 2019-10-04 15:03:17
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> create view somestats as select name, pgoffset from dbstat;
>sqlite> select * from somestats;
>sqlite_master|0
>
>
>But in 3.31.1:
>
>SQLite version 3.31.1 2020-01-27 19:55:54
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> create view somestats as select name, pgoffset from dbstat;
>sqlite> select * from somestats;
>Error: unsafe use of virtual table "dbstat"
>
>Could someone point me to where I can find more info on unsafe use of
>virtual tables?
>
>Thanks
>Tom
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: unsafe use of virtual table

Tom Bassel
In reply to this post by Tom Bassel
Ah I see now. Thank you Keith!

Tom

Message: 14
Date: Tue, 04 Feb 2020 14:57:06 -0700
From: "Keith Medcalf" <[hidden email]>
To: "SQLite mailing list" <[hidden email]>
Subject: Re: [sqlite] unsafe use of virtual table
Message-ID: <[hidden email]>
Content-Type: text/plain; charset="utf-8"


This is part of the trusted schema.

Virtual Tables and Functions can be labeled as DIRECT_ONLY, INNOCUOUS, or unlabeled.

INNOCUOUS virtual tables and functions can be used anywhere they are allowed including in the schema and views and indexes and so forth (provided that they would otherwise
 be permitted in that location).  DIRECT_ONLY virtual tables and functions can only be used from top-level SQL, ever.

Unlabeled virtual tables and views depend on whether or not the schema is trusted.  If the schema containing those things (in views, the schema definitions, etc) is untrus
ted, then those unlabeled virtual tables and functions are treated as DIRECT_ONLY.  If the schema is trusted, then there are no restrictions on the use of unlabeled virtua
l tables and functions.  The TEMP schema is always trusted since it must have always been created by the application/user and cannot have been a "crafted part" of the data
base.

The dbstat virtual table is DIRECT_ONLY meaning that since 3.30.0 it can only be used in top-level (directly issued) SQL and not in a view, even a view created in the temp
 database.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users