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

> Discover how Listmonk safely executes subscriber queries using SQL architecture, HTTP filtering, and read-only transactions. Learn about its robust security measures.

- Repository: [Kailash Nadh/listmonk](https://github.com/knadh/listmonk)
- Tags: deep-dive
- Published: 2026-05-19

---

**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`](https://github.com/knadh/listmonk/blob/main/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

```go
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/main/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`](https://github.com/knadh/listmonk/blob/main/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:

```go
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:

```go
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/main/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`:

```go
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:

```go
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`:

```go
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:

```bash
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):

```bash
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

```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`](https://github.com/knadh/listmonk/blob/main/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`](https://github.com/knadh/listmonk/blob/main/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.