Help with sqlite3TreeViewSelect

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

Help with sqlite3TreeViewSelect

curmudgeon
I’ve been using this while debugging by inserting the following code just before the return statement at the bottom of the sqlite3Select procedure.

freopen("c:/SQLiteData/TreeView.txt","w",stdout);
sqlite3TreeViewSelect(0, p, 0);
freopen("CON","w",stdout);

(NB above only works if you define SQLITE_DEBUG).

I’ve been looking at the result for various queries. Let’s suppose I want to take the output from TreeView.txt and rebuild the query from that so I have a version of the query where I know which schema, table and column each ID token refers to. It looks fairly easy given the tables and columns are all numbered but there’s a couple of things I’m unsure of.


  1.  When I include a WITH statement in the query it prints the WITH statement but it also seems to incorporate the associated select(s) into the main SELECT such that the WITH statement can be effectively ignored. Is that always the case?
  2.  In the text below what does the line ‘IF NULL-ROW 8’ mean (the SELECT below that line is what was contained in the WITH statement)?

    |-- SELECT (7/23B3648) selFlags=0xc4 nSelectRow=253
    |-- result-set
    |   |-- {0:-1}  flags=0x820000
    |   |-- {0:7}  flags=0x820000
    |   '-- IF-NULL-ROW 8
    |       '-- SELECT-expr flags=0x2220800
    |           '-- SELECT (2/23B3A68) selFlags=0x40c4 nSelectRow=0
    |               |-- result-set
    |               |   '-- {9:1}  flags=0x820000
    |               |-- FROM
    |               |   |-- {9,*} Course tab='course' nCol=9 ptr=23B18A8
    |               |   '-- {11,*} Dam tab='dam' nCol=3 ptr=23B1908
    |               |-- WHERE
    |               |   '-- AND
    |               |       |-- EQ
    |               |       |   |-- {9:-1}  flags=0x820000
    |               |       |   '-- 28
    |               |       '-- EQ
    |               |           |-- {9:-1}  flags=0x20008
    |               |           |   '-- 28
    |               |           '-- {11:-1}  flags=0x820000
    |               '-- LIMIT
    |                   '-- 1
    |-- FROM
.....

Also, is there any way I could redirect the stdout to a memory buffer rather than a file (I’m using clang compiler on windows)? It would be great if there was a function along the lines of sqlite3_normalized_sql(stmt) that returned the sqlite3TreeViewSelect text.

_______________________________________________
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: [EXTERNAL] Help with sqlite3TreeViewSelect

Hick Gunter
WITH is basically syntactic sugar that allows you to name the result set of a certain select and refer to it by name, so that select has to appear in the generated bytecode and also in the query resolution tree.

Guessing what an element of the query resolution tree does would be very much easier if you were to provide the text of the select statement itself. You might also like to look at EXPLAIN QUERY PLAN  and then EXPLAIN to view the bytecode. I would guess that the WITH is only executed if the table internally numbered 8 does not deliver a row.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von x
Gesendet: Samstag, 27. Juli 2019 14:08
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Help with sqlite3TreeViewSelect

I’ve been using this while debugging by inserting the following code just before the return statement at the bottom of the sqlite3Select procedure.

freopen("c:/SQLiteData/TreeView.txt","w",stdout);
sqlite3TreeViewSelect(0, p, 0);
freopen("CON","w",stdout);

(NB above only works if you define SQLITE_DEBUG).

I’ve been looking at the result for various queries. Let’s suppose I want to take the output from TreeView.txt and rebuild the query from that so I have a version of the query where I know which schema, table and column each ID token refers to. It looks fairly easy given the tables and columns are all numbered but there’s a couple of things I’m unsure of.


  1.  When I include a WITH statement in the query it prints the WITH statement but it also seems to incorporate the associated select(s) into the main SELECT such that the WITH statement can be effectively ignored. Is that always the case?
  2.  In the text below what does the line ‘IF NULL-ROW 8’ mean (the SELECT below that line is what was contained in the WITH statement)?

    |-- SELECT (7/23B3648) selFlags=0xc4 nSelectRow=253
    |-- result-set
    |   |-- {0:-1}  flags=0x820000
    |   |-- {0:7}  flags=0x820000
    |   '-- IF-NULL-ROW 8
    |       '-- SELECT-expr flags=0x2220800
    |           '-- SELECT (2/23B3A68) selFlags=0x40c4 nSelectRow=0
    |               |-- result-set
    |               |   '-- {9:1}  flags=0x820000
    |               |-- FROM
    |               |   |-- {9,*} Course tab='course' nCol=9 ptr=23B18A8
    |               |   '-- {11,*} Dam tab='dam' nCol=3 ptr=23B1908
    |               |-- WHERE
    |               |   '-- AND
    |               |       |-- EQ
    |               |       |   |-- {9:-1}  flags=0x820000
    |               |       |   '-- 28
    |               |       '-- EQ
    |               |           |-- {9:-1}  flags=0x20008
    |               |           |   '-- 28
    |               |           '-- {11:-1}  flags=0x820000
    |               '-- LIMIT
    |                   '-- 1
    |-- FROM
