Summary
Twenty CRM contains a critical vulnerability where an unsanitized timeZone parameter in the groupBy REST API endpoint is directly interpolated into a raw SQL expression, enabling SQL injection.
In deployments where the PostgreSQL user has superuser privileges, an authenticated attacker can pivot from SQL injection to executing arbitrary OS commands on the database server via PostgreSQL's COPY TO PROGRAM capability.
Affected context
This vulnerability affects Twenty CRM instances (versions 1.7.7 through 1.16.7) where:
- The REST API groupBy endpoint is exposed to authenticated users.
- The timeZone field is accepted as a query parameter (group_by[timeZone] or equivalent).
- The backend constructs SQL using JavaScript template literals, embedding the timeZone value directly into a raw SQL fragment.
- The connected PostgreSQL database user has elevated privileges, particularly superuser or roles capable of invoking COPY TO PROGRAM.
The vulnerable logic resides in:
- engine/api/graphql/graphql-query-runner/group-by/resolvers/utils/get-group-by-expression.util.ts
Exploit preconditions
- Attack surface: Authenticated REST/GraphQL API calls to the groupBy endpoint.
- Required access:
- Valid authenticated access to the CRM API sufficient to invoke the groupBy operation.
- Network reachability to the Twenty CRM backend (e.g., over HTTPS).
- Trigger point:
- Supplying a crafted timeZone value via the group_by query parameter that alters the intended SQL and introduces attacker-controlled SQL statements.
- Why exploitation is possible:
- The timeZone parameter is concatenated directly into a SQL expression using JavaScript template literals with:
- No parameterization (e.g., prepared statements or bind variables).
- No validation or whitelisting of expected time zone identifiers.
- No escaping or sanitization of special characters.
- PostgreSQL's COPY TO PROGRAM feature can be invoked by superuser-level roles, allowing execution of OS commands when reachable from injected SQL.
- When the database user is a superuser, injected SQL can chain from data access to command execution on the underlying host.
Why this happens
The root cause is the construction of SQL queries by directly interpolating untrusted input into query strings, matching CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection').
Specifically, the timeZone field from the group_by query parameter is:
- Treated as a trusted string and embedded via a JavaScript template literal into a SQL expression.
- Not constrained to a known-safe set of time zone identifiers.
Once SQL injection is possible, the underlying database's advanced functionality (PostgreSQL COPY TO PROGRAM) is misused to cross the boundary from database-level control to OS command execution, corresponding to CWE-78: Improper Neutralization of Special Elements used in an OS Command ('OS Command Injection'). The design assumes database superuser capabilities for the application role, which amplifies the impact of the initial injection.
Potential impact
- Authenticated users may execute arbitrary OS commands on the database server.
- Compromise of the entire PostgreSQL instance, including reading, modifying, or deleting sensitive CRM data.
- Installation of backdoors or malware on the database host via executed commands.
- Lateral movement from the database server to other systems in the same network environment.
- Complete loss of integrity and confidentiality of CRM data and potentially other co-hosted databases.
- Service disruption or denial of service caused by destructive commands or data tampering.
General mitigation guidance
Effective mitigation should focus on both eliminating SQL injection opportunities and reducing the blast radius of database accounts:
- Use parameterized queries and query builders:
- Replace string-concatenated SQL with prepared statements or ORM/query builder APIs where untrusted values are bound as parameters rather than interpolated into SQL text.
- Apply strict input validation:
- Treat timeZone as a controlled field:
- Enforce a whitelist of supported time zone identifiers (e.g., from the application framework or database).
- Reject values containing SQL metacharacters or unexpected syntax.
- Perform contextual encoding/escaping:
- When dynamic SQL fragments are unavoidable, apply appropriate escaping routines for identifiers and literals, and keep dynamic elements as constrained as possible.
- Enforce least privilege for database roles:
- Use a dedicated application database user with only the minimal permissions required for CRM operations.
- Avoid granting superuser or roles that can invoke COPY TO PROGRAM or manage server-level functions to the application account.
- Constrain dangerous database capabilities:
- Limit or disable features such as COPY TO PROGRAM in production where possible, or restrict them to administrative roles inaccessible to the application.
- Adopt secure-by-default coding patterns:
- Standardize on safe database access layers within the codebase, and disallow ad-hoc raw SQL that interpolates user-provided values.
How teams detect issues like this
Detection strategies can address both code-level vulnerabilities and runtime misuse:
- Static analysis and linters:
- Configure static analysis tools to flag:
- Use of string interpolation/template literals in SQL construction.
- Direct concatenation of request parameters (e.g., timeZone, query strings) into SQL statements.
- Add custom rules for JavaScript/TypeScript files (such as get-group-by-expression.util.ts) to detect raw SQL that references input-sourced variables.
- Code review practices:
- Establish review checklists requiring:
- Parameterized queries for all database access paths.
- Evidence of validation for dynamic SQL components such as time zone or grouping expressions.
- Verification that database connections do not use superuser or highly privileged roles.
- Highlight files implementing query builders or resolvers (e.g., GraphQL query runners) as high-risk areas for manual review.
- Database activity monitoring:
- Monitor for:
- Unexpected usage of COPY TO PROGRAM or similar functions.
- SQL statements containing unusual or rarely used commands originating from the application role.
- Configure alerts for commands that interact with the OS layer or file system.
- Policy enforcement in CI/CD:
- Integrate automated checks that fail builds when:
- New instances of raw SQL using template strings are introduced.
- Application database configuration exceeds approved privilege levels.

