Use of Prisma "unsafe" raw executions in runtime scripts and tests

[AseemPrasad]

Why do you want to contribute?

Use of Prisma "unsafe" raw executions in runtime scripts and tests**

• Files / functions involved

  • index.ts — multiple uses of prisma.$queryRawUnsafe (see index.ts)
  • apps/webapp/test/replicationUtils.ts and other tests use prisma.$executeRawUnsafe (see replicationUtils.ts and tests under test)
  • recover-stuck-runs.ts uses prisma.$queryRaw patterns; other scripts call raw execution.

• Root cause

  • The project uses prisma.$queryRawUnsafe / $executeRawUnsafe in a number of places. While some usages parameterize values correctly, some call sites:
    • Use the "unsafe" variant even when parameterized variants could be used.
  • Tests and scripts using $executeRawUnsafe can execute arbitrary SQL depending on the content passed in or developer error and may run destructive statements (especially in CI or in local environments if environment variables point to real DBs).

• Steps to reproduce

  • In tests or script environment, if environment variables are misconfigured pointing to a production DB and a script uses $executeRawUnsafe with a destructive SQL string, running the script will execute it.
  • Example misconfiguration scenario: running pnpm run db:populate or a dev script while DATABASE_URL points at a non-test DB.

• Expected vs actual

  • Expected: raw SQL is used only when necessary; parameterized and safe APIs are preferred; any raw execution is reviewed and limited to test databases.
  • Actual: $queryRawUnsafe / $executeRawUnsafe are used in multiple places and in tests/scripts. This increases risk of accidental destructive SQL execution if environment variables are mis-set.

• Suggested fix (implementation details)

  • Code hygiene:

    • Replace $queryRawUnsafe / $executeRawUnsafe with parameterized APIs (prisma.$queryRaw + Prisma.sql or use parameter placeholders) wherever possible.
    • If Unsafe is strictly necessary (e.g., dynamically composed multi-statement SQL), wrap with a clear helper that:
      • Validates that process.env.NODE_ENV !== "production" (or the DB connection is a known test DB) before allowing execution.
      • Logs the exact SQL and requires an explicit opt-in flag in environment for dangerous operations.

  • Tests/scripts:

    • Ensure test tasks that call raw SQL assert they are using test DBs (e.g., check DB name or host before executing).
    • Add CI guard: fail if any *.test.ts or scripts use $executeRawUnsafe unless a special tag/comment is present.

  • Add unit/integration tests to assert that dangerous raw functions are not executed against non-test databases (mock Prisma client, or assert that helper throws when DB URL points to production).

  • Add a small lint rule or code-search check in CI to flag any .$queryRawUnsafe / .$executeRawUnsafe usage and require a short rationale comment.

  • Tests exist but do not assert safety checks around Unsafe usage. So tests do not currently protect against accidental destructive execution.

Prior contributions or relevant experience

No response

[AseemPrasad]

i would like to work on this issue so ii want it to be asssigned