Feature request, sqlite3_stmt_action

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

Feature request, sqlite3_stmt_action

siscia
Hi List,

I would like to propose a feature and I believe here is the best place.
Please keep in mind that I am ready to work on a patch for this feature if
needed, but I would like to discuss it here first.

I would like to propose a function (named `sqlite3_stmt_action` for the sake
of discussion) that allow to understand if a specific statement is either a
SELECT, UPDATE, DELETE or INSERT.

Similar functionalities are provided by the authorizer which has a quite
non-ergonomic interface relying on callbacks. Indeed the use of the
authorizer for this is challenging, especially in a multi-threaded
environments with several databases.

The prototype that I am envisioning for the function would be something
like:

    int sqlite3_stmt_action(sqlite3_stmt* stmt)

where the function will return the action code
(https://www.sqlite.org/c3ref/c_alter_table.html) of the statement passed as
input.

We could go even a little further and return something similar to the
authorizer input itself:

   int sqlite3_stmt_action(sqlite_stmt* stmt, const char**, const char**,
const char**, const char**)

where the extra `const char**` will point to the NULL terminated string --
just like the authorizer -- that indicates tables name and index names where
it makes sense.

This new interface will make possible to even deprecate the authorizer
itself, since it can be implemented on top of `sqlite3_stmt_action` while
being more ergonomic especially in multi-threaded, multi-database
environments.

This same feature is already been required in the list itself:

o)
http://sqlite.1065341.n5.nabble.com/Determine-type-of-prepared-statement-via-C-Interface-td82075.html

o)
http://sqlite.1065341.n5.nabble.com/Determine-query-type-td83553.html#a83554

o)
http://sqlite.1065341.n5.nabble.com/Distinguish-type-of-statements-td106281.html#a106282
(myself)

even if most of those use cases have been solved using the stmt_readonly
interface.

Thanks for your attention!

Cheers,
Simone



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
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: Feature request, sqlite3_stmt_action

David Raymond
I'm not familiar with the C API, but the question I'll ask is this: How should this work with triggers? Running a statement as simple as "delete from foo;" could result in any number of different updates, deletes or inserts from any number of different tables, so how should that be reported?


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of siscia
Sent: Wednesday, March 27, 2019 1:05 PM
To: [hidden email]
Subject: [sqlite] Feature request, sqlite3_stmt_action

Hi List,

I would like to propose a feature and I believe here is the best place.
Please keep in mind that I am ready to work on a patch for this feature if
needed, but I would like to discuss it here first.

I would like to propose a function (named `sqlite3_stmt_action` for the sake
of discussion) that allow to understand if a specific statement is either a
SELECT, UPDATE, DELETE or INSERT.

Similar functionalities are provided by the authorizer which has a quite
non-ergonomic interface relying on callbacks. Indeed the use of the
authorizer for this is challenging, especially in a multi-threaded
environments with several databases.

The prototype that I am envisioning for the function would be something
like:

    int sqlite3_stmt_action(sqlite3_stmt* stmt)

where the function will return the action code
(https://www.sqlite.org/c3ref/c_alter_table.html) of the statement passed as
input.

We could go even a little further and return something similar to the
authorizer input itself:

   int sqlite3_stmt_action(sqlite_stmt* stmt, const char**, const char**,
const char**, const char**)

where the extra `const char**` will point to the NULL terminated string --
just like the authorizer -- that indicates tables name and index names where
it makes sense.

This new interface will make possible to even deprecate the authorizer
itself, since it can be implemented on top of `sqlite3_stmt_action` while
being more ergonomic especially in multi-threaded, multi-database
environments.

This same feature is already been required in the list itself:

o)
http://sqlite.1065341.n5.nabble.com/Determine-type-of-prepared-statement-via-C-Interface-td82075.html

o)
http://sqlite.1065341.n5.nabble.com/Determine-query-type-td83553.html#a83554

o)
http://sqlite.1065341.n5.nabble.com/Distinguish-type-of-statements-td106281.html#a106282
(myself)

even if most of those use cases have been solved using the stmt_readonly
interface.

Thanks for your attention!

Cheers,
Simone



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
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: Feature request, sqlite3_stmt_action

Olivier Mascia
In reply to this post by siscia

> Le 27 mars 2019 à 18:04, siscia <[hidden email]> a écrit :
>
> I would like to propose a function (named `sqlite3_stmt_action` for the sake
> of discussion) that allow to understand if a specific statement is either a
> SELECT, UPDATE, DELETE or INSERT.

There is probably a much more complex need that I did not grasp reading this request. What stops you from parsing the beginning of the statement text to decide if it is a select, update, delete or insert?

