{"record":{"id":"f8d05b7030359289","repo":"crewAIInc/crewAI","slug":"nl2sqltool-is-configured-in-read-only-mode-and-blo-f8d05b","errorCode":null,"errorMessage":"NL2SQLTool is configured in read-only mode and blocked a '{main_cmd}' statement after a CTE. 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 '(.+?)' statement after a CTE\\. 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":353,"sourceCode":"                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 \"\n                            f\"'{main_cmd}' statement after a CTE. To allow write \"\n                            f\"operations set allow_dml=True or \"\n                            f\"CREWAI_NL2SQL_ALLOW_DML=true.\"\n                        )\n                    logger.warning(\n                        \"NL2SQLTool: executing '%s' after CTE because allow_dml=True.\",\n                        main_cmd,\n                    )\n                elif main_cmd not in _READ_ONLY_COMMANDS:\n                    if not self.allow_dml:\n                        raise ValueError(\n                            f\"NL2SQLTool blocked an unrecognised SQL command '{main_cmd}' \"\n                            f\"after a CTE. Only {sorted(_READ_ONLY_COMMANDS)} are allowed \"\n                            f\"in read-only mode.\"\n                        )\n            return\n","sourceCodeStart":335,"sourceCodeEnd":371,"githubUrl":"https://github.com/crewAIInc/crewAI/blob/754d7323beb2fd042e33444a115ea2d5a47193f0/lib/crewai-tools/src/crewai_tools/tools/nl2sql/nl2sql_tool.py#L335-L371","documentation":"For WITH statements whose CTEs are read-only, NL2SQLTool extracts the main query after the CTE definitions (_extract_main_query_after_cte) and checks its first keyword. If that keyword is in _WRITE_COMMANDS (DELETE, UPDATE, INSERT, ...), read-only mode raises ValueError — e.g. 'WITH x AS (SELECT 1) DELETE FROM users' is a write.","triggerScenarios":"Passing 'WITH cte AS (SELECT ...) DELETE/UPDATE/INSERT/...' with allow_dml=False. LLMs commonly emit 'WITH latest AS (...) UPDATE ...' style statements on Postgres.","commonSituations":"Agent-generated CTE-wrapped mutations; refactoring of write scripts to use CTEs while the tool remains in default read-only mode.","solutions":["If the mutation is intended, opt in: allow_dml=True or CREWAI_NL2SQL_ALLOW_DML=true.","Otherwise strip the write part and keep only read statements.","Tell the agent in the tool description/prompt that only SELECT/SHOW/DESCRIBE/EXPLAIN are allowed."],"exampleFix":"# before (blocked)\ntool._run(\"WITH t AS (SELECT id FROM users) DELETE FROM orders WHERE user_id IN (SELECT id FROM t)\")\n\n# after\nNL2SQLTool(db_uri=uri, allow_dml=True)  # explicit opt-in for writes","handlingStrategy":"validation","validationCode":"WRITE_CMDS = {\"INSERT\", \"UPDATE\", \"DELETE\", \"DROP\", \"ALTER\", \"CREATE\", \"TRUNCATE\", \"MERGE\"}\n\ndef main_cmd_after_cte(sql: str) -> str:\n    import re\n    matches = list(re.finditer(r\"AS\\s*\\(\", sql))\n    if matches:\n        # naive: text after last top-level ')'\n        idx = sql.rfind(\")\")\n        tail = sql[idx+1:].strip()\n        return tail.split()[0].upper() if tail.split() else \"\"\n    return \"\"\n\nif not allow_dml and main_cmd_after_cte(sql) in WRITE_CMDS:\n    raise ValueError(\"write after CTE blocked\")","typeGuard":null,"tryCatchPattern":"try:\n    tool._run(sql)\nexcept ValueError as e:\n    if \"statement after a CTE\" in str(e):\n        route_to_write_tool_or_reject(sql)","preventionTips":["Treat any statement whose first keyword is WITH as potentially mutating — validate before submission.","Give the agent explicit read vs write tools instead of one multipurpose tool."],"tags":["nl2sql","cte","read-only","security","write-operations"],"backgroundTag":null,"analyzedSha":"754d7323beb2fd042e33444a115ea2d5a47193f0","analyzedAt":"2026-08-15T04:06:56.746Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}