{"id":"GHSA-cgfv-jrfp-2r7v","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\"\u003casset name\u003e : \u003cattribute name\u003e\" 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 ('\u003casset name\u003e : \u003cattribute name\u003e')) 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\u003ctimestamp\u003e,\u003cdatabase_user\u003e:\u003cdatabase_name\u003e:\u003ctable_count\u003e\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.","aliases":["CVE-2026-62238"],"modified":"2026-07-18T03:56:42.499103992Z","published":"2026-07-06T21:51:32Z","database_specific":{"github_reviewed_at":"2026-07-06T21:51:32Z","nvd_published_at":null,"cwe_ids":["CWE-89"],"severity":"HIGH","github_reviewed":true},"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"}],"affected":[{"package":{"name":"io.openremote:openremote-manager","ecosystem":"Maven","purl":"pkg:maven/io.openremote/openremote-manager"},"ranges":[{"type":"ECOSYSTEM","events":[{"introduced":"0"},{"fixed":"1.26.0"}]}],"versions":["1.10.0","1.11.0","1.11.1","1.11.2","1.11.3","1.12.0","1.12.1","1.12.3","1.13.0","1.13.1","1.14.0","1.15.0","1.15.1","1.15.2","1.16.1","1.17.0","1.17.1","1.17.2","1.17.3","1.18.0","1.19.0","1.2.0","1.2.1","1.2.2","1.2.3","1.2.4","1.20.0","1.20.1","1.20.2","1.21.0","1.22.0","1.22.1","1.23.0","1.23.1","1.24.0","1.24.1","1.24.2","1.25.0","1.3.0","1.3.1","1.3.2","1.3.3","1.3.4","1.3.5","1.4.0","1.5.0","1.6.0","1.6.2","1.6.3","1.6.4","1.6.5","1.7.0","1.8.0","1.8.1","1.9.0"],"database_specific":{"source":"https://github.com/github/advisory-database/blob/main/advisories/github-reviewed/2026/07/GHSA-cgfv-jrfp-2r7v/GHSA-cgfv-jrfp-2r7v.json"}}],"schema_version":"1.9.0","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"}]}