{"record":{"id":"bb10aa7b0f6cdbda","repo":"crewAIInc/crewAI","slug":"nl2sqltool-is-configured-in-read-only-mode-and-blo","errorCode":null,"errorMessage":"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.","messagePattern":"NL2SQLTool is configured in read-only mode and blocked a writable CTE containing a '(.+?)' statement\\. To allow write operations set allow_dml=True or CREWAI_NL2SQL_ALLOW_DML=true\\.","errorType":"validation","errorClass":"ValueError","httpStatus":null,"severity":"error","filePath":"lib/crewai-tools/src/crewai_tools/tools/nl2sql/nl2sql_tool.py","lineNumber":336,"sourceCode":"        command = self._extract_command(stmt)\n\n        # EXPLAIN ANALYZE / EXPLAIN ANALYSE actually *executes* the underlying\n        # query.  Resolve the real command so write operations are caught.\n        # parenthesized (\"EXPLAIN (ANALYZE) DELETE …\", \"EXPLAIN (ANALYZE, VERBOSE) DELETE …\").\n        # EXPLAIN ANALYZE actually executes the underlying query — resolve the\n        # real command so write operations are caught.\n        if command == \"EXPLAIN\":\n            resolved = _resolve_explain_command(stmt)\n            if resolved:\n                command = resolved\n\n        # (e.g. WITH d AS (DELETE …) SELECT …) must be blocked in read-only mode.\n        if command == \"WITH\":\n            write_found = _detect_writable_cte(stmt)\n            if write_found:\n                found = write_found\n                if not self.allow_dml:\n                    raise ValueError(\n                        f\"NL2SQLTool is configured in read-only mode and blocked a \"\n                        f\"writable CTE containing a '{found}' statement. To allow \"\n                        f\"write operations set allow_dml=True or \"\n                        f\"CREWAI_NL2SQL_ALLOW_DML=true.\"\n                    )\n                logger.warning(\n                    \"NL2SQLTool: executing writable CTE with '%s' because allow_dml=True.\",\n                    found,\n                )\n                return\n\n            main_query = _extract_main_query_after_cte(stmt)\n            if main_query:\n                main_cmd = main_query.split()[0].upper().rstrip(\";\")\n                if main_cmd in _WRITE_COMMANDS:\n                    if not self.allow_dml:\n                        raise ValueError(\n                            f\"NL2SQLTool is configured in read-only mode and blocked a \"","sourceCodeStart":318,"sourceCodeEnd":354,"githubUrl":"https://github.com/crewAIInc/crewAI/blob/754d7323beb2fd042e33444a115ea2d5a47193f0/lib/crewai-tools/src/crewai_tools/tools/nl2sql/nl2sql_tool.py#L318-L354","documentation":"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.","triggerScenarios":"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'.","commonSituations":"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.","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."],"exampleFix":"# before (blocked)\ntool._run(\"WITH d AS (DELETE FROM users WHERE id=1 RETURNING *) SELECT * FROM d\")\n\n# after (read-only intent)\ntool._run(\"SELECT * FROM users WHERE id=1\")\n# or, if writes are intended:\nNL2SQLTool(db_uri=uri, allow_dml=True)","handlingStrategy":"validation","validationCode":"WRITE_CTE = (\"INSERT\", \"UPDATE\", \"DELETE\", \"MERGE\")\n\ndef cte_is_readonly(sql: str) -> bool:\n    import re\n    for m in re.finditer(r\"AS\\s*\\(\", sql, re.I):\n        rest = sql[m.end():].lstrip().upper()\n        if rest.split()[0].strip(\"()\") in WRITE_CTE if rest.split() else False:\n            return False\n    return True","typeGuard":null,"tryCatchPattern":"try:\n    tool._run(sql)\nexcept ValueError as e:\n    if \"writable CTE\" in str(e) and writes_intended:\n        write_tool._run(sql)\n    else:\n        raise","preventionTips":["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."],"tags":["nl2sql","cte","read-only","security","sql-injection-guard"],"backgroundTag":null,"analyzedSha":"754d7323beb2fd042e33444a115ea2d5a47193f0","analyzedAt":"2026-08-15T04:06:56.746Z","schemaVersion":2},"datasetVersion":"2026-08-15T17:31:12.345Z"}