[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1
At 11:58 31/08/2015, you wrote: >--- >But the only sure way to prevent anyone else from installing its own >authorizer is to change the function name in the SQLite library. >--- That wouldn't really work under Windows: GetProcAddress can provide access to a DLL function by index (ordinal value) without knowledge of its declared name. Also a quick look at the code with any hex editor will help finding the changed named. >you cannot protect against anything that code does. That's the true bottom line.
[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1
Dominique Devienne wrote: > One annoyance with the authorizer approach is that you cannot "stack them" Write your own authorizer that implements its own callback list. But the only sure way to prevent anyone else from installing its own authorizer is to change the function name in the SQLite library. > I guess that could be viewed as a security hole, but then there's no > action-code for calling set_authorizer apparently, > so anyone can override any one authorizer already in place, no? You cannot set an authorizer with an SQL statement. As for code you link into your program, and call: you cannot protect against anything that code does. Regards, Clemens
[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1
On Thu, Aug 27, 2015 at 4:21 PM, Dominique Devienne wrote: > On Thu, Aug 27, 2015 at 3:37 PM, Richard Hipp wrote: > >> On 8/27/15, Clemens Ladisch wrote: >> > Dominique Devienne wrote: >> >> how can we programatically reliably discover which (v)tables a view >> >> accesses, staying in documented behavior land? >> > >> > With an authorizer callback: >> > http://www.sqlite.org/c3ref/set_authorizer.html >> > >> >> Clever! I was about to write back that SQLite does not provide the >> capability that Dominique wants, but I think Clemens's answer is better! >> > > Thanks Clemens and Richard. If VTable names are reported, this will work > for us! > I'll try it, to move away from our current reliance on undefined behavior. > --DD > One annoyance with the authorizer approach is that you cannot "stack them", since there's no get_authorizer (or set_authorizer returning the old one). This is a large codebase, and it's also plugin-based, so knowing for sure who sets one if problematic and error prone. >From the doc: > Only a single authorizer can be in place on a database connection at a > time. > Each call to sqlite3_set_authorizer overrides the previous call. > Disable the authorizer by installing a NULL callback. > The authorizer is disabled by default. Any chance we could have a way to "stack" authorizers, in a future version of SQLite? I guess that could be viewed as a security hole, but then there's no action-code for calling set_authorizer apparently, so anyone can override any one authorizer already in place, no? Or am I missing something? Thanks, --DD
[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1
On Thu, Aug 27, 2015 at 4:20 PM, Hick Gunter wrote: > But you have to run the query as opposed to just parsing EXPLAIN > The doc says: The authorizer callback is invoked as SQL statements are being compiled by sqlite3_prepare() [and co.] So the query to prepare is different indeed (drop the EXPLAIN prefix I currently add). Note also my current EXPLAIN query is actually "run" as well, i.e. stepped through. --DD
[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1
On Thu, Aug 27, 2015 at 3:37 PM, Richard Hipp wrote: > On 8/27/15, Clemens Ladisch wrote: > > Dominique Devienne wrote: > >> how can we programatically reliably discover which (v)tables a view > >> accesses, staying in documented behavior land? > > > > With an authorizer callback: > > http://www.sqlite.org/c3ref/set_authorizer.html > > > > Clever! I was about to write back that SQLite does not provide the > capability that Dominique wants, but I think Clemens's answer is better! > Thanks Clemens and Richard. If VTable names are reported, this will work for us! I'll try it, to move away from our current reliance on undefined behavior. --DD
[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1
On 27 Aug 2015, at 3:20pm, Hick Gunter wrote: > But you have to run the query as opposed to just parsing EXPLAIN You have to _prepare() it. You don't have to use _step() . Though I suppose you should _finalize() it if you don't intend to use _step() . Simon.
[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1
Dominique Devienne wrote: > how can we programatically reliably discover which (v)tables a view > accesses, staying in documented behavior land? With an authorizer callback: http://www.sqlite.org/c3ref/set_authorizer.html Regards, Clemens
[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1
But you have to run the query as opposed to just parsing EXPLAIN -Urspr?ngliche Nachricht- Von: Richard Hipp [mailto:drh at sqlite.org] Gesendet: Donnerstag, 27. August 2015 15:37 An: General Discussion of SQLite Database Betreff: Re: [sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1 On 8/27/15, Clemens Ladisch wrote: > Dominique Devienne wrote: >> how can we programatically reliably discover which (v)tables a view >> accesses, staying in documented behavior land? > > With an authorizer callback: > http://www.sqlite.org/c3ref/set_authorizer.html > Clever! I was about to write back that SQLite does not provide the capability that Dominique wants, but I think Clemens's answer is better! -- D. Richard Hipp drh at sqlite.org ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1
On Thu, Aug 27, 2015 at 1:08 PM, Richard Hipp wrote: > On 8/27/15, Dominique Devienne wrote: > > > > I understand that https://www.sqlite.org/opcode.html doesn't explicitly > say > > what VOpen's p4 column will contain, so I guess one could argue this is > > undocumented behavior we should not rely on, > > On needn't argue this; it is explicitly stated in the EXPLAIN > documentation (https://www.sqlite.org/lang_explain.html): > >"The output from EXPLAIN and EXPLAIN QUERY PLAN is intended for > interactive analysis and troubleshooting only. The details of the > output format are subject to change from one release of SQLite to the > next. Applications should not use EXPLAIN or EXPLAIN QUERY PLAN since > their exact behavior is variable and only partially documented." > OK. Right you are of course. Thank you for pointing this out for me. I did that a long time ago, and probably didn't notice this disclaimer (I assume it was already there back then). That does not remove though the need for our application to do this kind of introspection. How then do you recommend we go about this? I.e. how can we programatically reliably discover which (v)tables a view accesses, staying in documented behavior land? Thanks, --DD PS: FWIW, I'm still curious why change took place. I could do a "blame" if I was Fossil-savvy, but even then I'm not sure I'd get it from the diff.
[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1
On 8/27/15, Clemens Ladisch wrote: > Dominique Devienne wrote: >> how can we programatically reliably discover which (v)tables a view >> accesses, staying in documented behavior land? > > With an authorizer callback: > http://www.sqlite.org/c3ref/set_authorizer.html > Clever! I was about to write back that SQLite does not provide the capability that Dominique wants, but I think Clemens's answer is better! -- D. Richard Hipp drh at sqlite.org
[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1
Recently upgraded, and we ran into failures because references to vtables (via opcode VOpen's p4 column) changed from vtab:module_ptr:vtable_ptr to just vtab:vtable_ptr. What was the intent behind this change? How can code one determine the module the vtable is coming from now? This is not a showstopper for us, because we have a single module and we were just asserting it matched our "singleton" module, but I definitely missed that in the release notes :) I understand that https://www.sqlite.org/opcode.html doesn't explicitly say what VOpen's p4 column will contain, so I guess one could argue this is undocumented behavior we should not rely on, but the alternatives of query parsing, w/o an SQLite-provided AST of its queries would be even worse IMHO. Just trying to get clarification on this. Thanks, --DD /*! * \brief Introspects the pcode (compiled query) of a view. * * Uses an EXPLAIN SQL statement to list all SQLite VDBE instructions a simple * query accessing the view compiles into, and looks for \em TableLock and * \em VOpen opcodes, gathering their p4 arguments. In the case of VOpen, uses * a virtual table specific introspection function. * * \param view_name a SQL view name. * \return the list of table or virtual tables accessed by that view. * * \sa VirtualSchema::find_by_pcode() * * \note this type of introspection should be much more reliable than trying * to parse the SQL definition of the view, given SQL's complex grammar. */
[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1
On 8/27/15, Dominique Devienne wrote: > > I understand that https://www.sqlite.org/opcode.html doesn't explicitly say > what VOpen's p4 column will contain, so I guess one could argue this is > undocumented behavior we should not rely on, On needn't argue this; it is explicitly stated in the EXPLAIN documentation (https://www.sqlite.org/lang_explain.html): "The output from EXPLAIN and EXPLAIN QUERY PLAN is intended for interactive analysis and troubleshooting only. The details of the output format are subject to change from one release of SQLite to the next. Applications should not use EXPLAIN or EXPLAIN QUERY PLAN since their exact behavior is variable and only partially documented." -- D. Richard Hipp drh at sqlite.org
