Text2SQL in RAGFlow#

RAGFlow provides two distinct Text2SQL modes: a chat-assistant SQL path for structured knowledge bases (spreadsheets/Excel) queried via Elasticsearch, Infinity, or OceanBase, and a standalone agent template (ExeSQL) that connects to external relational databases. Both translate natural-language questions into SQL, but they differ in who owns the schema and where the query runs.

Entry Points#

The chat path is triggered inside async_chat(): KnowledgebaseService.get_field_map() checks the KB's parser_config for a field_map dict; if one exists, use_sql() is called first. A successful result (rows returned or an aggregate answer) short-circuits the normal vector-search path 1. On failure or empty results, it falls back to vector retrieval 2.

Field Map: Schema Source of Truth#

When an Excel or CSV file is parsed with the Table parser, rag/app/table.py builds a field_map dict and saves it into the KB's parser_config 3.

Key structure by engine:

Type suffixes (_tks, _flt, _long, _dt, _kwd) map to the ES dynamic field template conventions. Values in the dict are the original spreadsheet column headers (human-readable display names).

KnowledgebaseService.get_field_map(ids) merges the field_map from all KBs in a dialog into a single dict 4 — the merged dict is what use_sql() receives as field_map.

Column names with spaces: Spreadsheet headers are stored as-is in the field_map values (e.g., "Product Name") but the keys are Pinyin-derived alphanumeric identifiers with type suffixes. This means the LLM never needs to quote identifiers with spaces in the SQL it generates for the KB path — it uses the suffix-encoded key, not the original header.

Engine-Specific Prompt Construction#

use_sql() detects the active document engine via settings.DOC_ENGINE_INFINITY / settings.DOC_ENGINE_OCEANBASE and builds a different system prompt + user prompt for each 5.

Elasticsearch / OpenSearch 6:

• Fields are accessed directly by their ES field names (e.g., product_tks, price_flt).
• The user prompt lists available fields with types: - product_tks (text).
• The LLM is instructed to quote fields starting with a digit (e.g., "123_field").
• Non-aggregate SELECTs must include doc_id and docnm_kwd for citation tracking.

Infinity 7:

• Data is stored in a JSON chunk_data column; field access uses json_extract_string(chunk_data, '$.FieldName').
• The table name encodes the KB: ragflow_{tenant_id}_{kb_id} (one table per KB).
• The LLM receives exact (case-sensitive) field names and detailed rules for null checks, numeric casting, and string comparisons (the '"value"' double-quote-in-single-quote pattern).

OceanBase 8: Nearly identical to Infinity, but non-aggregate SELECTs include docnm_kwd instead of docnm.

All three branches call the LLM at temperature=0.06 9 for deterministic output and run normalize_sql() to strip <think> blocks, markdown fences, and trailing semicolons 10.

SQL Execution and ES-Specific Query Rewriting#

Generated SQL goes through settings.retriever.sql_retrieval(sql, format="json"), which calls ESConnectionBase.sql() for the ES/OS backend 11.

Before execution, sql() applies two preprocessing steps:

1. Backtick and whitespace normalization — re.sub(r"[ \]+", " ", sql)` strips backtick-quoted identifiers and collapses spaces 12. This means ES SQL does not support backtick-quoted identifiers with spaces.
2. MATCH rewriting — predicates on *_tks / *_ltks fields (field_tks LIKE 'value' or field_tks = 'value') are rewritten to Elasticsearch full-text MATCH(field, 'tokenized_value', 'operator=OR;minimum_should_match=30%') expressions 13. This is critical: the LLM produces standard SQL equality/LIKE syntax, and the driver converts it to the ES-native MATCH form transparently.

After execution, use_sql() injects a validated kb_id WHERE filter for ES/OS (Infinity uses the table name instead) 14 and builds a Markdown table result, mapping column names back to display names via map_column_name() 15.

Identifier Quoting and Columns with Spaces#

ES SQL does not natively allow unquoted identifiers with spaces. Backticks are stripped pre-flight 12, so identifiers with spaces in the original column name would fail. RAGFlow avoids this problem by design: the LLM is always prompted with the Pinyin+suffix keys (e.g., product_name_tks), not the raw display names. The field_map values (human-readable names) are used only for output rendering, not in the SQL itself.

The ES prompt also explicitly instructs the LLM to quote digit-leading field names with double quotes 16 — the one case where ES SQL requires quoting.

Error Handling and Retry Loop#

use_sql() has a two-attempt retry loop 17:

1. First attempt — Execute the LLM-generated SQL.
2. On failure — Rebuild the user prompt with the error message appended, re-prompt the LLM, and try once more.
3. Double failure — Log an error and return None; async_chat() falls back to vector search.

At the ES layer, ESConnectionBase.sql() catches BadRequestError (HTTP 400 from ES) and logs it at WARNING level rather than ERROR, because this is the expected hot-path for LLM-generated SQL referencing non-existent columns 18. The exception is re-raised so the retry loop in use_sql() can act on it. Real connectivity errors (ConnectionTimeout, generic exceptions) continue to surface at ERROR level 19. This logging behavior was introduced in PR #15709 20.

Missing source-column repair: If the returned table lacks doc_id or docnm_kwd (needed for citations), use_sql() makes one additional re-prompt asking the LLM to add those columns while preserving query intent 21.

Aggregate queries (COUNT, SUM, etc.) are detected via regex 22. For these, the result table is returned as the answer, and a separate SELECT doc_id, docnm_kwd FROM ... WHERE <same_where_clause> is issued to provide source citations 23.

Security Hardening and Agent Mode#

Chat-Path SQL Guards#

• Read-only allowlist — PR #15124 24 enforces that only SELECT statements can reach sql_retrieval, blocking LLM-generated DROP/UPDATE/etc.
• UUID injection guard — kb_id values are validated as canonical UUIDs before interpolation into the WHERE kb_id = '...' filter .
• Match-predicate quote-escape hardening — PR #14852 26 fixed a CWE-89 vulnerability where the regex [^']+ failed to consume SQL-escaped quotes ('') in WHERE clause values, allowing attacker-controlled fragments to bypass the KB isolation filter. The fix updated the pattern to (?:''|[^']+)+ and re-escapes tokenizer output before injecting it into MATCH literals.

Agent Mode: ExeSQL#

The standalone ExeSQL agent tool (agent/tools/exesql.py) connects to external RDBMSs (MySQL, PostgreSQL, MariaDB, MSSQL, IBM DB2, Trino, OceanBase) 27. It uses an SSRF guard to validate the database host before connection 28 and is paired with the multi-stage retrieval agent template in agent/templates/text2sql_data_expert.json 29, which fetches schema DDL, Q&A examples, and field descriptions from three separate KB retrievals before prompting for SQL. The read-only allowlist enforcement for ExeSQL was addressed separately in PR #14877 30.