Why is json_extract() considered unsafe?
(1) By Adrian (adrian.s) on 2023-01-08 16:15:50 [source]
Since a recent SQLite version (> 3.37.0) following error occurs when querying a view which includes json_extract() when the trusted_schema pragma is off:
$ sqlite3
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE VIEW test AS SELECT json_extract('{"a": 1}', '$.a');
sqlite> SELECT * FROM test;
1
sqlite> PRAGMA trusted_schema = False;
sqlite> SELECT * FROM test;
Parse error: unsafe use of json_extract()
Is this the supposed behavior?
How can json_extract() be unsafe?
(2) By Keith Medcalf (kmedcalf) on 2023-01-08 18:39:52 in reply to 1 [link] [source]
This is a defect in the definiton of the JFUNCTION macro in SqliteInt.h line 2080 which currently reads:
#define JFUNCTION(zName, nArg, iArg, xFunc) \
{nArg, SQLITE_FUNC_BUILTIN|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS|\
SQLITE_FUNC_CONSTANT|SQLITE_UTF8, \
SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, 0, #zName, {0} }
However, builtin function definitions should contain only the SQLITE_FUNC_* flags. Since SQLITE_INNOCUOUS == SQLITE_FUNC_UNSAFE, all the JSON functions are marked unsafe.
#define JFUNCTION(zName, nArg, iArg, xFunc) \
{nArg, SQLITE_FUNC_BUILTIN|SQLITE_UTF8|SQLITE_FUNC_CONSTANT, \
SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, 0, #zName, {0} }
will fix the issue for all the JSON functions. Presumably this error was introduced when the JSON functions were made internal.
(3) By ddevienne on 2023-01-09 08:58:06 in reply to 1 [link] [source]
In addition to Keith's answer, notice the lack of innocuous from 3.40.1:
PS: See https://sqlite.org/forum/forumpost/d014af10b6 for v$function_list
sqlite> select * from v$function_list where name like 'json%';
┌───────────────────┬─────────┬────────┬───────────┬─────────────────────────┬─────────────────────────┐
│ name │ builtin │ type │ arg-count │ preferred-text-encoding │ flags │
├───────────────────┼─────────┼────────┼───────────┼─────────────────────────┼─────────────────────────┤
│ json │ 1 │ scalar │ 1 │ utf8 │ deterministic │
│ json_array │ 1 │ scalar │ -1 │ utf8 │ deterministic │
│ json_array_length │ 1 │ scalar │ 1 │ utf8 │ deterministic │
│ json_array_length │ 1 │ scalar │ 2 │ utf8 │ deterministic │
│ json_extract │ 1 │ scalar │ -1 │ utf8 │ deterministic │
│ json_group_array │ 1 │ window │ 1 │ utf8 │ deterministic | subtype │
│ json_group_object │ 1 │ window │ 2 │ utf8 │ deterministic | subtype │
│ json_insert │ 1 │ scalar │ -1 │ utf8 │ deterministic │
│ json_object │ 1 │ scalar │ -1 │ utf8 │ deterministic │
│ json_patch │ 1 │ scalar │ 2 │ utf8 │ deterministic │
│ json_quote │ 1 │ scalar │ 1 │ utf8 │ deterministic │
│ json_remove │ 1 │ scalar │ -1 │ utf8 │ deterministic │
│ json_replace │ 1 │ scalar │ -1 │ utf8 │ deterministic │
│ json_set │ 1 │ scalar │ -1 │ utf8 │ deterministic │
│ json_type │ 1 │ scalar │ 1 │ utf8 │ deterministic │
│ json_type │ 1 │ scalar │ 2 │ utf8 │ deterministic │
│ json_valid │ 1 │ scalar │ 1 │ utf8 │ deterministic │
└───────────────────┴─────────┴────────┴───────────┴─────────────────────────┴─────────────────────────┘
While 3.36 (for example) has it:
sqlite> select name, flags from v$function_list where name like 'json%';
┌───────────────────┬─────────────────────────────────────┐
│ name │ flags │
├───────────────────┼─────────────────────────────────────┤
│ json │ deterministic | innocuous │
│ json_array │ deterministic | innocuous │
│ json_array_length │ deterministic | innocuous │
│ json_array_length │ deterministic | innocuous │
│ json_extract │ deterministic | innocuous │
│ json_group_array │ deterministic | subtype | innocuous │
│ json_group_object │ deterministic | subtype | innocuous │
│ json_insert │ deterministic | innocuous │
│ json_object │ deterministic | innocuous │
│ json_patch │ deterministic | innocuous │
│ json_quote │ deterministic | innocuous │
│ json_remove │ deterministic | innocuous │
│ json_replace │ deterministic | innocuous │
│ json_set │ deterministic | innocuous │
│ json_type │ deterministic | innocuous │
│ json_type │ deterministic | innocuous │
│ json_valid │ deterministic | innocuous │
└───────────────────┴─────────────────────────────────────┘
(4) By ddevienne on 2023-01-09 14:24:05 in reply to 1 [link] [source]
@Adrian: Fixed by Richard.
@Richard. Small typo in the fix for OP's issue (tha -> that, line 1937)
PS: Fossil Question: Tickets often reference a Forum Thread (which is great). But could the reverse also be done? I.e. add a link to ticket(s) referencing a particular thread, in the thread UI itself? That way, it's obvious the thread was acted upon / addressed. W/o someone having to poll the timeline, and post a hit.
(5) By Richard Hipp (drh) on 2023-01-09 14:34:47 in reply to 4 [link] [source]
Tickets often reference a Forum Thread (which is great). But could the reverse also be done?
Fossil does that automatically if the ticket/forum is in the same repository as the check-in. But for SQLite, we've chosen to keep the forum in a separate repository from the code. Because the forum is separate, the code repository has no way of knowing that forum repository is referencing it.
It is useful, I suppose for us to manually add links to check-ins that close issues raised in the Forum. Sometimes I do that. Other times I get distracted by the next issue at hand... Thanks for fixing that oversight in your previous post.