Re: [sqlite] 3.31.0 Request TRUSTED_SCHEMA and pragma's
On 1/21/20, Keith Medcalf wrote: > > Richard, > > The TRUSTED_SCHEMA setting works really well but I have noticed one problem > (there may be more, but I haven't run across any yet) with it that is > perhaps easy to address, though it needs to be done properly. That is > perhaps adding an innocuous flag to pragma definitions in mkpragmatab.tcl so > that it can be carried though into the vtable code that handles > pragma_ xConnect method. > > This would permit pragma's such as table_info (for example) to be marked as > innocuous so that pragma_table_info could be used in a view even when the > schema is untrusted. That would potentially leak information about the schemas of other attached database files. It seems like a harmless information leak, but it is a leak nevertheless. If you are setting untrusted schema (as you probably should) but you need to use pragma virtual tables inside of triggers and views, consider putting them inside TEMP triggers and views. TEMP triggers and views, because they must originate in the application itself, are always trusted. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.31.0 Request TRUSTED_SCHEMA and pragma's
On Tuesday, 21 January, 2020 05:28, Richard Hipp wrote: >On 1/21/20, Keith Medcalf wrote: >> Richard, >> >> The TRUSTED_SCHEMA setting works really well but I have noticed one >> problem (there may be more, but I haven't run across any yet) with >> it that is perhaps easy to address, though it needs to be done >> properly. That is perhaps adding an innocuous flag to pragma >> definitions in mkpragmatab.tcl so that it can be carried though >> into the vtable code that handles pragma_ xConnect >> method. >> >> This would permit pragma's such as table_info (for example) to be >> marked as innocuous so that pragma_table_info could be used in a >> view even when the schema is untrusted. > That would potentially leak information about the schemas of other > attached database files. It seems like a harmless information leak, > but it is a leak nevertheless. > > If you are setting untrusted schema (as you probably should) but you > need to use pragma virtual tables inside of triggers and views, > consider putting them inside TEMP triggers and views. TEMP triggers > and views, because they must originate in the application itself, are > always trusted. Done, tested, and putting those views in temp works just fine. And yes, I agree that not marking the pragma vtabs as innocuous is the right thing. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GENERATE_SERIES is not available in the command-line shell
> > Is it going to be fixed in 3.31? > > I'll fix the documentation, so that it doesn't say that any more, if > that is what you mean by "fixed". :-) > > Hoped to have it compiled in by default :-) It'd be nice addition... Come on, typical Linux or Windows has multiple gigabytes of memory... I'm not talking about enabling it by default for resource-constrained builds. Thanks for the response anyway. > You can use a recursive common table expression in place of > generated_series(). Like this: > > WITH generate_series(value) AS ( > VALUES($START) > UNION ALL > SELECT value + $STEP FROM generate_series > WHERE value $ STEP <= $STOP > ) > SELECT value FROM generate_series; > > This is more typing, I know. But it is the official SQL standard way > of getting an ascending list of integers. > > I would prefer more PostgreSQL'ish way of accomplishing the same :-) It's crazy useful for ad-hoc one-liner queries to generate lots of data. Have a good day! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] GENERATE_SERIES is not available in the command-line shell
Hi! In the docs here https://www.sqlite.org/series.html there's this statement "The generate_series(START,END,STEP) table-valued function is a loadable extension included in the SQLite source tree, **and compiled into the command-line shell.**" Unfortunately in version 3.30.1 on Windows downloaded from SQLite.org it's not available by default. Is it going to be fixed in 3.31? Thanks! C:\>sqlite3 SQLite version 3.30.1 2019-10-10 20:19:45 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> SELECT value FROM generate_series(5,50); Error: no such table: generate_series sqlite> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Reporting documentation issues/unclarity?
Should issues with the documentation (i.e., missing/unclear things) be reported
to this mailing list too?
Specifically, something that was unclear to me while implementing my own
aggregate function is what happens if sqlite3_result_error() is called and
another result functions gets called afterwards. So, suppose we have:
void stepfun(sqlite3_context *ctxt, int nArgs, sqlite3_value **args)
{
... random code here...
sqlite3_result_error(ctxt, "Something went bad!", -1);
...more random code...
sqlite3_result_int(ctxt, 42);
return;
}
Would the overall function still report an error or would the call to
sqlite3_result_int overwrite the earlier error and have it return successfully
again?
Cheers,
Merijn
signature.asc
Description: Message signed with OpenPGP
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reporting documentation issues/unclarity?
On 1/21/20, Merijn Verstraaten wrote:
> Should issues with the documentation (i.e., missing/unclear things) be
> reported to this mailing list too?
>
> Specifically, something that was unclear to me while implementing my own
> aggregate function is what happens if sqlite3_result_error() is called and
> another result functions gets called afterwards. So, suppose we have:
>
> void stepfun(sqlite3_context *ctxt, int nArgs, sqlite3_value **args)
> {
> ... random code here...
> sqlite3_result_error(ctxt, "Something went bad!", -1);
> ...more random code...
> sqlite3_result_int(ctxt, 42);
> return;
> }
>
> Would the overall function still report an error or would the call to
> sqlite3_result_int overwrite the earlier error and have it return
> successfully again?
For details like this, I think it is best to just ask on the mailing
list, and not expect that the answer will be documented.
Writing documentation is a careful balance between brevity and detail.
You don't what to explain what happens in every corner case, as that
will attention away from the main use case, where it belongs.
IIRC, in this case, sqlite3_result_error() persists. The subsequent
sqlite3_result_int() call merely changes the error message to the
number 42. If you want to cancel a prior call to
sqlite3_result_error(), you need to invoke
sqlite3_result_error_code(context, SQLITE_OK).
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GENERATE_SERIES is not available in the command-line shell
On 1/21/20, Digital Dog wrote: > Hi! > > In the docs here > https://www.sqlite.org/series.html > there's this statement "The generate_series(START,END,STEP) table-valued > function is a loadable extension included in the SQLite source tree, **and > compiled into the command-line shell.**" Unfortunately in version 3.30.1 on > Windows downloaded from SQLite.org it's not available by default. > Is it going to be fixed in 3.31? I'll fix the documentation, so that it doesn't say that any more, if that is what you mean by "fixed". :-) You can use a recursive common table expression in place of generated_series(). Like this: WITH generate_series(value) AS ( VALUES($START) UNION ALL SELECT value + $STEP FROM generate_series WHERE value $ STEP <= $STOP ) SELECT value FROM generate_series; This is more typing, I know. But it is the official SQL standard way of getting an ascending list of integers. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
