Re: [sqlite] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration
I totally agree with your answer. But this wasn't really the question. > You have hacked around this security feature I beg you to try to look at my "hacks" with a fresh eye. The service they provide is a genuine one: be able to run raw SQL requests, and also to be notified when one has committed changes in the results of another. I suppose you know that most high-level libraries in GUI platforms embed such database observation features. This is part of the expected tool belt these days. It happens that a security feature has been rerouted for another purpose. This other purpose sheds a new light on authorizers. In GRDB, statements are always "authorized": applications want to manage *their* database, so there is no point restricting access to the database. There is no need for the security side of SQLite authorizer. There is need for the statement inspection features provided by SQLite authorizers (what will be read/written). And prevention of the truncate optimization. Now that I hope I have better explained where I talk from, I hope you will read again my previous question. Thanks in advance, Gwendal Roué ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration
On 8/2/19, Gwendal Roué wrote: > > Do you think this can still be seen as a misuse of the library? > Forcing a reprepare after an authorizer change is a security feature. It helps to prevent people from adding an authorizer, but then mistakenly using a statement that was prepared before the authorizer was added, thinking that the authorizer prevented that statement from leaking sensitive information or harming the database, when it does not. You have hacked around this security feature. As long as you are careful to never use a prepared statement that was created using a lax authorizer, then you will be fine. But if you mess up, and accidentally use a prepared statement with an incorrect authorizer, and that statement leaks information or allows unauthorized changes to the database, then no tears. -- 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] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration
For the record, I have successfully refactored my code so that authorizer is set only once, and the issue has disappeared. Yet, this authorizer not always returns the same value for the same input. Precisely speaking, it always return SQLITE_OK, but when asked if it should allow deletion. In this case, it may return SQLITE_IGNORE or SQLITE_OK during compilation (prepare), and SQLITE_IGNORE or SQLITE_OK during execution (step). All four combinations happen, depending on whether this is a DELETE statement with truncate optimization enabled or disabled, or a DROP statement. Do you think this can still be seen as a misuse of the library? Some insights would be appreciated. Thanks in advance On Thursday, August 1, 2019, Richard Hipp wrote: > On 8/1/19, Gwendal Roué wrote: > > > > 1. set authorizer > > 2. compile statement > > 3. reset authorizer > > 4. step > > 5. set authorizer (and do something else) > > 6. step -> SQLITE_ABORT_ROLLBACK > > Please test to see if changing the "0" to a "1" on the line of code > shown below fixes the problem, and report back. > > https://www.sqlite.org/src/artifact/0fac710388?ln=81 > -- > 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 > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration
After I have read the documentation of sqlite3ExpirePreparedStatements, I better see how the GRDB authorizers dance is a misuse of the library. The goal of GRDB authorizers, which is only to grab information about the statements before they are executed, is at odds with the fact that SQLite connections have a single authorizer dedicated to restricting database accesses. I understand why a change of authorizer invalidates statements. Conclusion: I should use a single authorizer and never change it. This issue report remains interesting, because SQLite exhibits inconsistent behaviors depending of the invalidated statements. But the real fix for "my" issue is to refactor my use of authorizers. On Thu, Aug 1, 2019 at 10:47 PM Gwendal Roué wrote: > For the context, GRDB uses authorizers as a support for its database > observation features: > > - during the compilation of a read statements in order to know what part > of the database would be accessed by the statement. > - during the compilation of other statements in order to know what part of > the database would be modified, or which transaction/savepoint operation > would be executed, or if the database schema would change. > - during the execution of statements for the sole purpose of preventing > the truncate optimization when the library user has expressed the desire of > being notified of row deletions. > > Joined together, all those pieces of observation allow the library user to > say that it wants to track a "database region" (sets of tables, columns, > and rowids), and be notified of any transaction that has committed changes > to this region. This is insanely useful. With full support for raw SQL. > > I'm not sure this is what authorizers were designed for, but... I can't > live without them now :-) > > On Thu, Aug 1, 2019 at 10:26 PM Gwendal Roué > wrote: > >> Yes, Richard, this fixes the problem! Tested with my local copy of SQLite >> 3.28.0. >> >> On Thu, Aug 1, 2019 at 9:23 PM Richard Hipp wrote: >> >>> On 8/1/19, Gwendal Roué wrote: >>> > >>> > 1. set authorizer >>> > 2. compile statement >>> > 3. reset authorizer >>> > 4. step >>> > 5. set authorizer (and do something else) >>> > 6. step -> SQLITE_ABORT_ROLLBACK >>> >>> Please test to see if changing the "0" to a "1" on the line of code >>> shown below fixes the problem, and report back. >>> >>> https://www.sqlite.org/src/artifact/0fac710388?ln=81 >>> -- >>> 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 >>> >> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration
For the context, GRDB uses authorizers as a support for its database observation features: - during the compilation of a read statements in order to know what part of the database would be accessed by the statement. - during the compilation of other statements in order to know what part of the database would be modified, or which transaction/savepoint operation would be executed, or if the database schema would change. - during the execution of statements for the sole purpose of preventing the truncate optimization when the library user has expressed the desire of being notified of row deletions. Joined together, all those pieces of observation allow the library user to say that it wants to track a "database region" (sets of tables, columns, and rowids), and be notified of any transaction that has committed changes to this region. This is insanely useful. With full support for raw SQL. I'm not sure this is what authorizers were designed for, but... I can't live without them now :-) On Thu, Aug 1, 2019 at 10:26 PM Gwendal Roué wrote: > Yes, Richard, this fixes the problem! Tested with my local copy of SQLite > 3.28.0. > > On Thu, Aug 1, 2019 at 9:23 PM Richard Hipp wrote: > >> On 8/1/19, Gwendal Roué wrote: >> > >> > 1. set authorizer >> > 2. compile statement >> > 3. reset authorizer >> > 4. step >> > 5. set authorizer (and do something else) >> > 6. step -> SQLITE_ABORT_ROLLBACK >> >> Please test to see if changing the "0" to a "1" on the line of code >> shown below fixes the problem, and report back. >> >> https://www.sqlite.org/src/artifact/0fac710388?ln=81 >> -- >> 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 >> > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration
Yes, Richard, this fixes the problem! Tested with my local copy of SQLite 3.28.0. On Thu, Aug 1, 2019 at 9:23 PM Richard Hipp wrote: > On 8/1/19, Gwendal Roué wrote: > > > > 1. set authorizer > > 2. compile statement > > 3. reset authorizer > > 4. step > > 5. set authorizer (and do something else) > > 6. step -> SQLITE_ABORT_ROLLBACK > > Please test to see if changing the "0" to a "1" on the line of code > shown below fixes the problem, and report back. > > https://www.sqlite.org/src/artifact/0fac710388?ln=81 > -- > 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 > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration
On 8/1/19, Gwendal Roué wrote: > > 1. set authorizer > 2. compile statement > 3. reset authorizer > 4. step > 5. set authorizer (and do something else) > 6. step -> SQLITE_ABORT_ROLLBACK Please test to see if changing the "0" to a "1" on the line of code shown below fixes the problem, and report back. https://www.sqlite.org/src/artifact/0fac710388?ln=81 -- 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
[sqlite] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration
Hello,
This is an issue report.
SQLite from version 3.8.8 to version 3.24.0 exhibits an issue which
prevents the use of sqlite3_set_authorizer during the iteration of a
statement. The issue does not happen with all statements, but only with
some of them.
It basically goes this way:
1. set authorizer
2. compile statement
3. reset authorizer
4. step
5. set authorizer (and do something else)
6. step -> SQLITE_ABORT_ROLLBACK
Please find below a reproducible test case, reduced as much as I could. It
outputs `code = 516`, when it should not.
A piece of information that may help narrowing the trouble: I could only
trigger the error with the provided query, that involve two tables.
Finally, I post this message after investigation for an issue in the GRDB
Swift library: https://github.com/groue/GRDB.swift/issues/583
Thanks for reading,
Gwendal Roué
#include
#include
int authorize(void* a,int b,const char* c,const char* d,const char* e,const
char* f) {
return SQLITE_OK;
}
int main() {
sqlite3 *connection;
sqlite3_open_v2(":memory:", &connection, SQLITE_OPEN_READWRITE |
SQLITE_OPEN_NOMUTEX, 0);
sqlite3_extended_result_codes(connection, 1);
sqlite3_exec(connection, "CREATE TABLE user (username TEXT NOT NULL)",
0, 0, 0);
sqlite3_exec(connection, "CREATE TABLE flagUser (username TEXT NOT
NULL)", 0, 0, 0);
sqlite3_exec(connection, "INSERT INTO flagUser (username) VALUES
('User1')", 0, 0, 0);
sqlite3_exec(connection, "INSERT INTO flagUser (username) VALUES
('User2')", 0, 0, 0);
sqlite3_stmt *statement;
sqlite3_set_authorizer(connection, authorize, 0);
sqlite3_prepare_v3(connection, "SELECT * FROM flagUser WHERE (SELECT
COUNT(*) FROM user WHERE username = flagUser.username) = 0", -1, 0,
&statement, 0);
sqlite3_set_authorizer(connection, 0, 0);
int code = sqlite3_step(statement);
printf("code = %i\n", code);
sqlite3_set_authorizer(connection, 0, 0);
code = sqlite3_step(statement);
printf("code = %i\n", code);
sqlite3_finalize(statement);
sqlite3_close_v2(connection);
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
