GHSA-cgfv-jrfp-2r7v: Openremote manager SQL Injection Vulnerability

{
  "schema_version" : "1.4.0",
  "id" : "GHSA-cgfv-jrfp-2r7v",
  "modified" : "2026-07-06T21:51:32Z",
  "published" : "2026-07-06T21:51:32Z",
  "aliases" : [ ],
  "summary" : "OpenRemote has Authenticated SQL Injection via Datapoint Crosstab Export",
  "details" : "## Summary\n\nThe datapoint export API builds a PostgreSQL crosstab export query by concatenating asset display names into raw SQL. An authenticated user who can create or rename an asset and then request a crosstab datapoint export can inject SQL through the asset name. The injected query output is streamed back to the caller inside the normal ZIP/CSV export response.\n\nThis creates a practical database exfiltration primitive through the application API. In a multi-tenant deployment, this can expose data outside the attacker's tenant if the application database role can read shared manager tables.\n\n## Affected Component\n\n- Datapoint export endpoint for asset datapoints.\n- Crosstab export formats, specifically CSV crosstab-style exports.\n- Query builder path that constructs a `COPY (SELECT ... FROM crosstab(...)) TO STDOUT` statement.\n\n## Security Impact\n\nImpact is high. A remote authenticated attacker with asset read/write capabilities can:\n\n- Store SQL syntax inside an asset name.\n- Trigger the crosstab export path for that asset.\n- Cause the backend to execute attacker-influenced SQL through the PostgreSQL connection used by the manager service.\n- Receive injected `SELECT` results in the exported CSV contained in the ZIP response.\n\nThe demonstrated impact is database data exfiltration. The proof of concept safely retrieved database execution context and an aggregate table count. A real attacker could adapt the injected `SELECT` to read other database tables accessible to the application database role.\n\nThis is especially sensitive in multi-tenant deployments because application tables commonly contain data for multiple realms/tenants in the same database.\n\n## Attack Preconditions\n\nThe attacker needs:\n\n- A valid authenticated session.\n- Permission to create or rename at least one asset.\n- Permission to read/export datapoints for at least one attribute on that asset.\n- Access to a crosstab datapoint export format.\n\nNo direct database access is required. No server filesystem access is required. No token forgery is required.\n\n## Technical Details\n\nThe export implementation derives a crosstab header from the asset name and attribute name. It then embeds that header into two SQL contexts:\n\n1. A PostgreSQL double-quoted column identifier:\n\n```sql\n\"<asset name> : <attribute name>\" text\n```\n\n2. A category query passed to `crosstab(...)`, wrapped in a fixed dollar-quoted delimiter:\n\n```sql\n$cat$ SELECT header FROM (VALUES ('<asset name> : <attribute name>')) AS t(header) $cat$\n```\n\nThe current escaping is incomplete:\n\n- Single quotes are escaped in one string-literal context.\n- Double quotes in asset names are not escaped before being placed inside quoted identifiers.\n- The fixed dollar-quote delimiter is not protected against an asset name containing the delimiter token.\n\nAs a result, an attacker-controlled asset name can break out of the intended SQL grammar boundary and append SQL to the generated `COPY ... TO STDOUT` query. Because the backend streams `COPY` output into the export response, injected query rows are returned to the attacker as CSV.\n\n## Example Exploit Flow\n\n1. Authenticate normally.\n2. Create or rename an asset using a name containing SQL metacharacters that closes the crosstab column definition.\n3. Ensure the asset has an exportable datapoint attribute.\n4. Write at least one datapoint value for that attribute, if necessary.\n5. Request a CSV crosstab datapoint export for the crafted asset attribute.\n6. Inspect the returned ZIP/CSV. The CSV contains both normal datapoint rows and rows produced by the injected SQL.\n\nA safe proof query demonstrated exfiltration of:\n\n- `current_user`\n- `current_database()`\n- `count(*)` from an application table\n\nThe returned CSV contained a row equivalent to:\n\n```text\n<timestamp>,<database_user>:<database_name>:<table_count>\n```\n\n## Root Cause\n\nThe root cause is manual SQL string construction using user-controlled display data as SQL syntax.\n\nThe asset name is treated as presentation data in the application model, but later reused as part of executable SQL:\n\n- As an SQL identifier in the crosstab output column list.\n- As a value inside a category query string passed to PostgreSQL.\n\nThese contexts require different escaping rules. Applying partial string escaping is error-prone and currently misses exploitable grammar boundaries.\n\n## Recommended Fix\n\nAvoid embedding user-controlled asset names directly into executable SQL.\n\nRecommended options:\n\n1. Do not use asset names as SQL identifiers.\n   - Generate deterministic internal column aliases such as `c1`, `c2`, `c3`.\n   - Keep the user-facing asset/attribute labels outside SQL and apply them only when serializing CSV headers.\n\n2. If dynamic identifiers are unavoidable, quote them using a database-aware identifier quoting function.\n   - For PostgreSQL identifiers, double embedded `\"` characters.\n   - Do not perform ad hoc quoting with string concatenation.\n\n3. Avoid fixed dollar-quote delimiters around attacker-influenced content.\n   - Use prepared statements or server-side functions where possible.\n   - If textual SQL must be generated, choose a delimiter that cannot appear in user input or escape/validate before use.\n\n4. Add a strict validation boundary for display names if the product can tolerate it.\n   - This should be defense-in-depth, not the only fix.\n   - Reject control characters and SQL-significant delimiter sequences in asset names if they are not required.\n\n5. Add regression tests for:\n   - Asset names containing `\"`.\n   - Asset names containing the fixed dollar-quote delimiter.\n   - Asset names containing newline/comment syntax.\n   - Crosstab exports with multiple assets and attributes.\n   - Confirmation that returned CSV never contains injected query output.\n\n## Suggested Safe Design\n\nBuild the crosstab with internal, non-user-controlled category keys and column names. For example:\n\n- Use asset IDs and attribute names only as parameterized data for filtering.\n- Generate internal column identifiers such as `col_0`, `col_1`.\n- Maintain a separate mapping from `col_0` to the display label.\n- Replace the CSV header row after query execution using application-side serialization, not SQL identifiers derived from user input.\n\nThis removes asset display names from SQL syntax entirely.\n\n## Severity\n\nSuggested severity: High\n\nRationale:\n\n- Network reachable through the authenticated API.\n- Low attack complexity after authentication.\n- Requires only ordinary asset read/write/export capabilities.\n- Demonstrated SQL injection result exfiltration through a normal application response.\n- High confidentiality impact due to possible cross-tenant database reads.\n- Integrity and availability impact were not required for the demonstrated exploit and should be assessed separately based on the database role's privileges.",
  "severity" : [ {
    "type" : "CVSS_V4",
    "score" : "CVSS:4.0/AV:N/AC:L/AT:N/PR:L/UI:N/VC:H/VI:L/VA:L/SC:N/SI:N/SA:N"
  } ],
  "affected" : [ {
    "package" : {
      "ecosystem" : "Maven",
      "name" : "io.openremote:openremote-manager"
    },
    "ranges" : [ {
      "type" : "ECOSYSTEM",
      "events" : [ {
        "introduced" : "0"
      }, {
        "fixed" : "1.26.0"
      } ]
    } ]
  } ],
  "references" : [ {
    "type" : "WEB",
    "url" : "https://github.com/openremote/openremote/security/advisories/GHSA-cgfv-jrfp-2r7v"
  }, {
    "type" : "WEB",
    "url" : "https://github.com/openremote/openremote/commit/02ac83074b81617add814b2a72d459abdf374147"
  }, {
    "type" : "PACKAGE",
    "url" : "https://github.com/openremote/openremote"
  } ],
  "database_specific" : {
    "cwe_ids" : [ "CWE-89" ],
    "severity" : "HIGH",
    "github_reviewed" : true,
    "github_reviewed_at" : "2026-07-06T21:51:32Z",
    "nvd_published_at" : null
  }
}