Inconsistent behavior in sqlite3_set_authorizer() and error messages

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

Inconsistent behavior in sqlite3_set_authorizer() and error messages

Joshua Thomas Wise
Hello,

I found some behaviors that should probably be considered bugs (and should be fixed and/or documented).

Let’s start the explanation by observing some behavior that actually is correct and consistent. Below, we observe which type of action is reported by sqlite3_set_authorizer(), given some SQL input:

"CREATE TEMP TABLE foo(x)” -----> SQLITE_CREATE_TEMP_TABLE
"CREATE TEMP VIEW foo AS SELECT 1” -----> SQLITE_CREATE_TEMP_VIEW
"CREATE TEMP TRIGGER foo BEFORE INSERT ON t BEGIN SELECT 1; END" -----> SQLITE_CREATE_TEMP_TRIGGER

So far so good. But what happens when we use the “temp.foo” syntax instead of the TEMP keyword?

"CREATE TABLE temp.foo(x)" -----> SQLITE_CREATE_TABLE
"CREATE INDEX temp.foo ON t(x)" -----> SQLITE_CREATE_TEMP_INDEX
"CREATE VIEW temp.foo AS SELECT 1" -----> SQLITE_CREATE_VIEW
"CREATE TRIGGER temp.foo BEFORE INSERT ON t BEGIN SELECT 1; END" -----> SQLITE_CREATE_TEMP_TRIGGER

Uh-oh. We have some inconsistencies there. CREATE TABLE and CREATE VIEW report their regular CREATE_* variants, while CREATE INDEX and CREATE TRIGGER do report their TEMP_* variants.

I recommend that either all or none of those statements should report their TEMP_* variants to the sqlite3_set_authorizer() callback. If the decision is made to not use TEMP_* variants in these cases, the schema name (“temp”, in this case) should ideally be included as an argument to the sqlite3_set_authorizer() callback.

I also found strange inconsistencies regarding error messages. If we execute the 4 statements above, but this time with the SQLITE_OMIT_TEMPDB compile-time option present, we get the following error messages (respectively):

'SQL logic error'
'cannot create a TEMP index on non-TEMP table “t”'
'SQL logic error’
'SQL logic error’

However, if we replace “temp.foo” with “miss.foo” in each of those statements, we get the much better error message:

'unknown database miss’

All of the observations described in this email were very surprising to me. Hopefully they can be fixed and/or documented.

Best regards,

Josh


_______________________________________________
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: Inconsistent behavior in sqlite3_set_authorizer() and error messages

Graham Holden
For the last point, using the SQLITE_OMIT_TEMPDB option, did you compile from the amalgamation or the full cannonical sources? According to https://www.sqlite.org/compile.html#_options_to_omit_features, many "OMIT" options only work when the cannonical sources are used (my guess if compiling the amalgamation is the parser still recognises TEMP as a keyword but there's no code to implement it, hence the "logic error").Graham HoldenSent from my Samsung Galaxy S7 - powered by Three
-------- Original message --------From: Joshua Thomas Wise <[hidden email]> Date: 04/02/2019  03:18  (GMT+00:00) To: [hidden email] Subject: [sqlite] Inconsistent behavior in sqlite3_set_authorizer() and
  error messages Hello,I found some behaviors that should probably be considered bugs (and should be fixed and/or documented).Let’s start the explanation by observing some behavior that actually is correct and consistent. Below, we observe which type of action is reported by sqlite3_set_authorizer(), given some SQL input:"CREATE TEMP TABLE foo(x)” -----> SQLITE_CREATE_TEMP_TABLE"CREATE TEMP VIEW foo AS SELECT 1” -----> SQLITE_CREATE_TEMP_VIEW"CREATE TEMP TRIGGER foo BEFORE INSERT ON t BEGIN SELECT 1; END" -----> SQLITE_CREATE_TEMP_TRIGGERSo far so good. But what happens when we use the “temp.foo” syntax instead of the TEMP keyword?"CREATE TABLE temp.foo(x)" -----> SQLITE_CREATE_TABLE"CREATE INDEX temp.foo ON t(x)" -----> SQLITE_CREATE_TEMP_INDEX"CREATE VIEW temp.foo AS SELECT 1" -----> SQLITE_CREATE_VIEW"CREATE TRIGGER temp.foo BEFORE INSERT ON t BEGIN SELECT 1; END" -----> SQLITE_CREATE_TEMP_TRIGGERUh-oh. We have some inconsistencies there. CREATE TABLE and CREATE VIEW report their regular CREATE_* variants, while CREATE INDEX and CREATE TRIGGER do report their TEMP_* variants.I recommend that either all or none of those statements should report their TEMP_* variants to the sqlite3_set_authorizer() callback. If the decision is made to not use TEMP_* variants in these cases, the schema name (“temp”, in this case) should ideally be included as an argument to the sqlite3_set_authorizer() callback.I also found strange inconsistencies regarding error messages. If we execute the 4 statements above, but this time with the SQLITE_OMIT_TEMPDB compile-time option present, we get the following error messages (respectively):'SQL logic error''cannot create a TEMP index on non-TEMP table “t”''SQL logic error’'SQL logic error’However, if we replace “temp.foo” with “miss.foo” in each of those statements, we get the much better error message:'unknown database miss’All of the observations described in this email were very surprising to me. Hopefully they can be fixed and/or documented.Best regards,Josh_______________________________________________sqlite-users mailing [hidden email]://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] Inconsistent behavior in sqlite3_set_authorizer() and error messages