--
Best regards, Meilleures salutations, Met vriendelijke groeten,  
Olivier Mascia (from mobile device)



_______________________________________________
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: Feature request, sqlite3_stmt_action

Dominique Devienne
On Thu 28 Mar 2019 at 08:07, Olivier Mascia <[hidden email]> wrote:

>
> > Le 27 mars 2019 à 18:04, siscia <[hidden email]> a écrit :
> >
> > I would like to propose a function (named `sqlite3_stmt_action` for the
> sake
> > of discussion) that allow to understand if a specific statement is
> either a
> > SELECT, UPDATE, DELETE or INSERT.
>
> There is probably a much more complex need that I did not grasp reading
> this request. What stops you from parsing the beginning of the statement
> text to decide if it is a select, update, delete or insert?


Because it’s never as simple as it looks... CTEs anyone ? It can be
approximated sure. But will typically be brittle.

I’ve long wished for an AST for SQLite statements but in fact the grammar
actions directly build the internal data structures, it’s not two phase

>
_______________________________________________
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: Feature request, sqlite3_stmt_action

R Smith-2
In reply to this post by Olivier Mascia
On 2019/03/28 9:07 AM, Olivier Mascia wrote:
>> Le 27 mars 2019 à 18:04, siscia <[hidden email]> a écrit :
>>
>> I would like to propose a function (named `sqlite3_stmt_action` for the sake
>> of discussion) that allow to understand if a specific statement is either a
>> SELECT, UPDATE, DELETE or INSERT.
> There is probably a much more complex need that I did not grasp reading this request. What stops you from parsing the beginning of the statement text to decide if it is a select, update, delete or insert?


Having done this already, allow me to offer some recounting of the
difficulties:

First there are typically two things a programmer is interested in
(well, if you maintain an SQLite management utility or the like):
-  First: Will the Query produce data output back that I need to show to
the user?, or will it silently execute?
-  If it does produce output, is this confirming the state (such as when
calling a pragma command), or is this output that I need to show the
user, or perhaps log?

-  Then: Will the query change the database?
-  Put another way, will it work on a read-only file?
-  or, will it alter the table content that is currently displayed? Do I
need to re-run the display query?
-  or will it change the schema?
-  Do I need to re-parse the schema to show the user the DB layout after
executing?

Some of these SQLite does cater for, but many not, and there are some
work-aroundy ways of accomplishing it.

For instance, you might reparse the schema after ANY non-select query.
But then - how do I know if it is anything other than a SELECT query?

The obvious answer is not to see if it's any of INSERT, UPDATE, CREATE,
etc... but to simply see if it is indeed a SELECT query. Right?

But then, what about CREATE TABLE t AS SELECT a,b,c, FROM.....

Or if it is a CTE, consider these two:

WITH X(z) AS (SELECT 1) SELECT z FROM X;

vs.

WITH X(z) AS (SELECT 1) INSERT INTO t(z) SELECT z FROM X;

These are already difficult to self-parse, and they are extremely simple
examples.

I would even be happy to have something like extending the EXPLAIN QUERY
PLAN sql interface to include something like:
EXPLAIN QUERY RESULT .... ;

which outputs a simple row of values that can tell me:

- This query produces results - YES/NO (even if those results may be
empty, is it the /intent/ of the query to produce results?),
- It updates the data - YES/NO,
- It updates the schema - YES/NO
- It is a pragma or setting adjustment - YES/NO

Maybe even, if possible, This query updates these tables: x1, x2, x3...
etc. (some of which might hide behind an FK relation or Trigger)  but I
know this is pushing my luck.  :)

Plus, I really do not mind if this explain takes some time, it will be
faster and more future-proof than any self-parsing one can do.


Cheers,
Ryan


_______________________________________________
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: Feature request, sqlite3_stmt_action

Dominique Devienne
On Thu, Mar 28, 2019 at 10:59 AM R Smith <[hidden email]> wrote:

> Maybe even, if possible, This query updates these tables: x1, x2, x3...
> etc. (some of which might hide behind an FK relation or Trigger)  but I
> know this is pushing my luck.  :)
>

What I ended-up doing is introspecting the VDBE program of the statement.
It's not exactly easy, and can be brittle too, since the output is not
"publicly documented"
so subject to change w/o notice, but I consider this approach less brittle
than parsing the SQL.


> Plus, I really do not mind if this explain takes some time, it will be
> faster and more future-proof than any self-parsing one can do.


Right. Some info about the statement from EXPLAIN QUERY PLAN, that DRH would
agree to, and accept to "publicly document" and thus support would be nice,
good idea.
No an AST of course, but would go a long way already, for those of us that
need/wish for that.

I'll put it on my Xmas list :). --DD
_______________________________________________
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] Re: Feature request, sqlite3_stmt_action

