Resolving json_extract() Safety Errors in SQLite When trusted_schema=0

Unexpected "Unsafe Use of json_extract()" Errors in Views with trusted_schema=0

The core issue arises when executing queries against views containing JSON functions like json_extract() after disabling the trusted_schema pragma in SQLite versions ≥3.37.0. This manifests as a parse error: "unsafe use of json_extract()", despite the function being deterministic and having no obvious security risks. The problem occurs specifically under these conditions:

1. Views or Triggers Using JSON Functions: The error triggers only when JSON functions are embedded in schema objects (views, triggers, CHECK constraints) rather than ad-hoc queries. Direct execution of SELECT json_extract(...) works without issues.

2. trusted_schema=0 Configuration: The SQLite trusted_schema pragma controls whether schema objects can execute potentially unsafe SQL functions. When set to 0 (false), SQLite enforces stricter security checks on function usage within persisted schema definitions.

3. Version-Specific Behavior: SQLite versions prior to 3.37.0 do not exhibit this error because JSON functions were explicitly marked as "innocuous" (safe). Newer versions inadvertently classify them as unsafe due to internal flag misconfigurations.

Example Scenario
A view created with CREATE VIEW test AS SELECT json_extract('{"a":1}', '$.a') works when trusted_schema=1 but fails when the pragma is disabled. This contradicts expectations because json_extract() doesn’t access external resources or exhibit non-deterministic behavior. The error indicates a deeper conflict between SQLite’s security model and function metadata.

Key Technical Background

• trusted_schema Pragmatics: This setting prevents schema objects from executing functions that could introduce vulnerabilities (e.g., load_extension(), random()). Innocuous functions are exempt from these restrictions.
• Function Flags: SQLite categorizes functions using bitmask flags like SQLITE_DETERMINISTIC, SQLITE_INNOCUOUS, and SQLITE_FUNC_UNSAFE. Misconfigured flags alter security evaluations.
• Schema vs. Ad-Hoc Execution: Security checks apply only to precompiled schema objects. Ad-hoc queries bypass these checks because they’re presumed to be contextually safe.

Misconfigured Function Flags in JSON Function Definitions

The root cause lies in incorrect flag assignments to JSON functions during internal code refactoring. SQLite’s JFUNCTION macro in sqliteInt.h erroneously combines mutually exclusive flags, causing JSON functions to be classified as unsafe despite their inherent safety.

Flag Conflict Analysis

• SQLITE_INNOCUOUS vs. SQLITE_FUNC_UNSAFE: These flags share the same numeric value (0x00040000). Using SQLITE_INNOCUOUS in function definitions inadvertently sets the SQLITE_FUNC_UNSAFE bit due to a macro naming collision.
• JFUNCTION Macro Defect: The JSON function generator macro includes SQLITE_INNOCUOUS alongside SQLITE_FUNC_BUILTIN, which is incompatible. Built-in functions should only use SQLITE_FUNC_* flags, not the deprecated SQLITE_INNOCUOUS.

Code-Level Breakdown
The flawed macro definition:

#define JFUNCTION(zName, nArg, iArg, xFunc) \
 {nArg, SQLITE_FUNC_BUILTIN|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS|... }

is interpreted as:

{..., SQLITE_FUNC_BUILTIN | ... | SQLITE_FUNC_UNSAFE | ... }

This marks JSON functions as unsafe. The corrected macro removes SQLITE_INNOCUOUS:

#define JFUNCTION(zName, nArg, iArg, xFunc) \
 {nArg, SQLITE_FUNC_BUILTIN|SQLITE_UTF8|SQLITE_FUNC_CONSTANT, ... }

Version Comparison Evidence

• 3.36.0 Function Flags: JSON functions include deterministic | innocuous in v$function_list.
• 3.40.0 Function Flags: The innocuous flag is absent, leaving only deterministic. However, the underlying flag conflict causes SQLite to misinterpret JSON functions as unsafe.

Security Implications
Misclassifying innocuous functions as unsafe disrupts legitimate use cases. Applications relying on JSON functions in views/triggers become unusable under secure configurations (trusted_schema=0), forcing developers to choose between security and functionality.

Correcting JSON Function Safety Classifications and Workarounds

Official Patch and Version Updates

The SQLite team resolved this in commit 51a5d83c425d2e31 by revising the JFUNCTION macro. Users should:

1. Upgrade to Fixed Versions: SQLite 3.40.1+ includes the corrected function flags.
2. Verify Function Flags: Query v$function_list to confirm JSON functions have the innocuous flag:

   SELECT name, flags FROM pragma_function_list WHERE name LIKE 'json%';
   

   Expected output includes deterministic | innocuous.

Temporary Workarounds for Older Versions

If upgrading isn’t feasible, consider these solutions:

1. Enable trusted_schema Pragmatically
Execute PRAGMA trusted_schema=1; before accessing affected views. This weakens security but restores functionality. Use judiciously in environments where schema objects are trusted.

2. Inline JSON Functions in Queries
Refactor views to move JSON operations into ad-hoc queries:

-- Instead of:
CREATE VIEW v1 AS SELECT json_extract(data, '$.id') FROM tbl;
SELECT * FROM v1;

-- Use:
SELECT json_extract(data, '$.id') FROM tbl;

Ad-hoc queries bypass schema security checks.

3. Recompile SQLite with Corrected Macros
For custom builds, modify sqliteInt.h line 2080 (or equivalent) to remove SQLITE_INNOCUOUS from the JFUNCTION macro:

// Before
#define JFUNCTION(...) SQLITE_INNOCUOUS | ...
// After
#define JFUNCTION(...) ... // Remove SQLITE_INNOCUOUS

Rebuild and redeploy the library.

4. Use SQLITE_DBCONFIG_TRUSTED_SCHEMA
Applications can programmatically enable trusted schema at runtime:

sqlite3_db_config(db, SQLITE_DBCONFIG_TRUSTED_SCHEMA, 1, NULL);

This is equivalent to PRAGMA trusted_schema=1 but offers better integration in programmatic contexts.

5. Migrate JSON Processing to Application Layer
Extract raw JSON strings in views and process them externally:

CREATE VIEW v1 AS SELECT data FROM tbl; -- Store raw JSON

Application code then parses JSON using native libraries (e.g., Python’s json module), avoiding SQLite’s JSON functions entirely.

Preventive Measures for Future Development

• Audit Function Flags: Periodically check v$function_list after SQLite upgrades to detect unexpected changes in function properties.
• Secure Schema Design: Avoid embedding complex functions in views/triggers unless necessary. Use application-layer logic for data transformation.
• Monitor SQLite Changelogs: Track commits related to function safety and macro definitions, especially after version upgrades.

Fossil Repository Integration Notes

While not directly related to the error, contributors should note that SQLite’s forum and code repository are separate Fossil instances. Manual linking between forum threads and code check-ins is required. Developers can reference fixes in forum posts by including commit hashes (e.g., 51a5d83c425d2e31) to improve traceability.

This comprehensive approach addresses immediate errors, provides actionable workarounds, and establishes long-term strategies to prevent recurrence. By understanding the interplay between SQLite’s function classification system and security pragmas, developers can maintain both functionality and security in their applications.