SQLite Forum

Why is json_extract() considered unsafe?
Login

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.