Hick Gunter
In reply to this post by Joshua Thomas Wise
From https://www.sqlite.org/c3ref/c_alter_table.html

"The 5th parameter to the authorizer callback is the name of the database ("main", "temp", etc.) if applicable."

Is that not the case in your tests?

I guess the difference between "CREATE TEMP TABLE x" and "CREATE TABLE temp.x" ist hat the former already knows that the action is "create a temporary table" before the table name is parsed, whereas the latter has to check the schema name first. Note that you can do "CREATE TEMP TABLE temp.x".

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Joshua Thomas Wise
Gesendet: Montag, 04. Februar 2019 04:19
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] Inconsistent behavior in sqlite3_set_authorizer() and error messages

Hello,

I found some behaviors that should probably be considered bugs (and should be fixed and/or documented).

Let’s start the explanation by observing some behavior that actually is correct and consistent. Below, we observe which type of action is reported by sqlite3_set_authorizer(), given some SQL input:

"CREATE TEMP TABLE foo(x)” -----> SQLITE_CREATE_TEMP_TABLE "CREATE TEMP VIEW foo AS SELECT 1” -----> SQLITE_CREATE_TEMP_VIEW "CREATE TEMP TRIGGER foo BEFORE INSERT ON t BEGIN SELECT 1; END" -----> SQLITE_CREATE_TEMP_TRIGGER

So far so good. But what happens when we use the “temp.foo” syntax instead of the TEMP keyword?

"CREATE TABLE temp.foo(x)" -----> SQLITE_CREATE_TABLE "CREATE INDEX temp.foo ON t(x)" -----> SQLITE_CREATE_TEMP_INDEX "CREATE VIEW temp.foo AS SELECT 1" -----> SQLITE_CREATE_VIEW "CREATE TRIGGER temp.foo BEFORE INSERT ON t BEGIN SELECT 1; END" -----> SQLITE_CREATE_TEMP_TRIGGER

Uh-oh. We have some inconsistencies there. CREATE TABLE and CREATE VIEW report their regular CREATE_* variants, while CREATE INDEX and CREATE TRIGGER do report their TEMP_* variants.

I recommend that either all or none of those statements should report their TEMP_* variants to the sqlite3_set_authorizer() callback. If the decision is made to not use TEMP_* variants in these cases, the schema name (“temp”, in this case) should ideally be included as an argument to the sqlite3_set_authorizer() callback.

I also found strange inconsistencies regarding error messages. If we execute the 4 statements above, but this time with the SQLITE_OMIT_TEMPDB compile-time option present, we get the following error messages (respectively):

'SQL logic error'
'cannot create a TEMP index on non-TEMP table “t”'
'SQL logic error’
'SQL logic error’

However, if we replace “temp.foo” with “miss.foo” in each of those statements, we get the much better error message:

'unknown database miss’

All of the observations described in this email were very surprising to me. Hopefully they can be fixed and/or documented.

Best regards,

Josh


_______________________________________________
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: Inconsistent behavior in sqlite3_set_authorizer() and error messages

Joshua Thomas Wise
In reply to this post by Joshua Thomas Wise

> On Feb 4, 2019, at 7:00 AM, [hidden email] wrote:
>
> For the last point, using the SQLITE_OMIT_TEMPDB option, did you compile from the amalgamation or the full cannonical sources? According to https://www.sqlite.org/compile.html#_options_to_omit_features <https://www.sqlite.org/compile.html#_options_to_omit_features>, many "OMIT" options only work when the cannonical sources are used (my guess if compiling the amalgamation is the parser still recognises TEMP as a keyword but there's no code to implement it, hence the "logic error").


Rest assured, I compiled from the full canonical source. When using SQLITE_OMIT_TEMPDB, the “TEMP” keywords do result in syntax errors (as expected), but “temp.foo” schema names result in those cryptic error messages, rather than the expected "unknown database temp”.

I should also note that when using SQLITE_OMIT_TEMPDB, all four SQL statements previously mentioned will report the their normal variants to the sqlite3_set_authorizer() callback (i.e., not their TEMP_* variants), which is good and desired.
_______________________________________________
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: Inconsistent behavior in sqlite3_set_authorizer() and error messages

Joshua Thomas Wise
In reply to this post by Joshua Thomas Wise
Is the SQLite team aware of these issues?


