Security advisory GHSA-wrwh-c28m-9jjh: @nocobase/plugin-collection-sql: SQL Validation Bypass Through Missing `checkSQL` Call
Package: @nocobase/plugin-collection-sql (npm), vulnerable version: 2.0.38, fixed in: 2.0.39
CWE: CWE-284
CWE-89
Files changed by the fix:
packages/plugins/@nocobase/plugin-collection-sql/src/server/resources/sql.ts

Fix diff:
diff --git a/packages/plugins/@nocobase/plugin-collection-sql/src/server/__tests__/actions.test.ts b/packages/plugins/@nocobase/plugin-collection-sql/src/server/__tests__/actions.test.ts
index 9fe3046640237..5abe3d3bc8db0 100644
--- a/packages/plugins/@nocobase/plugin-collection-sql/src/server/__tests__/actions.test.ts
+++ b/packages/plugins/@nocobase/plugin-collection-sql/src/server/__tests__/actions.test.ts
@@ -186,6 +186,67 @@ describe('sql collection', () => {
     expect(loadedFields2.size).toBe(1);
   });
 
+  it('sqlCollection:update: should check sql', async () => {
+    await agent.resource('collections').create({
+      values: {
+        name: 'fakeCollection',
+        fields: [
+          {
+            name: 'testField1',
+            type: 'string',
+            interface: 'input',
+          },
+        ],
+      },
+    });
+    await agent.resource('collections').create({
+      values: {
+        name: 'sqlCollection',
+        sql: 'select * from "fakeCollection"',
+        template: 'sql',
+        fields: [
+          {
+            name: 'testField1',
+            type: 'string',
+            interface: 'input',
+          },
+        ],
+      },
+    });
+
+    const collection = await db.getRepository('collections').findOne({
+      filter: {
+        name: 'sqlCollection',
+      },
+    });
+
+    const updateRes = await agent.resource('sqlCollection').update({
+      filterByTk: 'sqlCollection',
+      values: {
+        key: collection.key,
+        sql: "select pg_read_file('/etc/passwd')",
+        name: 'sqlCollection',
+        fields: [
+          {
+            name: 'testField1',
+            type: 'string',
+            interface: 'input',
+          },
+        ],
+      },
+    });
+
+    expect(updateRes.status).toBe(400);
+    expect(updateRes.body.errors[0].message).toMatch('SQL statements contain dangerous keywords');
+
+    const collectionAfterUpdate = await db.getRepository('collections').findOne({
+      filter: {
+        name: 'sqlCollection',
+      },
+    });
+    expect(collectionAfterUpdate.options.sql).toBe('select * from "fakeCollection"');
+  });
+
   it('sqlCollection:setFields', async () => {
     await agent.resource('collections').create({
       values: {
diff --git a/packages/plugins/@nocobase/plugin-collection-sql/src/server/resources/sql.ts b/packages/plugins/@nocobase/plugin-collection-sql/src/server/resources/sql.ts
index b366200724dff..26a824b3b9cbd 100644
--- a/packages/plugins/@nocobase/plugin-collection-sql/src/server/resources/sql.ts
+++ b/packages/plugins/@nocobase/plugin-collection-sql/src/server/resources/sql.ts
@@ -103,6 +103,14 @@ export default {
       await next();
     },
     update: async (ctx: Context, next: Next) => {
+      const { sql } = ctx.action.params.values || {};
+      if (sql) {
+        try {
+          checkSQL(sql);
+        } catch (e) {
+          ctx.throw(400, ctx.t(e.message));
+        }
+      }
       const transaction = await ctx.app.db.sequelize.transaction();
       try {
         const { upRes } = await updateCollection(ctx, transaction);