.....

Also, is there any way I could redirect the stdout to a memory buffer rather than a file (I’m using clang compiler on windows)? It would be great if there was a function along the lines of sqlite3_normalized_sql(stmt) that returned the sqlite3TreeViewSelect text.

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

Re: [EXTERNAL] Help with sqlite3TreeViewSelect

curmudgeon
Thanks Gunter. I didn’t include the text of the original SELECT as it was basically an unnecessarily complex jumble of WITHs and embedded SELECTS just so I could see the response of the TreeViewSelect. I hoped someone would’ve been able to say “yes, the WITH is just included for completeness but is already integrated into the main SELECT”. I also hoped the ‘IF-NULL-ROW 8’ would have a simple explanation so didn’t think including the complex query would’ve been relevant (similar to including 30 lines of C code and asking what ‘goto’ meant).

I also made a mistake in what I printed in any case as the original WITH doesn’t appear in the part of the tree I printed (it was below the FROM at the end of it).


From: Hick Gunter<mailto:[hidden email]>
Sent: 28 July 2019 09:34
To: 'SQLite mailing list'<mailto:[hidden email]>
Subject: Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

WITH is basically syntactic sugar that allows you to name the result set of a certain select and refer to it by name, so that select has to appear in the generated bytecode and also in the query resolution tree.

Guessing what an element of the query resolution tree does would be very much easier if you were to provide the text of the select statement itself. You might also like to look at EXPLAIN QUERY PLAN  and then EXPLAIN to view the bytecode. I would guess that the WITH is only executed if the table internally numbered 8 does not deliver a row.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von x
Gesendet: Samstag, 27. Juli 2019 14:08
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Help with sqlite3TreeViewSelect
g the lin
I’ve been using this while debugging by inserting the following code just before the return statement at the bottom of the sqlite3Select procedure.

freopen("c:/SQLiteData/TreeView.txt","w",stdout);
sqlite3TreeViewSelect(0, p, 0);
freopen("CON","w",stdout);

(NB above only works if you define SQLITE_DEBUG).

I’ve been looking at the result for various queries. Let’s suppose I want to take the output from TreeView.txt and rebuild the query from that so I have a version of the query where I know which schema, table and column each ID token refers to. It looks fairly easy given the tables and columns are all numbered but there’s a couple of things I’m unsure of.


  1.  When I include a WITH statement in the query it prints the WITH statement but it also seems to incorporate the associated select(s) into the main SELECT such that the WITH statement can be effectively ignored. Is that always the case?
  2.  In the text below what does the line ‘IF NULL-ROW 8’ mean (the SELECT below that line is what was contained in the WITH statement)?

    |-- SELECT (7/23B3648) selFlags=0xc4 nSelectRow=253
    |-- result-set
    |   |-- {0:-1}  flags=0x820000
    |   |-- {0:7}  flags=0x820000
    |   '-- IF-NULL-ROW 8
    |       '-- SELECT-expr flags=0x2220800
    |           '-- SELECT (2/23B3A68) selFlags=0x40c4 nSelectRow=0
    |               |-- result-set
    |               |   '-- {9:1}  flags=0x820000
    |               |-- FROM
    |               |   |-- {9,*} Course tab='course' nCol=9 ptr=23B18A8
    |               |   '-- {11,*} Dam tab='dam' nCol=3 ptr=23B1908
    |               |-- WHERE
    |               |   '-- AND
    |               |       |-- EQ
    |               |       |   |-- {9:-1}  flags=0x820000
    |               |       |   '-- 28
    |               |       '-- EQ
    |               |           |-- {9:-1}  flags=0x20008
    |               |           |   '-- 28
    |               |           '-- {11:-1}  flags=0x820000
    |               '-- LIMIT
    |                   '-- 1
    |-- FROM
.....

