plugin: Allow json_object and json_group_array functions in sql plugin
What the developer wrote
Authored by ShahanaFarooqui
plugin: Allow json_object and json_group_array functions in sql plugin
Changelog-Added: Plugins: `sql` also supports functions `json_object(key1, value1, ...)` to construct JSON objects and `json_group_array(value)` to aggregate rows into JSON array.
Security Considerations
- No new SQL injection risks: Functions only process explicitly provided column values (no arbitrary string parsing).
- Explicit column requirements: Wildcards (*) are not supported, all fields must be named (e.g., json_object('peer_id', id)).
- Permission-bound data access: Functions adhere to the same table/row permissions as the underlying query.
Performance Impact
- Optimized native execution: Leverages SQLite’s built-in JSON1 extension (when available) for efficiency.
- Moderate CPU overhead: Complex nesting may impact performance on large datasets but still faster than application-layer JSON conversion.
What changed, and why it matters
This commit adds two SQLite JSON helper functions, json_object and json_group_array, to the list of functions allowed by Core Lightning's SQL plugin. The change is a small whitelist expansion in the plugin's query authorization code. The commit message explicitly states this does not introduce new SQL injection risks and that the functions follow existing table and row permissions. There is no direct evidence in the commit of a security vulnerability.
No immediate security action required. Review the SQL plugin's authorization model and ensure json_object/json_group_array cannot be combined with other features to bypass permissions or leak data. Consider whether nested JSON construction could enable denial-of-service via large result sets, as noted in the commit's performance considerations.
Security signals we found
Whitelist expansion for SQLite JSON1 functions in SQL plugin authorization callback
Commit message includes explicit security considerations claiming no new SQL injection risk and permission-bound access
No changes to input validation, query parsing, or permission logic in the diff
Evidence from the diff
The patch modifies plugins/sql.c in the sqlite_authorize callback, adding authorization for the SQLite JSON1 functions json_object and json_group_array. These functions construct JSON objects and aggregate rows into JSON arrays, respectively. The authorization callback already whitelists other SQLite functions such as lower, upper, and unixepoch. The change is additive and does not alter query parsing, input handling, or permission enforcement beyond allowing these specific function names to pass authorization.
Changed components
plugins/sql.cCore Lightning SQL pluginSQLite authorization callbackInspect captured patch +4 / −0
diff --git a/plugins/sql.c b/plugins/sql.c
index 9e4ca30..d61452f 100644
--- a/plugins/sql.c
+++ b/plugins/sql.c
@@ -352,6 +352,10 @@ static int sqlite_authorize(void *dbq_, int code,
return SQLITE_OK;
if (streq(b, "unixepoch"))
return SQLITE_OK;
+ if (streq(b, "json_object"))
+ return SQLITE_OK;
+ if (streq(b, "json_group_array"))
+ return SQLITE_OK;
}
/* See https://www.sqlite.org/c3ref/c_alter_table.html to decode these! */
Why this scored 18/100
Community notes
Notes can correct, qualify, or add evidence to the AI analysis. Every note shown here has been validated by a human moderator.
The AI analysis stands alone for now. Submit a note if you can add evidence or important context.