crewAIInc/crewAI · error · ValueError

NL2SQLTool is configured in read-only mode and blocked a wri

Error message

NL2SQLTool is configured in read-only mode and blocked a writable CTE containing a '{found}' statement. To allow write operations set allow_dml=True or CREWAI_NL2SQL_ALLOW_DML=true.

What it means

NL2SQLTool inspects WITH (CTE) statements: if a CTE body starts with a write keyword (INSERT/UPDATE/DELETE/MERGE, etc. via _detect_writable_cte), the whole statement is treated as a write even if the outer query is a SELECT. In read-only mode this raises a ValueError naming the offending CTE command.

Source

Thrown at lib/crewai-tools/src/crewai_tools/tools/nl2sql/nl2sql_tool.py:336

        command = self._extract_command(stmt)

        # EXPLAIN ANALYZE / EXPLAIN ANALYSE actually *executes* the underlying
        # query.  Resolve the real command so write operations are caught.
        # parenthesized ("EXPLAIN (ANALYZE) DELETE …", "EXPLAIN (ANALYZE, VERBOSE) DELETE …").
        # EXPLAIN ANALYZE actually executes the underlying query — resolve the
        # real command so write operations are caught.
        if command == "EXPLAIN":
            resolved = _resolve_explain_command(stmt)
            if resolved:
                command = resolved

        # (e.g. WITH d AS (DELETE …) SELECT …) must be blocked in read-only mode.
        if command == "WITH":
            write_found = _detect_writable_cte(stmt)
            if write_found:
                found = write_found
                if not self.allow_dml:
                    raise ValueError(
                        f"NL2SQLTool is configured in read-only mode and blocked a "
                        f"writable CTE containing a '{found}' statement. To allow "
                        f"write operations set allow_dml=True or "
                        f"CREWAI_NL2SQL_ALLOW_DML=true."
                    )
                logger.warning(
                    "NL2SQLTool: executing writable CTE with '%s' because allow_dml=True.",
                    found,
                )
                return

            main_query = _extract_main_query_after_cte(stmt)
            if main_query:
                main_cmd = main_query.split()[0].upper().rstrip(";")
                if main_cmd in _WRITE_COMMANDS:
                    if not self.allow_dml:
                        raise ValueError(
                            f"NL2SQLTool is configured in read-only mode and blocked a "

View on GitHub (pinned to 754d7323be)

Solutions

If the write is intentional and authorized, set allow_dml=True (or CREWAI_NL2SQL_ALLOW_DML=true) on the tool.
Otherwise rewrite the query without the data-modifying CTE — use a plain SELECT for reads.
Fix the agent prompt to state the tool is read-only and CTE writes are blocked.

Example fix

# before (blocked)
tool._run("WITH d AS (DELETE FROM users WHERE id=1 RETURNING *) SELECT * FROM d")

# after (read-only intent)
tool._run("SELECT * FROM users WHERE id=1")
# or, if writes are intended:
NL2SQLTool(db_uri=uri, allow_dml=True)

Defensive patterns

Strategy: validation

Validate before calling

WRITE_CTE = ("INSERT", "UPDATE", "DELETE", "MERGE")

def cte_is_readonly(sql: str) -> bool:
    import re
    for m in re.finditer(r"AS\s*\(", sql, re.I):
        rest = sql[m.end():].lstrip().upper()
        if rest.split()[0].strip("()") in WRITE_CTE if rest.split() else False:
            return False
    return True

Try / catch

try:
    tool._run(sql)
except ValueError as e:
    if "writable CTE" in str(e) and writes_intended:
        write_tool._run(sql)
    else:
        raise

Prevention

State in the agent prompt that data-modifying CTEs are writes and will be blocked.
Keep a separate write-enabled tool instance if writes are ever needed.
Unit-test agent-generated SQL against the read-only validator before execution.

When it happens

Trigger: Running e.g. 'WITH d AS (DELETE FROM users RETURNING *) SELECT * FROM d' with allow_dml=False. Also data-modifying CTEs like 'WITH u AS (UPDATE t SET x=1 RETURNING *) SELECT * FROM u'.

Common situations: Postgres-savvy LLMs generate data-modifying CTEs because they look read-only on the outside; attempts (accidental or deliberate) to bypass the read-only guard by hiding a write inside a CTE.

Related errors

AI-assisted analysis of crewAIInc/crewAI@754d7323be (2026-08-15). Data as JSON: /api/errors/bb10aa7b0f6cdbda. Report an issue: GitHub.