GHSA-cgfv-jrfp-2r7v: Authenticated SQL Injection in OpenRemote Datapoint Crosstab Export

An authenticated SQL injection vulnerability has been identified in the open-source Internet of Things (IoT) platform, OpenRemote. The vulnerability is tracked under the identifier GHSA-cgfv-jrfp-2r7v and carries a high severity CVSS v4.0 score of 8.5.

The flaw resides in the datapoint crosstab export functionality of the OpenRemote Manager core. This component handles database query preparation to allow users to pull history tables in a matrix layout. Under standard configurations, the application uses PostgreSQL's dynamic crosstab functionality to transform temporal event records into structured columnar reports.

Because the application handles dynamic user inputs as raw strings during the creation of PostgreSQL column structures, an attacker with authorization to create or modify assets can insert malicious commands. This bypasses structural barriers, exposing the raw PostgreSQL instance directly to the authenticated session.

The root cause of the vulnerability lies in the manual composition of database commands within the AssetDatapointService component. When generating a CSV crosstab report, the server compiles the metadata columns dynamically. It does so by pulling the names of requested assets and attributes and placing them directly into SQL statements using string concatenation.

To construct PostgreSQL identifiers, the application wraps headers with double-quote characters. However, the input values undergo no neutralization or escaping of double-quotes prior to query generation. Because double quotes are SQL metacharacters used to delimit identifiers, a double quote within an asset or attribute name terminates the identifier context.

Additionally, the query construction embeds categorical parameters inside fixed dollar-quoted literal strings ($cat$). Since dollar-quotes serve as literal boundaries, adding the specific $cat$ delimiter inside the user-controlled input breaks out of the string literal scope. This allows the attacker to inject arbitrary commands, such as appending secondary operations or accessing tables outside their tenancy.

The vulnerable version of the source code builds the dynamic SQL query by concatenating the headers stream directly into SQL templates. In manager/src/main/java/org/openremote/manager/datapoint/AssetDatapointService.java, the query composition was implemented as follows:

// Vulnerable dynamically-constructed SQL columns
String attributeColumns = headers.stream()
        .map(header -> "\"" + header + "\" text")
        .collect(Collectors.joining(", "));
 
String categoryValues = headers.stream()
        .map(header -> "('" + header.replace("'", "''") + "')")
        .collect(Collectors.joining(", "));
String categoryQuery = "SELECT header FROM (VALUES " + categoryValues + ") AS t(header)";
 
return String.format(
        "copy (select * from crosstab('%s', $cat$%s$cat$) as ct(timestamp timestamp, %s)%s",
        innerQuery, categoryQuery, attributeColumns, TO_STDOUT_WITH_CSV_FORMAT);

The refactored implementation completely decouples user-supplied display names from the SQL engine context. Instead of interpolating user-controlled names as SQL structure names, the patch dynamically assigns safe, synthetic column identifiers on the server, such as col_0, col_1, etc.

// Secure version mapping user assets to synthetic column identifiers
List<ExportColumn> exportColumns = getExportColumns(attributeRefs);
 
try (PreparedStatement insertStatement = connection.prepareStatement(
    "insert into " + tempTableName + " (ordinal, entity_id, attribute_name, column_key) values (?, ?, ?, ?)"
)) {
    for (ExportColumn exportColumn : exportColumns) {
        AttributeRef attributeRef = exportColumn.attributeRef();
        insertStatement.setInt(1, exportColumn.ordinal());
        insertStatement.setString(2, validateAssetId(attributeRef.getId()));
        insertStatement.setString(3, attributeRef.getName());
        insertStatement.setString(4, exportColumn.columnKey());
        insertStatement.addBatch();
    }
    insertStatement.executeBatch();
}

The actual CSV header row containing user-friendly asset names is compiled directly in the application's Java memory space, rather than inside the database execution context. The application streams the headers separately using a clean, safe string formatter before copy operations proceed.

Exploitation of GHSA-cgfv-jrfp-2r7v requires a valid authenticated session with privileges to modify or create assets in the current tenant space. The attacker must prepare a breakout sequence using the specific formatting delimiters found in the vulnerable query.

To construct an exploit payload, the attacker must target either the double-quoted column definition or the dollar-quoted categorization queries. For instance, inserting an asset name containing double quotes allows the attacker to close the column context and append dynamic subqueries. The following conceptual structure demonstrates the injection breakout:

SQL \"delimiter\" $cat$ -- label

When the user triggers a datapoint export, the backend processes the crafted asset name, causing the SQL parser to close the string literal at the injected dollar-quotes or double-quotes, and execute arbitrary nested statements. The application streams the returned records directly to the output stream as a ZIP file containing the resulting CSV file, enabling a silent data exfiltration pipeline.

The security impact of this vulnerability is classified as high. An attacker can leverage the SQL injection vector to bypass tenant barriers in multi-tenant OpenRemote environments. This allows database querying across multiple system schemas.

Because the database connection executes within the application's security context, the attacker can extract sensitive metadata, structural information, and arbitrary tables stored in the Shared PostgreSQL database. This includes Keycloak configurations, encrypted system credentials, and records associated with other tenants.

Furthermore, because the query output is streamed directly to the attacker as a valid CSV, the exfiltration rate is high, requiring very little computational effort from the attacker. No escalation to host-level command execution has been demonstrated, but complete read access to the database layer must be assumed.

The primary remediation path is upgrading the OpenRemote platform deployment to version 1.26.0 or higher. This release contains the complete refactoring of the export mechanism, which eliminates dynamic string concatenation from the SQL processing path.

If patching is not immediately feasible, system administrators can apply several temporary workarounds to lower the attack surface. First, restrict asset modification and creation privileges to highly trusted users. This prevents malicious asset renaming.

Additionally, deploy Web Application Firewall rules to audit incoming asset modification requests for SQL indicators. Monitor the database execution logs for query syntax failures, runtime errors, or occurrences of the crosstab function utilizing unexpected schema terms.