What changed, and why it matters
This commit is a performance optimization for paginated database searches. It changes how the total row count is calculated when a known table name is provided, avoiding a slower nested subquery. It also adds a small safety check to ensure the provided table name looks like a valid SQL identifier. There is no direct evidence in the commit that this fixes an active security vulnerability.
Treat as a routine performance refactor. Review that all callers pass only literal, hard-coded table names and never user-derived values. Confirm the _valid_sql_name regex matches your naming conventions and that the S608 noqa-marked string formatting remains limited to the validated table_name.
Security signals we found
New SQL identifier validation helper added (_valid_sql_name)
COUNT query construction now uses string formatting with validated table_name (S608 noqa marker present)
No changes to WHERE-clause parameterization or authorization checks
Evidence from the diff
The patch modifies Connection.fetch_page() and Database.fetch_page() in lnbits/db.py to accept an optional table_name parameter. When table_name is supplied, the COUNT(*) query is built directly against that table with the WHERE clause, rather than wrapping the original query in a subquery. The table_name is validated via a new _valid_sql_name() helper using a regex that permits identifiers and schema-qualified identifiers. The existing group_by validation is refactored to use the same helper. Call sites in assets.py, audit.py, payments.py, users.py, and wallets.py now pass the underlying table name. The change is framed as a performance improvement and does not alter access-control logic or query parameterization.
Changed components
lnbits/db.pylnbits/core/crud/assets.pylnbits/core/crud/audit.pylnbits/core/crud/payments.pylnbits/core/crud/users.pylnbits/core/crud/wallets.pyInspect captured patch +43 / −13
diff --git a/lnbits/core/crud/assets.py b/lnbits/core/crud/assets.py
index 5bfa0d3..215ee3a 100644
--- a/lnbits/core/crud/assets.py
+++ b/lnbits/core/crud/assets.py
@@ -90,11 +90,12 @@ async def get_user_assets(
filters = filters or Filters()
filters.sortby = filters.sortby or "created_at"
return await (conn or db).fetch_page(
- query="SELECT * from assets",
+ query="SELECT * FROM assets",
where=["user_id = :user_id"],
values={"user_id": user_id},
filters=filters,
model=AssetInfo,
+ table_name="assets",
)
diff --git a/lnbits/core/crud/audit.py b/lnbits/core/crud/audit.py
index a84f941..f65ada5 100644
--- a/lnbits/core/crud/audit.py
+++ b/lnbits/core/crud/audit.py
@@ -16,11 +16,12 @@ async def get_audit_entries(
conn: Connection | None = None,
) -> Page[AuditEntry]:
return await (conn or db).fetch_page(
- "SELECT * from audit",
+ "SELECT * FROM audit",
[],
{},
filters=filters,
model=AuditEntry,
+ table_name="audit",
)
diff --git a/lnbits/core/crud/payments.py b/lnbits/core/crud/payments.py
index 8204bd4..e11cd03 100644
--- a/lnbits/core/crud/payments.py
+++ b/lnbits/core/crud/payments.py
@@ -181,6 +181,7 @@ async def get_payments_paginated( # noqa: C901
values,
filters=filters,
model=Payment,
+ table_name="apipayments",
)
diff --git a/lnbits/core/crud/users.py b/lnbits/core/crud/users.py
index 6f91423..0620c2a 100644
--- a/lnbits/core/crud/users.py
+++ b/lnbits/core/crud/users.py
@@ -89,6 +89,7 @@ async def get_accounts(
filters=filters,
model=AccountOverview,
group_by=["accounts.id"],
+ table_name="accounts",
)
diff --git a/lnbits/core/crud/wallets.py b/lnbits/core/crud/wallets.py
index 67d63ea..b13d83c 100644
--- a/lnbits/core/crud/wallets.py
+++ b/lnbits/core/crud/wallets.py
@@ -187,6 +187,7 @@ async def get_wallets_paginated(
values={"user": user_id, "deleted": deleted},
filters=filters,
model=Wallet,
+ table_name="wallets",
)
wallets.data = await get_source_wallets(wallets.data, conn)
diff --git a/lnbits/db.py b/lnbits/db.py
index f598124..0e10619 100644
--- a/lnbits/db.py
+++ b/lnbits/db.py
@@ -222,7 +222,22 @@ class Connection(Compat):
filters: Filters | None = None,
model: type[TModel] | None = None,
group_by: list[str] | None = None,
+ table_name: str | None = None,
) -> Page[TModel]:
+ """
+ Parameters:
+ query: The main SQL query string to execute for data retrieval.
+ where: list of additional WHERE clause conditions to filter results.
+ values: dictionary of parameter values to be used in the SQL query.
+ filters: object for advanced filtering, sorting, and pagination logic.
+ model: pydantic model type to map query results into model instances.
+ group_by: list of column names to group results by in the SQL query.
+ table_name: if provided some optimisations can be applied.
+ """
+
+ if table_name and not _valid_sql_name(table_name):
+ raise ValueError(f"Invalid table name: '{table_name}'.")
+
if not filters:
filters = Filters()
clause = filters.where(where)
@@ -231,9 +246,7 @@ class Connection(Compat):
group_by_string = ""
if group_by:
for field in group_by:
- if not re.fullmatch(
- r"[a-zA-Z_][a-zA-Z0-9_]*(\.[a-zA-Z_][a-zA-Z0-9_]*)?", field
- ):
+ if not _valid_sql_name(field):
raise ValueError("Value for GROUP BY is invalid")
group_by_string = f"GROUP BY {', '.join(group_by)}"
@@ -251,16 +264,17 @@ class Connection(Compat):
if rows:
# no need for extra query if no pagination is specified
if filters.offset or filters.limit:
- result = await self.execute(
- f"""
- SELECT COUNT(*) as count FROM (
+ if table_name:
+ count_query = f"SELECT COUNT(*) as count FROM {table_name} {clause}" # noqa: S608
+ else:
+ count_query = f"""SELECT COUNT(*) as count
+ FROM (
{query}
{clause}
{group_by_string}
- ) as count
- """, # noqa: S608
- parsed_values,
- )
+ ) as count""" # noqa: S608
+
+ result = await self.execute(count_query, parsed_values)
row = result.mappings().first()
result.close()
count = int(row.get("count", 0))
@@ -393,9 +407,12 @@ class Database(Compat):
filters: Filters | None = None,
model: type[TModel] | None = None,
group_by: list[str] | None = None,
+ table_name: str | None = None,
) -> Page[TModel]:
async with self.connect() as conn:
- return await conn.fetch_page(query, where, values, filters, model, group_by)
+ return await conn.fetch_page(
+ query, where, values, filters, model, group_by, table_name
+ )
async def execute(self, query: str, values: dict | None = None):
async with self.connect() as conn:
@@ -750,3 +767,11 @@ def _safe_load_json(value: str) -> dict:
# DB is corrupted if it gets here
logger.error(f"Failed to decode JSON: '{value}'")
return {}
+
+
+def _valid_sql_name(name: str) -> bool:
+ """Check if a SQL name is valid (alphanumeric and underscores only)"""
+ return (
+ re.fullmatch(r"[a-zA-Z_][a-zA-Z0-9_]*(\.[a-zA-Z_][a-zA-Z0-9_]*)?", name)
+ is not None
+ )
Why this scored 19/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.