> On Feb 4, 2019, at 7:00 AM, [hidden email] wrote:
>
> Hello,
>
> I found some behaviors that should probably be considered bugs (and should be fixed and/or documented).
>
> Let’s start the explanation by observing some behavior that actually is correct and consistent. Below, we observe which type of action is reported by sqlite3_set_authorizer(), given some SQL input:
>
> "CREATE TEMP TABLE foo(x)” -----> SQLITE_CREATE_TEMP_TABLE
> "CREATE TEMP VIEW foo AS SELECT 1” -----> SQLITE_CREATE_TEMP_VIEW
> "CREATE TEMP TRIGGER foo BEFORE INSERT ON t BEGIN SELECT 1; END" -----> SQLITE_CREATE_TEMP_TRIGGER
>
> So far so good. But what happens when we use the “temp.foo” syntax instead of the TEMP keyword?
>
> "CREATE TABLE temp.foo(x)" -----> SQLITE_CREATE_TABLE
> "CREATE INDEX temp.foo ON t(x)" -----> SQLITE_CREATE_TEMP_INDEX
> "CREATE VIEW temp.foo AS SELECT 1" -----> SQLITE_CREATE_VIEW
> "CREATE TRIGGER temp.foo BEFORE INSERT ON t BEGIN SELECT 1; END" -----> SQLITE_CREATE_TEMP_TRIGGER
>
> Uh-oh. We have some inconsistencies there. CREATE TABLE and CREATE VIEW report their regular CREATE_* variants, while CREATE INDEX and CREATE TRIGGER do report their TEMP_* variants.
>
> I recommend that either all or none of those statements should report their TEMP_* variants to the sqlite3_set_authorizer() callback. If the decision is made to not use TEMP_* variants in these cases, the schema name (“temp”, in this case) should ideally be included as an argument to the sqlite3_set_authorizer() callback.
>
> I also found strange inconsistencies regarding error messages. If we execute the 4 statements above, but this time with the SQLITE_OMIT_TEMPDB compile-time option present, we get the following error messages (respectively):
>
> 'SQL logic error'
> 'cannot create a TEMP index on non-TEMP table “t”'
> 'SQL logic error’
> 'SQL logic error’
>
> However, if we replace “temp.foo” with “miss.foo” in each of those statements, we get the much better error message:
>
> 'unknown database miss’
>
> All of the observations described in this email were very surprising to me. Hopefully they can be fixed and/or documented.
>
> Best regards,
>
> Josh

_______________________________________________
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: Inconsistent behavior in sqlite3_set_authorizer() and error messages

Joshua Thomas Wise
I’ll interpret the silence as a “no”?


> On Feb 7, 2019, at 11:53 AM, Joshua Thomas Wise <[hidden email]> wrote:
>
> Is the SQLite team aware of these issues?
>
>
>> On Feb 4, 2019, at 7:00 AM, [hidden email] <mailto:[hidden email]> wrote:
>>
>> Hello,
>>
>> I found some behaviors that should probably be considered bugs (and should be fixed and/or documented).
>>
>> Let’s start the explanation by observing some behavior that actually is correct and consistent. Below, we observe which type of action is reported by sqlite3_set_authorizer(), given some SQL input:
>>
>> "CREATE TEMP TABLE foo(x)” -----> SQLITE_CREATE_TEMP_TABLE
>> "CREATE TEMP VIEW foo AS SELECT 1” -----> SQLITE_CREATE_TEMP_VIEW
>> "CREATE TEMP TRIGGER foo BEFORE INSERT ON t BEGIN SELECT 1; END" -----> SQLITE_CREATE_TEMP_TRIGGER
>>
>> So far so good. But what happens when we use the “temp.foo” syntax instead of the TEMP keyword?
>>
>> "CREATE TABLE temp.foo(x)" -----> SQLITE_CREATE_TABLE
>> "CREATE INDEX temp.foo ON t(x)" -----> SQLITE_CREATE_TEMP_INDEX
>> "CREATE VIEW temp.foo AS SELECT 1" -----> SQLITE_CREATE_VIEW
>> "CREATE TRIGGER temp.foo BEFORE INSERT ON t BEGIN SELECT 1; END" -----> SQLITE_CREATE_TEMP_TRIGGER
>>
>> Uh-oh. We have some inconsistencies there. CREATE TABLE and CREATE VIEW report their regular CREATE_* variants, while CREATE INDEX and CREATE TRIGGER do report their TEMP_* variants.
>>
>> I recommend that either all or none of those statements should report their TEMP_* variants to the sqlite3_set_authorizer() callback. If the decision is made to not use TEMP_* variants in these cases, the schema name (“temp”, in this case) should ideally be included as an argument to the sqlite3_set_authorizer() callback.
>>
>> I also found strange inconsistencies regarding error messages. If we execute the 4 statements above, but this time with the SQLITE_OMIT_TEMPDB compile-time option present, we get the following error messages (respectively):
>>
>> 'SQL logic error'
>> 'cannot create a TEMP index on non-TEMP table “t”'
>> 'SQL logic error’
>> 'SQL logic error’
>>
>> However, if we replace “temp.foo” with “miss.foo” in each of those statements, we get the much better error message:
>>
>> 'unknown database miss’
>>
>> All of the observations described in this email were very surprising to me. Hopefully they can be fixed and/or documented.
>>
>> Best regards,
>>
>> Josh
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users