How Subscriber Querying Works in Listmonk: Deep Dive into SQL Architecture and Security

Subscriber querying in listmonk combines HTTP permission filtering, dynamic SQL generation with table whitelisting via EXPLAIN validation, and read-only transactions to safely execute arbitrary SQL expressions against the subscriber database.

Listmonk provides a powerful, flexible system for administrators to search and filter subscribers using both simple search strings and complex SQL-like expressions. According to the knadh/listmonk source code, this process spans multiple layers from HTTP request handling in the command layer down to safety-validated database transactions in the core service.

HTTP Endpoint and Permission Filtering

The entry point for subscriber queries resides in cmd/subscribers.go. The QuerySubscribers HTTP handler first enforces user permissions before touching the database.

The handler performs three critical validation steps:

1. List permission filtering – filterListQueryByPerm extracts the list_id parameter and removes any IDs the caller cannot access
2. SQL query permission check – If the request includes a custom query parameter, the handler verifies the user has the subscribers:sql_query permission
3. Input sanitization – formatSQLExp strips trailing semicolons from arbitrary SQL expressions to prevent statement chaining

func (a *App) QuerySubscribers(c echo.Context) error {
    user := auth.GetUser(c)

    // ① Filter list IDs by the caller's permissions.
    listIDs, err := a.filterListQueryByPerm("list_id", c.QueryParams(), user)
    
    // ② Optional arbitrary SQL expression – must have subscribers:sql_query permission.
    query := formatSQLExp(c.FormValue("query"))
    if query != "" && !user.HasPerm(auth.PermSubscribersSqlQuery) {
        return echo.NewHTTPError(http.StatusForbidden, ...)
    }
    
    // Delegate to core layer...
    res, total, err := a.core.QuerySubscribers(searchStr, query, listIDs,
        subStatus, order, orderBy, pg.Offset, pg.Limit)
    
    // Mask restricted list names before returning
    for i := range res {
        maskRestrictedSubLists(user, &res[i])
    }
    return c.JSON(http.StatusOK, okResp{out})
}