Hick Gunter
IMHO the sqlite3_set_authorizer() interface already does a pretty decent job of providing the requested information:

asql> explain insert into tx7300.vals(keyid,value,sync_offset)
 ...> select k.id,tx.retailer_loc_id,tx.sync_offset from tx7300.keys k, atx_txlog tx where k.name='retailer_loc_id' and tx.period_no = 7300 and retailer_loc_id;

2019-03-28 13:27:27.821: AUTH: T: vals C: (null) D: tx7300 A: (null) P: Insert
2019-03-28 13:27:27.821: AUTH: T: (null) C: (null) D: (null) A: (null) P: Select
2019-03-28 13:27:27.821: AUTH: T: keys C: id D: tx7300 A: (null) P: Read
2019-03-28 13:27:27.821: AUTH: T: atx_txlog C: retailer_loc_id D: main A: (null) P: Read
2019-03-28 13:27:27.821: AUTH: T: atx_txlog C: sync_offset D: main A: (null) P: Read
2019-03-28 13:27:27.821: AUTH: T: keys C: name D: tx7300 A: (null) P: Read
2019-03-28 13:27:27.821: AUTH: T: atx_txlog C: period_no D: main A: (null) P: Read
2019-03-28 13:27:27.821: AUTH: T: atx_txlog C: retailer_loc_id D: main A: (null) P: Read

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Dominique Devienne
Gesendet: Donnerstag, 28. März 2019 11:26
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] Feature request, sqlite3_stmt_action

On Thu, Mar 28, 2019 at 10:59 AM R Smith <[hidden email]> wrote:

> Maybe even, if possible, This query updates these tables: x1, x2, x3...
> etc. (some of which might hide behind an FK relation or Trigger)  but
> I know this is pushing my luck.  :)
>

What I ended-up doing is introspecting the VDBE program of the statement.
It's not exactly easy, and can be brittle too, since the output is not "publicly documented"
so subject to change w/o notice, but I consider this approach less brittle than parsing the SQL.


> Plus, I really do not mind if this explain takes some time, it will be
> faster and more future-proof than any self-parsing one can do.


Right. Some info about the statement from EXPLAIN QUERY PLAN, that DRH would agree to, and accept to "publicly document" and thus support would be nice, good idea.
No an AST of course, but would go a long way already, for those of us that need/wish for that.

I'll put it on my Xmas list :). --DD
_______________________________________________
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] Re: Feature request, sqlite3_stmt_action

Dominique Devienne
On Thu, Mar 28, 2019 at 1:35 PM Hick Gunter <[hidden email]> wrote:

> IMHO the sqlite3_set_authorizer() interface already does a pretty decent
> job of providing the requested information:
>

True, but only if you are fully in control, because authorizer do not
"stack". There's only one, you can't get to restore one a previous one.
Which is logical since used for "security". But when you want do it both
for security, and introspection, and are you are part of a larger
application using SQLite, it makes things more complicated than it should
be. This was NOT designed for statement introspection after all... --DD
_______________________________________________
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: Feature request, sqlite3_stmt_action

Simon Slavin-3
In reply to this post by Dominique Devienne
On 28 Mar 2019, at 10:25am, Dominique Devienne <[hidden email]> wrote:

> Some info about the statement from EXPLAIN QUERY PLAN, that DRH would agree to, and accept to "publicly document" and thus support would be nice,

You want something like

    EXPLAIN EFFECTS OF <statement>

and it should answer with zero or more lines.  Each line contains a single string column.  The strings are things like 'change data', 'change schema', 'change pragma', 'return nothing', 'return table', 'return one row'.

The 'change' results do not mean that anything actually changes, they mean that the command is the kind of command intended to make that change.  For example, an UPDATE command that changes no rows (or perhaps even refers to a table which doesn't exist) still returns 'changes data'.  The 'return' results are similar.  'return table' means the command can return any number of rows, not how many rows it actually will return.

If 'changes pragma' appears, then perhaps another line could say which one, like 'changes pragma journal_mode'.

This would be useful for people writing a SQLite tool, or those with a setup which might involve an injection vulnerability.  Whether it's actually worth building into SQLite I have no idea.

Simon.
_______________________________________________
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: Feature request, sqlite3_stmt_action

siscia
Working with RediSQL another use case comes to mind for some implementation
of the interface we were discussing.

How to detect SELECT statements that return empty.

SQLite simply return SQLITE_DONE in all cases, and it makes impossible to
know if it is an empty SELECT or something else.

A possible solution that I tried was to check if the query executed was
read_only, unfortunately also things like `CREATE TABLE IF NOT EXISTS
foo(a,b);` are read_only if the table do exists.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users