CVE-2022-35942: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: Improper input validation on the `contains` LoopBack filter may allow for arbitrary SQL injection. When the extended filter property `contains` is permitted to be interpreted by the Postgres connector, it is possible to inject arbitrary SQL which may affect the confidentiality and integrity of data stored on the connected database. A patch was released in version 5.5.1. This affects users who does any of the following: - Connect to the database via the DataSource with `allowExtendedProperties: true` setting OR - Uses the connector's CRUD methods directly OR - Uses the connector's other methods to interpret the LoopBack filter. Users who are unable to upgrade should do the following if applicable: - Remove `allowExtendedProperties: true` DataSource setting - Add `allowExtendedProperties: false` DataSource setting - When passing directly to the connector functions, manually sanitize the user input for the `contains` LoopBack filter beforehand.
CWE description: The product constructs all or part of an SQL command using externally-influenced input from an upstream component, but it does not neutralize or incorrectly neutralizes special elements that could modify the intended SQL command when it is sent to a downstream component. Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data.
CVSS v3 base score: 10
Published: 2022-08-12T23:15Z
Repository: https://github.com/loopbackio/loopback-connector-postgresql
Language: JavaScript
Files changed: lib/postgresql.js
Commit date: 2022-08-04 19:27:20 +0800
Commit message: fix: improve filter sanitisation

Add sanitisation of user-input for `contains` LoopBack filter which may allow for arbitrary SQL injection.

Signed-off-by: Rifa Achrinza <25147899+achrinza@users.noreply.github.com>

Diff: diff --git a/lib/postgresql.js b/lib/postgresql.js
index 8ba9724..8cb28a7 100644
--- a/lib/postgresql.js
+++ b/lib/postgresql.js
@@ -543,14 +543,15 @@ PostgreSQL.prototype.buildExpression = function(columnName, operator,
 
       const regexOperator = operatorValue.ignoreCase ? ' ~* ?' : ' ~ ?';
       return new ParameterizedSQL(columnName + regexOperator,
         [operatorValue.source]);
     case 'contains':
-      return new ParameterizedSQL(columnName + ' @> array[' + operatorValue.map((v) => `'${v}'`) + ']::'
-        + propertyDefinition.postgresql.dataType);
+      return new ParameterizedSQL(columnName + ' @> array[' + operatorValue.map(() => '?') + ']::'
+        + propertyDefinition.postgresql.dataType,
+        operatorValue);
     case 'match':
-      return new ParameterizedSQL(`to_tsvector(${columnName}) @@ to_tsquery('${operatorValue}')`);
+      return new ParameterizedSQL(`to_tsvector(${columnName}) @@ to_tsquery(?)`, [operatorValue]);
     default:
       // invoke the base implementation of `buildExpression`
       return this.invokeSuper('buildExpression', columnName, operator,
         operatorValue, propertyDefinition);
   }

Vulnerable code:       return new ParameterizedSQL(columnName + ' @> array[' + operatorValue.map((v) => `'${v}'`) + ']::'
        + propertyDefinition.postgresql.dataType);
      return new ParameterizedSQL(`to_tsvector(${columnName}) @@ to_tsquery('${operatorValue}')`);
Fixed code:       return new ParameterizedSQL(columnName + ' @> array[' + operatorValue.map(() => '?') + ']::'
        + propertyDefinition.postgresql.dataType,
        operatorValue);
      return new ParameterizedSQL(`to_tsvector(${columnName}) @@ to_tsquery(?)`, [operatorValue]);