Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration

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

Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration

Gwendal Roué-2
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 <sqlite3.h>
#include <stdio.h>

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

Re: Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration

Richard Hipp-3
On 8/1/19, Gwendal Roué <[hidden email]> 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
[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: Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration

Gwendal Roué-2
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 <[hidden email]> wrote:

> On 8/1/19, Gwendal Roué <[hidden email]> 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
> [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: Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration

Gwendal Roué-2
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é <[hidden email]> 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 <[hidden email]> wrote:
>
>> On 8/1/19, Gwendal Roué <[hidden email]> 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
>> [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: Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration

Gwendal Roué-2
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é <[hidden email]> 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é <[hidden email]>
> 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 <[hidden email]> wrote:
>>
>>> On 8/1/19, Gwendal Roué <[hidden email]> 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
>>> [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: Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration

Gwendal Roué-2
In reply to this post by Richard Hipp-3
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 <[hidden email]> wrote:

> On 8/1/19, Gwendal Roué <[hidden email]> 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
> [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: Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration

Richard Hipp-3
On 8/2/19, Gwendal Roué <[hidden email]> 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
[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: Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration

Gwendal Roué-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users