I’m still not sure what you’re getting at Gunter. The example code doesn’t present a problem, it’s when subqueries within subqueries and withs are added it gets a lot more complex.
I’ve already written c++ code that interprets (more or less) any sql and works out the schema.table.column each token ID belongs to but it’s lengthy code and when sqlite’s syntax changes I’ve got to relearn that code and make the appropriate changes. That’s all something that sqlite does in any case and all I’m trying to do is find a way to make use of that instead of having to maintain my own code. ________________________________ From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Hick Gunter <h...@scigames.at> Sent: Monday, July 29, 2019 10:53:02 AM To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect Let's go back to your example statement with your join of two tables. select b,c from tbl0 join tbl1 where tbl0.a = tbl1.a and tbl0.a > ?1; SQLIte should ask the authorizer the following questions: 1) SELECT 2) READ tbl0 3) READ tbl1 4) READ tbl0 field a 5) READ tbl1 field a 6) READ tbl0 field b 7) READ tbl1 field c Postulated internal dialog of the Query Planner QP: "Oh, a Select! May I SELECT on this connection?" QP: "Oh, FROM tbl0! May I READ tbl0?" QP: "Oh, FROM tbl1 too! May I READ tbl1?" QP: "Oh, I need tbl0.a for the join! May I READ tbl0.a? QP: "Oh, and I need tbl1.a for the join too! May I READ tbl1.a? QP: "Oh, I need tbl0.a again, but I already know the answer" QP: "Oh I need tbl0.b for the result set! May I READ tbl0.b?" QP: "Oh and I need tbl1.c the result set! May I READ tbl1.c?" Note that except fort he last two questions (where DENY means SELECT NULL), a DENY answer means that no more questions need to be asked. Maybe you would like to have the questions posed in a different order (e.g. grouped by table 1,2,4,6,3,5,7; or in the order they occur in the statement 1,6,7,2,3,4,5) because your procedure of answering them is easier to program that way. Point is, you don't get to choose. You need to be able to deal with the questions in any order and still be consistent about your answers. Anything else woudl remind me of the following conversation: The monks are sitting in their benches, praying their mandatory morning prayers, when ohne lights up a cigarette. "Hey, you aren't allowed to smoke during prayer!" "But I am, by special permission of the abbot!" "How come? I asked for permission to smoke during prayer and he declined!" "Well, I asked for permission to pray while smoking." -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von x Gesendet: Montag, 29. Juli 2019 11:09 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect >>That strikes me as purely procedural thinking. Does the set of allowed >>operations really depend on the order of the requests (which probably depends >>on the query plan)? E.g. "you can update this field of this table only if you >>read this other field from that other table *first*"? I’m not really sure what you mean Gunter. My problem is relating the column reference sent to the authorizer callback to an ID token within the sql as the orders differ. Sqlite orders them roughly as subqueries first followed by columns followed by WHEREs followed by ONs (but omitting USINGs) followed by ORDER BYs (complicated further by HAVINGs and GROUP BYs etc.). I’m not saying there’s anything wrong with that but writing code to anticipate the order they’re sent in is as fraught as hacking the sql code. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von x Gesendet: Montag, 29. Juli 2019 10:05 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect >>Your implicit claim is "not all instances of column reference are reported to >>the authorizer, notably those inside a USING clause That and you’ve got to anticipate the order they’re sent to the callback in. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
