{"record":{"id":"18368dbe54bacd57","repo":"knadh/listmonk","slug":"error-getting-tables-from-query-v","errorCode":null,"errorMessage":"error getting tables from query: %v","messagePattern":"error getting tables from query: (.+?)","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"internal/core/subscribers.go","lineNumber":620,"sourceCode":"\n// validateQueryTables checks if the query accesses only allowed tables.\nfunc validateQueryTables(db *sqlx.DB, query string, allowedTables map[string]struct{}, args ...any) error {\n\t// Get the EXPLAIN (FORMAT JSON) output.\n\ttx, err := db.BeginTxx(context.Background(), &sql.TxOptions{ReadOnly: true})\n\tif err != nil {\n\t\treturn err\n\t}\n\tdefer tx.Rollback()\n\n\tvar plan string\n\tif err = tx.QueryRow(\"EXPLAIN (FORMAT JSON) \"+query, args...).Scan(&plan); err != nil {\n\t\treturn err\n\t}\n\n\t// Extract all relation names from the JSON plan.\n\ttables, err := getTablesFromQueryPlan(plan)\n\tif err != nil {\n\t\treturn fmt.Errorf(\"error getting tables from query: %v\", err)\n\t}\n\n\t// Validate against allowed tables.\n\tfor _, table := range tables {\n\t\tif _, ok := allowedTables[table]; !ok {\n\t\t\treturn fmt.Errorf(\"table '%s' is not allowed\", table)\n\t\t}\n\t}\n\n\treturn nil\n}\n\n// getTablesFromQueryPlan parses the EXPLAIN JSON to find all \"Relation Name\" entries.\nfunc getTablesFromQueryPlan(explainJSON string) ([]string, error) {\n\tvar plans []map[string]any\n\tif err := json.Unmarshal([]byte(explainJSON), &plans); err != nil {\n\t\treturn nil, err\n\t}","sourceCodeStart":602,"sourceCodeEnd":638,"githubUrl":"https://github.com/knadh/listmonk/blob/670c01717d48647093335cc23a6be6f4b79c3b6b/internal/core/subscribers.go#L602-L638","documentation":"validateQueryTables runs EXPLAIN-style plan extraction on a custom subscriber query to enumerate every relation it touches. If getTablesFromQueryPlan cannot parse or extract table names from the returned query plan, this wrapped error is returned so QuerySubscribers/ExportSubscribers never run an unvalidatable query.","triggerScenarios":"Calling QuerySubscribers or ExportSubscribers with a SQL query whose plan cannot be parsed by getTablesFromQueryPlan — e.g. malformed SQL that still produced a plan object, unsupported SQL constructs (CTEs, subqueries in unusual positions, dialect-specific syntax) the parser doesn't understand.","commonSituations":"Users pasting complex analytic SQL with CTEs or unions into a custom segment/export query, database upgrades changing EXPLAIN output format so the plan parser no longer matches, or typos in SQL.","solutions":["Simplify the query: avoid CTEs (WITH ...), unions, or exotic syntax; use a plain SELECT ... FROM subscribers with joins to known tables","First fix the underlying SQL so it executes cleanly (test it directly against the DB) — unparseable plans often stem from invalid SQL","Check whether a DB engine version change altered plan output and update getTablesFromQueryPlan's parsing","Verify the query only references allowed tables (subscribers, lists, subscriber_lists) since even a parsed query must pass validation next"],"exampleFix":"// before\nWITH active AS (SELECT id FROM subscribers WHERE status='enabled') SELECT * FROM active\n// after\nSELECT s.* FROM subscribers s WHERE s.status = 'enabled'","handlingStrategy":"validation","validationCode":"// Validate the SQL before submitting it as a segment/export query\nif strings.Contains(strings.ToUpper(q), \"WITH \") || strings.Contains(strings.ToUpper(q), \"UNION\") {\n    return errors.New(\"CTEs and UNIONs are not supported in subscriber queries\")\n}","typeGuard":null,"tryCatchPattern":"if err := validateQueryTables(query); err != nil {\n    if strings.HasPrefix(err.Error(), \"error getting tables from query\") {\n        log.Printf(\"unparseable query plan, simplify SQL: %v\", err)\n    }\n}","preventionTips":["Keep custom subscriber queries to plain SELECT ... FROM subscribers with whitelisted joins","Test the exact SQL against the target DB engine version before saving it","Pin/verify DB version when upgrading — EXPLAIN output format changes can break plan parsing","Avoid CTEs, UNIONs, and dialect-specific syntax in segment queries"],"tags":["sql","query-validation","security","subscribers"],"backgroundTag":"sql-query-plan-parse-failed","analyzedSha":"670c01717d48647093335cc23a6be6f4b79c3b6b","analyzedAt":"2026-09-01T03:39:35.452Z","contentChangedAt":null,"schemaVersion":2},"datasetVersion":"2026-09-08T10:18:20.063Z"}