Summary
An authenticated SQL injection vulnerability in OpenRemote's datapoint crosstab export endpoint allows crafted asset display names to be concatenated directly into PostgreSQL queries.
When an attacker can create or rename assets, they can inject arbitrary SQL and receive the results in exported CSV responses, enabling targeted database data exfiltration under authenticated conditions.
An authenticated SQL injection vulnerability in OpenRemote's datapoint crosstab export endpoint allows crafted asset display names to be concatenated directly into PostgreSQL queries.
When an attacker can create or rename assets, they can inject arbitrary SQL and receive the results in exported CSV responses, enabling targeted database data exfiltration under authenticated conditions.
Affected context
This vulnerability occurs in OpenRemote deployments that expose the datapoint crosstab export functionality to authenticated users who can create or rename assets.
It is relevant where asset display names are used to dynamically build PostgreSQL queries without sufficient input validation or proper parameterization.
The risk increases in environments where the export endpoint has access to sensitive tables or views and where exported CSVs are automatically consumed or trusted by other systems.
This vulnerability occurs in OpenRemote deployments that expose the datapoint crosstab export functionality to authenticated users who can create or rename assets.
It is relevant where asset display names are used to dynamically build PostgreSQL queries without sufficient input validation or proper parameterization.
The risk increases in environments where the export endpoint has access to sensitive tables or views and where exported CSVs are automatically consumed or trusted by other systems.
Exploit preconditions
- Attack surface: HTTP(S) datapoint crosstab export endpoint in the OpenRemote application
- Required access: Authenticated account with asset creation or rename permissions
- Trigger point: Submission of asset display names containing SQL syntax that is incorporated into dynamically constructed PostgreSQL queries
- Why exploitation is possible: The endpoint concatenates user-controlled asset names directly into raw SQL instead of using parameterized queries or strict input validation, allowing the database to execute attacker-supplied fragments and return results via the CSV export.
- Attack surface: HTTP(S) datapoint crosstab export endpoint in the OpenRemote application
- Required access: Authenticated account with asset creation or rename permissions
- Trigger point: Submission of asset display names containing SQL syntax that is incorporated into dynamically constructed PostgreSQL queries
- Why exploitation is possible: The endpoint concatenates user-controlled asset names directly into raw SQL instead of using parameterized queries or strict input validation, allowing the database to execute attacker-supplied fragments and return results via the CSV export.
Why this happens
The underlying bug class is classic SQL injection as described by CWE-89: Improper Neutralization of Special Elements used in an SQL Command.
Application logic constructs PostgreSQL queries by directly embedding asset display name values into string-based SQL without parameter binding or robust escaping.
This pattern treats user-controllable text as executable query logic rather than data, enabling attackers to alter the structure of the SQL statement, pivot to additional tables, and extract arbitrary records through legitimate query execution.
The underlying bug class is classic SQL injection as described by CWE-89: Improper Neutralization of Special Elements used in an SQL Command.
Application logic constructs PostgreSQL queries by directly embedding asset display name values into string-based SQL without parameter binding or robust escaping.
This pattern treats user-controllable text as executable query logic rather than data, enabling attackers to alter the structure of the SQL statement, pivot to additional tables, and extract arbitrary records through legitimate query execution.
Potential impact
- Attacker may exfiltrate sensitive data from accessible database tables via CSV exports.
- Attacker could manipulate query filters to access records they are not authorized to view.
- Attacker can potentially enumerate database schema, tables, and columns.
- Attacker may influence application behavior that depends on query results, such as reporting or analytics outputs.
- In some configurations, attacker could craft queries that degrade performance or cause denial-of-service conditions at the database layer.
- Attacker may exfiltrate sensitive data from accessible database tables via CSV exports.
- Attacker could manipulate query filters to access records they are not authorized to view.
- Attacker can potentially enumerate database schema, tables, and columns.
- Attacker may influence application behavior that depends on query results, such as reporting or analytics outputs.
- In some configurations, attacker could craft queries that degrade performance or cause denial-of-service conditions at the database layer.
General mitigation guidance
Mitigation focuses on eliminating direct string concatenation in SQL construction and reducing trust in user-controlled asset metadata.
Common defensive patterns include:
- Using parameterized queries or ORM abstractions so that asset display names are always treated as data, not query syntax.
- Applying strict validation and normalization to asset names (e.g., restricting to a safe character set and length).
- Separating query logic from presentation attributes, avoiding use of display names for structural SQL decisions.
- Limiting database privileges of the application account to only necessary tables and operations, reducing impact if injection occurs.
- Implementing defense-in-depth checks at the service layer, such as centralized query builders or stored procedures with fixed structure.
Mitigation focuses on eliminating direct string concatenation in SQL construction and reducing trust in user-controlled asset metadata.
Common defensive patterns include:
- Using parameterized queries or ORM abstractions so that asset display names are always treated as data, not query syntax.
- Applying strict validation and normalization to asset names (e.g., restricting to a safe character set and length).
- Separating query logic from presentation attributes, avoiding use of display names for structural SQL decisions.
- Limiting database privileges of the application account to only necessary tables and operations, reducing impact if injection occurs.
- Implementing defense-in-depth checks at the service layer, such as centralized query builders or stored procedures with fixed structure.
How teams detect issues like this
Detection typically combines code-focused and behavior-focused approaches:
- Static analysis rules that flag dynamic SQL construction involving string concatenation with user-supplied values, particularly in reporting or export endpoints.
- Code review practices that require use of parameterized queries or ORM APIs and prohibit raw SQL that embeds request parameters or asset metadata.
- Logging and monitoring of unusual query patterns, such as unexpected UNIONs, comment markers, or tautological WHERE clauses, originating from the export endpoint.
- Security testing and fuzzing of endpoints with controlled asset names to identify where user input reaches SQL without neutralization.
- Integrating automated SAST and DAST tools into CI pipelines to surface SQL injection patterns before deployment.
Detection typically combines code-focused and behavior-focused approaches:
- Static analysis rules that flag dynamic SQL construction involving string concatenation with user-supplied values, particularly in reporting or export endpoints.
- Code review practices that require use of parameterized queries or ORM APIs and prohibit raw SQL that embeds request parameters or asset metadata.
- Logging and monitoring of unusual query patterns, such as unexpected UNIONs, comment markers, or tautological WHERE clauses, originating from the export endpoint.
- Security testing and fuzzing of endpoints with controlled asset names to identify where user input reaches SQL without neutralization.
- Integrating automated SAST and DAST tools into CI pipelines to surface SQL injection patterns before deployment.
