SQL identifier interpolation allows injection / arbitrary SQL execution

[AseemPrasad]

Why do you want to contribute?

SQL identifier interpolation allows injection / arbitrary SQL execution **

• Files / functions involved

  • index.ts — methods: #addJob, #removeJob, #handleCleanup, #handleReporter (see index.ts)
  • Relevant examples: the source builds SQL by interpolating this.graphileWorkerSchema (e.g. SELECT * FROM ${this.graphileWorkerSchema}.add_job(...)) and other queries that interpolate the schema or other identifiers.

• Root cause

  • The code interpolates a schema identifier (value of this.graphileWorkerSchema, from this.#runnerOptions.schema) directly into raw SQL strings passed to prisma.$queryRawUnsafe. While query parameters (e.g. $1) are used for values, the schema/identifier is injected directly into the SQL text without validation or escaping.
  • Identifier interpolation is dangerous because an attacker or misconfiguration that can control runnerOptions.schema (or any code-path that sets these identifiers) can inject arbitrary SQL fragments (e.g., public; DROP TABLE users; --) because it's not escaped as an identifier.

• Steps to reproduce

  1. Create a ZodWorker with runnerOptions.schema set to a malicious value such as public; DROP TABLE "User"; -- (or any value containing SQL control characters).
  2. Call enqueue(...) which triggers #addJob.
  3. The interpolated SQL string will include the malicious identifier and, when executed via prisma.$queryRawUnsafe, can execute appended SQL.

• Expected vs actual

  • Expected: identifiers used in SQL to be validated/escaped or disallowed from user control; only parameterized values go into raw SQL.
  • Actual: identifiers are interpolated verbatim. prisma.$queryRawUnsafe executes the resulting SQL text — making it possible to run arbitrary SQL.

• Suggested fix (implementation details)

  • Sanitize and strictly validate any identifier used in SQL (schema, table, function name). For example:

    • Add utility sanitizeSqlIdentifier(name: string): string that asserts name matches a strict identifier regex (e.g., /^[A-Za-z_][A-Za-z0-9_]*$/) and then returns a safely quoted identifier (double-quote and escape internal quotes).
    • Replace direct interpolation with the sanitized/quoted identifier:
      • Example:
        • const schemaQuoted = sanitizeSqlIdentifier(this.graphileWorkerSchema);
        • await tx.$queryRawUnsafe(SELECT * FROM ${schemaQuoted}.add_job(...), ...);

  • Prefer the safer Prisma helpers where possible:

    • If Prisma supports composing identifiers via Prisma.sql/Prisma.join, use those APIs (but note Prisma doesn't parameterize identifiers — you still must validate identifiers before composing).

  • Add validation at worker instantiation time:

    • If runnerOptions.schema is provided, validate it early and throw if invalid. This fails fast and prevents later injection.

  • Add unit tests that attempt to set an invalid schema and assert the constructor rejects it.

  • The change is isolated: add a sanitizer function + replace interpolations at the call sites in index.ts (4–5 locations).

  • Tests: add a short test that asserts invalid schema values throw, and a positive test that valid identifiers allow normal work.

  • No tests appear to assert identifier validation or defend against injection; existing tests focus on functional behavior, not malicious identifier cases. So coverage is missing.

Prior contributions or relevant experience

No response

[AseemPrasad]

i would like to work on this issue so i want it to be assigned to me..

[matt-aitken]

Technically it's true that this is SQL injectable but the reality is that value is from an environment variable set by whoever hosts Trigger.dev. So it would be someone SQL injecting themselves which is clearly not a real vulnerability.

We will be removing this Graphile Worker code completely very soon, it hasn't been used in production for 12 months.