Also, is there any way I could redirect the stdout to a memory buffer rather than a file (I’m using clang compiler on windows)? It would be great if there was a function along the lines of sqlite3_normalized_sql(stmt) that returned the sqlite3TreeViewSelect text.

_______________________________________________
sqlite-users mailing list
[hidden email]
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
[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: [EXTERNAL] Help with sqlite3TreeViewSelect

curmudgeon
In reply to this post by Hick Gunter
The following simple example might illustrate what I’m trying to achieve

CREATE TABLE tbl0 (a INTEGER PRIMARY KEY, b INTEGER);
CREATE TABLE tbl1 (a INTEGER PRIMARY KEY, c INTEGER);

Suppose a user enters the following sql

SELECT b, c FROM tbl0 INNER JOIN tbl1 USING (a) WHERE a > ?1;

I want to scan the sql, get the tables and columns associated with each ID token, check authorization and do some manipulation of my own. The sqlite3TreeViewSelect returns the following text

'-- SELECT (1/2364128) selFlags=0xc4 nSelectRow=179
    |-- result-set
    |   |-- {0:1}  flags=0x820000
    |   '-- {1:1}  flags=0x820000
    |-- FROM
    |   |-- {0,*} tbl0 tab='tbl0' nCol=2 ptr=2421C68
    |   '-- {1,*} tbl1 tab='tbl1' nCol=2 ptr=2421D88
    '-- WHERE
        '-- AND
            |-- GT
            |   |-- {0:-1}  flags=0x820000
            |   '-- VARIABLE(?1,1)
            '-- EQ
                |-- {0:-1}
                '-- {1:-1}

From this text it would be easy to extract the equivalent sql

SELECT t0.b, t1.c FROM tbl0 t0, tbl1 t1 WHERE t0.rowid > ?1 AND t0.rowid = t1.rowid;

and I’ll know exactly what every table and column each token ID maps to. I can check the user is allowed to access these and then change any of the sql I want to before executing it.

I could use the sqlite3_set_authorizer but the schema.table.column for each ID token isn’t sent to the authorizer callback in the order they appear in the sql. It also mysteriously omits some tokens.

e.g. for above sql the authorizer wouldn’t receive a call for the ‘a’ column in USING (a) yet if the sql read

SELECT b, c FROM tbl0 INNER JOIN tbl1 ON tbl0.a = tbl1.a WHERE tbl0.a > ?1;

the authorizer callback would be called for tbl0.a and tbl1.a.


_______________________________________________
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: [EXTERNAL] Help with sqlite3TreeViewSelect

Hick Gunter
Note that parsing debug output is not a stable method of analysis (meaning SQlite Dev can change anything at whim), whereas the authorizer interface is documented.

Your implicit claim is "not all instances of column reference are reported to the authorizer, notably those inside a USING clause".

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von x
Gesendet: Sonntag, 28. Juli 2019 13:49
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

The following simple example might illustrate what I’m trying to achieve

CREATE TABLE tbl0 (a INTEGER PRIMARY KEY, b INTEGER); CREATE TABLE tbl1 (a INTEGER PRIMARY KEY, c INTEGER);

Suppose a user enters the following sql

SELECT b, c FROM tbl0 INNER JOIN tbl1 USING (a) WHERE a > ?1;

I want to scan the sql, get the tables and columns associated with each ID token, check authorization and do some manipulation of my own. The sqlite3TreeViewSelect returns the following text

'-- SELECT (1/2364128) selFlags=0xc4 nSelectRow=179
    |-- result-set
    |   |-- {0:1}  flags=0x820000
    |   '-- {1:1}  flags=0x820000
    |-- FROM
    |   |-- {0,*} tbl0 tab='tbl0' nCol=2 ptr=2421C68
    |   '-- {1,*} tbl1 tab='tbl1' nCol=2 ptr=2421D88
    '-- WHERE
        '-- AND
            |-- GT
            |   |-- {0:-1}  flags=0x820000
            |   '-- VARIABLE(?1,1)
            '-- EQ
                |-- {0:-1}
                '-- {1:-1}

From this text it would be easy to extract the equivalent sql

SELECT t0.b, t1.c FROM tbl0 t0, tbl1 t1 WHERE t0.rowid > ?1 AND t0.rowid = t1.rowid;

and I’ll know exactly what every table and column each token ID maps to. I can check the user is allowed to access these and then change any of the sql I want to before executing it.

I could use the sqlite3_set_authorizer but the schema.table.column for each ID token isn’t sent to the authorizer callback in the order they appear in the sql. It also mysteriously omits some tokens.

e.g. for above sql the authorizer wouldn’t receive a call for the ‘a’ column in USING (a) yet if the sql read

SELECT b, c FROM tbl0 INNER JOIN tbl1 ON tbl0.a = tbl1.a WHERE tbl0.a > ?1;

the authorizer callback would be called for tbl0.a and tbl1.a.


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

Re: [EXTERNAL] Help with sqlite3TreeViewSelect

curmudgeon
>>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Help with sqlite3TreeViewSelect

Hick Gunter
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*"?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von x
Gesendet: Montag, 29. Juli 2019 10:05
An: SQLite mailing list <[hidden email]>
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Help with sqlite3TreeViewSelect

curmudgeon
>>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:[hidden email]] Im Auftrag von x
Gesendet: Montag, 29. Juli 2019 10:05
An: SQLite mailing list <[hidden email]>
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
[hidden email]
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
[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: [EXTERNAL] Help with sqlite3TreeViewSelect

Hick Gunter
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:[hidden email]] Im Auftrag von x
Gesendet: Montag, 29. Juli 2019 11:09
An: SQLite mailing list <[hidden email]>
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:[hidden email]] Im Auftrag von x
Gesendet: Montag, 29. Juli 2019 10:05
An: SQLite mailing list <[hidden email]>
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
[hidden email]
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
[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


___________________________________________
 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Help with sqlite3TreeViewSelect

curmudgeon
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 <[hidden email]> on behalf of Hick Gunter <[hidden email]>
Sent: Monday, July 29, 2019 10:53:02 AM
To: 'SQLite mailing list' <[hidden email]>
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:[hidden email]] Im Auftrag von x
Gesendet: Montag, 29. Juli 2019 11:09
An: SQLite mailing list <[hidden email]>
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:[hidden email]] Im Auftrag von x
Gesendet: Montag, 29. Juli 2019 10:05
An: SQLite mailing list <[hidden email]>
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
[hidden email]
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
[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


___________________________________________
 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
[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: [EXTERNAL] Help with sqlite3TreeViewSelect

Hick Gunter
You stated your goals were
a) authorization
b) some unspecified manipulation