Source: [cmd/subscribers.go](https://github.com/knadh/listmonk/blob/master/cmd/subscribers.go#L98-L146)

Core Query Logic and SQL Safety

The internal/core/subscribers.go file contains the Core.QuerySubscribers method, which orchestrates the actual database interaction. This implementation prioritizes safety when handling user-supplied SQL expressions.

Validating Sort Parameters and List IDs

Before constructing SQL, the core validates ordering parameters against a whitelist:

if !strSliceContains(orderBy, subQuerySortFields) {
    orderBy = "subscribers.id"
}
if order != SortAsc && order != SortDesc {
    order = SortDesc
}

The function also normalizes the listIDs slice to ensure safe array handling with PostgreSQL.

Dynamic SQL Construction

The method replaces placeholder tokens in the SQL template with actual values:

cond := "TRUE"
if queryExp != "" {
    cond = queryExp                // arbitrary expression supplied by the UI
}
stmt := strings.ReplaceAll(c.q.QuerySubscribers, "%query%", cond)
stmt = strings.ReplaceAll(stmt, "%order%", orderBy+" "+order)

Source: [internal/core/subscribers.go](https://github.com/knadh/listmonk/blob/master/internal/core/subscribers.go#L105-L171)

Table Whitelisting via EXPLAIN

The validateQueryTables function provides the critical security layer. It executes EXPLAIN (FORMAT JSON) on the generated query to extract all referenced tables and verify them against allowedSubQueryTables:

func validateQueryTables(db *sqlx.DB, query string,
    allowedTables map[string]struct{}) error {
    
    var plan string
    if err = tx.QueryRow("EXPLAIN (FORMAT JSON) "+query, ...).Scan(&plan); err != nil {
        return err
    }

    tables, err := getTablesFromQueryPlan(plan)
    for _, table := range tables {
        if _, ok := allowedTables[table]; !ok {
            return fmt.Errorf("table '%s' is not allowed", table)
        }
    }
    return nil
}

If the EXPLAIN analysis reveals access to non-whitelisted tables (such as pg_authid or custom tables), the query aborts before execution.

Pagination and Data Retrieval

Counting and Read-Only Transactions

The system uses getSubscriberCount to obtain the total matching rows before fetching the page. Both count and fetch operations execute within read-only transactions:

tx, err := c.db.BeginTxx(context.Background(), &sql.TxOptions{ReadOnly: true})
if err != nil { ... }
defer tx.Rollback()

var out models.Subscribers
if err := tx.Select(&out, stmt,
    pq.Array(listIDs), subStatus, searchStr, offset, limit); err != nil {
    return nil, 0, echo.NewHTTPError(http.StatusInternalServerError, ...)
}

This ensures that even if SQL injection bypassed the EXPLAIN validation, the transaction cannot modify data.

Lazy Loading List Memberships

After fetching the subscriber rows, the system loads each subscriber's list memberships separately via LoadLists:

if err := out.LoadLists(c.q.GetSubscriberListsLazy); err != nil {
    return nil, 0, echo.NewHTTPError(http.StatusInternalServerError, ...)
}

This lazy-loading pattern prevents expensive joins on the initial query while ensuring complete subscriber data for the result set.

Practical Query Examples

Basic Search via API

Retrieve subscribers matching an email pattern with pagination:

curl -X POST http://localhost:9000/api/subscribers \
  -H "Authorization: Bearer <ADMIN_TOKEN>" \
  -d "search=john@example.com" \
  -d "order=desc" \
  -d "order_by=subscribers.created_at" \
  -d "page=1" \
  -d "per_page=20"

Arbitrary SQL Expressions

Execute complex filtering (requires subscribers:sql_query permission):

curl -X POST http://localhost:9000/api/subscribers \
  -H "Authorization: Bearer <ADMIN_TOKEN>" \
  -d "query=subscribers.created_at > now() - interval '30 days' AND subscribers.status='enabled'" \
  -d "order=asc" \
  -d "order_by=subscribers.email"

Using the Core API in Go

import (
    "github.com/knadh/listmonk/internal/core"
    "github.com/knadh/listmonk/models"
)

func fetchRecentSubscribers(c *core.Core) ([]models.Subscriber, int, error) {
    query := "subscribers.created_at > now() - interval '30 days' AND subscribers.status='enabled'"
    return c.QuerySubscribers("", query, nil, "", "desc", "subscribers.created_at", 0, 50)
}

Summary

• Permission filtering occurs at the HTTP layer in cmd/subscribers.go, ensuring users only see subscribers from accessible lists.
• SQL safety relies on validateQueryTables performing EXPLAIN analysis to whitelist only approved tables (subscribers, lists, subscribers_lists).
• Read-only transactions guarantee that even arbitrary SQL expressions cannot modify database state.
• Lazy loading of list memberships via LoadLists optimizes query performance while maintaining data completeness.
• Result masking in the HTTP handler hides list names the caller lacks permission to view, even if the query matches them.

Frequently Asked Questions

How does listmonk prevent SQL injection in subscriber queries?

Listmonk employs a defense-in-depth strategy. First, formatSQLExp strips trailing semicolons to prevent statement chaining. Second, validateQueryTables executes an EXPLAIN (FORMAT JSON) on the generated query to parse all referenced tables and verify them against allowedSubQueryTables, rejecting any query touching unauthorized tables. Finally, the query runs inside a ReadOnly transaction, ensuring no data modification can occur even if other protections fail.

What tables can be accessed in custom subscriber queries?

The system restricts arbitrary queries to a specific whitelist defined in allowedSubQueryTables. Based on the source analysis, this typically includes subscribers, lists, and subscribers_lists. Attempting to reference system tables or other application tables triggers an error before the query executes.

Why are subscriber queries executed in read-only transactions?

The Core.QuerySubscribers method in internal/core/subscribers.go explicitly begins transactions with &sql.TxOptions{ReadOnly: true}. This serves as the final safety net ensuring that even if a malicious or buggy SQL expression bypasses the EXPLAIN validation, the database layer will reject any INSERT, UPDATE, DELETE, or DDL operations, protecting data integrity.

How does pagination work when querying subscribers?

Pagination relies on a two-phase approach. First, getSubscriberCount executes a COUNT(*) query using the same filter conditions to determine the total result set size. Then, the main query applies PostgreSQL OFFSET and LIMIT parameters (passed via pg.Offset and pg.Limit) to return only the requested page. The HTTP handler returns both the paginated results array and the total count for client-side pagination controls.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}