I don't see how the order of the information available on the Sqlite authorizer interface is at all relevant to the result of authorization. Either the set of necessary operations are all allowed individually (total answer: yes) or at least one operation is not (total answer: no) with the possible exception of fields that are confidential and need to be replaced by NULL when an unprivileged user requests them.

e.g. the user's password could be protected from being  viewed for non-administrators by denying the request to read this field, thus returning null instead of the field contents, and using ifnull() to set a replacement string.

Whatever your unspecified manipulation is, questions about which internal representation was selected, and why, is something only the developers would be able to divulge, given willingness to do so.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von x
Gesendet: Montag, 29. Juli 2019 12:19
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

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 <[hidden email]> on behalf of Hick Gunter <[hidden email]>
Sent: Monday, July 29, 2019 10:53:02 AM
To: 'SQLite mailing list' <[hidden email]>
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:[hidden email]] Im Auftrag von x
Gesendet: Montag, 29. Juli 2019 11:09
An: SQLite mailing list <[hidden email]>
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:[hidden email]] Im Auftrag von x
Gesendet: Montag, 29. Juli 2019 10:05
An: SQLite mailing list <[hidden email]>
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
[hidden email]
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
[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


___________________________________________
 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
[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


___________________________________________
 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Help with sqlite3TreeViewSelect

Richard Hipp-3
On 7/29/19, Hick Gunter <[hidden email]> wrote:
> Qquestions about which internal
> representation ... is something only the developers would
> be able to divulge,

The internal representation and the output of sqlite3TreeViewSelect()
are emphatically not APIs. Both are undocumented and both can and do
change from one release to the next.  The sqlite3TreeViewSelect()
interface is intended for debugging use only.

If you want to know what the output of sqlite3TreeViewSelect() means,
you should consult the source code in the treeview.c source file for
the specific version of SQLite you are running.  That's what the
developers do.  The meanings of the fields in the internal
representation are well described by code comments, especially in the
sqliteInt.h file.   For information about specific fields or values,
try grepping in the source code.  There are usually nearby comments
that explain what is going on.

Sadly, the IF_NULL_ROW value is not as well commented as it might be.
But, by looking at the source code, you quickly get the idea that it
has something to do with LEFT JOIN.  And if you search the Fossil
repository history, you can see that the identifier was added here:

     https://www.sqlite.org/src/timeline?c=3a5860d86fadcf92

Using those clues, perhaps you can figure it out.  I added the
IF_NULL_ROW code myself but that was over two years ago and at this
point I don't remember exactly why.  I'll have to go back and reverse
engineer it, if and when I improve the comments, or should we ever
need to work on that particular aspect of the implementation.

As with all of the internal representation, we might find a better way
to accomplish the same thing tomorrow, and totally eliminate the
IF_NULL_ROW operator from the code.  So don't become too attached to
it.
--
D. Richard Hipp
[hidden email]
_______________________________________________
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: [EXTERNAL] Help with sqlite3TreeViewSelect

curmudgeon
Thanks Richard. I’ve being doing my best with the code but time is not on my side. It took me ages just to get the clang compiler to debug sqlite code (had to split the files).



It’s not as easy to get access to sqlite3TreeViewSelect on windows as it would appear to be in the unix debugger. When I did find out how to access it I noticed it returned a string that would enable me to do away with reams of code I wrote to do the same thing. I’m finding it hard to let go now. ☹



With a bit of extra code I’ve managed to get the result of the sqlite3TreeViewSelect sent to the authorizer callback a line at a time. How I would’ve loved a sqlite3_TreeView_sql(stmt) function although having messed about with the code I appreciate how difficult that would be.



________________________________
From: sqlite-users <[hidden email]> on behalf of Richard Hipp <[hidden email]>
Sent: Monday, July 29, 2019 4:54:36 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

On 7/29/19, Hick Gunter <[hidden email]> wrote:
> Qquestions about which internal
> representation ... is something only the developers would
> be able to divulge,

The internal representation and the output of sqlite3TreeViewSelect()
are emphatically not APIs. Both are undocumented and both can and do
change from one release to the next.  The sqlite3TreeViewSelect()
interface is intended for debugging use only.

If you want to know what the output of sqlite3TreeViewSelect() means,
you should consult the source code in the treeview.c source file for
the specific version of SQLite you are running.  That's what the
developers do.  The meanings of the fields in the internal
representation are well described by code comments, especially in the
sqliteInt.h file.   For information about specific fields or values,
try grepping in the source code.  There are usually nearby comments
that explain what is going on.

Sadly, the IF_NULL_ROW value is not as well commented as it might be.
But, by looking at the source code, you quickly get the idea that it
has something to do with LEFT JOIN.  And if you search the Fossil
repository history, you can see that the identifier was added here:

     https://www.sqlite.org/src/timeline?c=3a5860d86fadcf92

Using those clues, perhaps you can figure it out.  I added the
IF_NULL_ROW code myself but that was over two years ago and at this
point I don't remember exactly why.  I'll have to go back and reverse
engineer it, if and when I improve the comments, or should we ever
need to work on that particular aspect of the implementation.

As with all of the internal representation, we might find a better way
to accomplish the same thing tomorrow, and totally eliminate the
IF_NULL_ROW operator from the code.  So don't become too attached to
it.
--
D. Richard Hipp
[hidden email]
_______________________________________________
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: [EXTERNAL] Help with sqlite3TreeViewSelect

Simon Slavin-3
On 29 Jul 2019, at 5:44pm, x <[hidden email]> wrote:

> It’s not as easy to get access to sqlite3TreeViewSelect on windows as it would appear to be in the unix debugger. When I did find out how to access it I noticed it returned a string that would enable me to do away with reams of code I wrote to do the same thing. I’m finding it hard to let go now. ☹

Please remember that since the result of the call is not documented, it can change at any time.  So even if you write code which works perfectly with this version of SQLite, it may not work with later versions.
_______________________________________________
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: [EXTERNAL] Help with sqlite3TreeViewSelect

curmudgeon
I realise that Simon. I’m working on the basis that when things change I can use an older version until I can account for the changes. Seems easier than maintaining my own code. Thanks.



________________________________
From: sqlite-users <[hidden email]> on behalf of Simon Slavin <[hidden email]>
Sent: Monday, July 29, 2019 7:07:15 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

On 29 Jul 2019, at 5:44pm, x <[hidden email]> wrote:

> It’s not as easy to get access to sqlite3TreeViewSelect on windows as it would appear to be in the unix debugger. When I did find out how to access it I noticed it returned a string that would enable me to do away with reams of code I wrote to do the same thing. I’m finding it hard to let go now. ☹

Please remember that since the result of the call is not documented, it can change at any time.  So even if you write code which works perfectly with this version of SQLite, it may not work with later versions.
_______________________________________________
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