Basic SQL injection not detected in JS files

Product: SonarQube Enterprise 9.5.0.56709
Rule: RSPEC-3649

Multiple SQL injections were found in the application by manual testing, but these were not detected by a Sonar scan.

I cannot share the full source code of the application, but here are the relevant parts for the code flow for the SQL injection in the key GET parameter.

backend/services/database.js

const oracledb = require('oracledb');
const dbConfig = require('../config/db.js');
async function initialize() {
  const pool = await oracledb.createPool(dbConfig.motherBox);
}
 
module.exports.initialize = initialize;
 
async function close() {
    await oracledb.getPool().close();
}
   
module.exports.close = close;
function simpleExecute(statement, binds = [], opts = {}) {
    return new Promise(async (resolve, reject) => {
      let conn;
   
      opts.outFormat = oracledb.OBJECT;
      opts.autoCommit = true;
      oracledb.fetchAsString = [ oracledb.CLOB , oracledb.DATE ];
      
      try {
        conn = await oracledb.getConnection();
   
        const result = await conn.execute(statement, binds, opts);
   
        resolve(result);
      } catch (err) {
        reject(err);
        throw err;
      } finally {
        if (conn) { // conn assignment worked, need to close
          try {
            await conn.close();
          } catch (err) {
            throw err;
          }
        }
      }
    });
  }
   
  module.exports.simpleExecute = simpleExecute;

backend/models/team.js

  // Get Single team details from ID on specific field
  getByKeyword: async function (strSelect = "*", strTeamKey) {
    try {
      var strSQl =
        "SELECT " +
        strSelect +
        " FROM teams WHERE TEAM_KEY = '" +
        strTeamKey +
        "'";
      const result = await database.simpleExecute(strSQl);
      return result.rows;
    } catch (error) {
      console.log(
        "[Error] File: " + fileName + ", Method: " + helper.getFunctionName(),
        error
      );
      throw error;
    }
  },
...

backend/controllers/team.js (controller definition)

// Fetch Team's component from given key
exports.singleComponentFetch = async (req, res, next) => {
  try {
    strTeamKey = req.query.key;
    const result = await Team.getByKeyword("ID", strTeamKey);
    if (result.length == 0) {
      console.log("WRONG Teams Key Supplied " + strTeamKey);
      var arr = {
        message: "Team Key not found",
        component: []
      };
      return res.status(200).json(arr);

package.json

...
"dependencies": {
...
    "jquery": "^3.6.0",
    "oracledb": "^5.2.0",
    "util": "^0.12.4",
...
  },
  "devDependencies": {
 ...
   "@types/node": "^16.9.4",
...
    "ts-node": "10.2.1",
    "tslint": "~6.1.0",
    "typescript": "^4.4.4"
  }

Relevant snippets from the scanner output with sonar.verbose=true:
It seems the scanner couldn’t parse the oracledb dependency, therefore it has no clue that it should be considered a sink for this rule. Did I misconfigure something? The rule is obviously enabled.
What does this line supposed to mean?

(Exclude oracledb) Omitting dependency /usr/src/node_modules/oracledb/index.js since it is not in the list of simulated dependencies = undefined

8:52:43.868 INFO: SonarScanner 4.6.2.2472
18:52:43.870 INFO: Java 11.0.14 Alpine (64-bit)
18:52:43.870 INFO: Linux 5.10.104-linuxkit amd64
18:52:44.035 DEBUG: keyStore is :
18:52:44.035 DEBUG: keyStore type is : pkcs12
18:52:44.035 DEBUG: keyStore provider is :
18:52:44.036 DEBUG: init keystore
18:52:44.036 DEBUG: init keymanager of type SunX509
18:52:44.888 DEBUG: Create: /opt/sonar-scanner/.sonar/cache
18:52:44.889 INFO: User cache: /opt/sonar-scanner/.sonar/cache
18:52:44.889 DEBUG: Create: /opt/sonar-scanner/.sonar/cache/_tmp
18:52:44.893 DEBUG: Extract sonar-scanner-api-batch in temp...
18:52:44.897 DEBUG: Get bootstrap index...
18:52:53.964 INFO: Scanner configuration file: /opt/sonar-scanner/conf/sonar-scanner.properties
18:52:53.965 INFO: Project root configuration file: NONE
18:52:53.966 DEBUG: Execution getVersion
18:52:53.996 INFO: Analyzing on SonarQube server 9.5.0.56709
18:52:53.997 INFO: Default locale: "en_US", source code encoding: "UTF-8" (analysis is platform dependent)
18:52:53.998 DEBUG: Work directory: /usr/src/.scannerwork
18:52:54.001 DEBUG: Execution execute
18:52:54.436 DEBUG: Enterprise 9.5.0.56709
...
18:53:44.023 INFO: Load/download plugins (done) | time=48960ms
18:53:44.606 DEBUG: Plugins:
18:53:44.606 DEBUG:   * Sonar Text Plugin 1.1.6 (communitytext)
18:53:44.606 DEBUG:   * IaC Code Quality and Security 1.7.0.2012 (iac)
18:53:44.607 DEBUG:   * PL/SQL Code Quality and Security 3.7.0.4372 (plsql)
18:53:44.607 DEBUG:   * Scala Code Quality and Security 1.9.0.3429 (sonarscala)
18:53:44.607 DEBUG:   * C# Code Quality and Security 8.40.0.48530 (csharp)
18:53:44.607 DEBUG:   * Vulnerability Analysis 9.5.1.16463 (security)
18:53:44.608 DEBUG:   * Java Code Quality and Security 7.12.0.29739 (java)
18:53:44.608 DEBUG:   * HTML Code Quality and Security 3.6.0.3106 (web)
18:53:44.608 DEBUG:   * Flex Code Quality and Security 2.7.0.2865 (flex)
18:53:44.609 DEBUG:   * XML Code Quality and Security 2.5.0.3376 (xml)
18:53:44.609 DEBUG:   * Text file Code Quality and Security 1.1.0.282 (text)
18:53:44.610 DEBUG:   * VB.NET Code Quality and Security 8.40.0.48530 (vbnet)
18:53:44.610 DEBUG:   * Swift Code Quality and Security 4.6.0.5406 (swift)
18:53:44.610 DEBUG:   * CFamily Code Quality and Security 6.34.0.48468 (cpp)
18:53:44.610 DEBUG:   * Python Code Quality and Security 3.13.0.9611 (python)
18:53:44.610 DEBUG:   * Dataflow Bug Detection 1.2.2.1720 (dbd)
18:53:44.611 DEBUG:   * Go Code Quality and Security 1.9.0.3429 (go)
18:53:44.612 DEBUG:   * JaCoCo 1.1.1.1157 (jacoco)
18:53:44.612 DEBUG:   * Kotlin Code Quality and Security 2.9.0.1147 (kotlin)
18:53:44.612 DEBUG:   * RPG Code Quality 3.2.0.3034 (rpg)
18:53:44.612 DEBUG:   * Dataflow Bug Detection Rules for Java 1.2.2.1720 (dbdjavafrontend)
18:53:44.613 DEBUG:   * PL/I Code Quality and Security 1.11.1.2727 (pli)
18:53:44.613 DEBUG:   * T-SQL Code Quality and Security 1.6.0.4844 (tsql)
18:53:44.613 DEBUG:   * VB6 Code Quality and Security 2.8.0.3021 (vb)
18:53:44.613 DEBUG:   * Apex Code Quality and Security 1.9.0.3429 (sonarapex)
18:53:44.613 DEBUG:   * JavaScript/TypeScript/CSS Code Quality and Security 9.3.0.18033 (javascript)
18:53:44.614 DEBUG:   * Ruby Code Quality and Security 1.9.0.3429 (ruby)
18:53:44.614 DEBUG:   * Vulnerability Rules for C# 9.5.1.16463 (securitycsharpfrontend)
18:53:44.614 DEBUG:   * Vulnerability Rules for Java 9.5.1.16463 (securityjavafrontend)
18:53:44.614 DEBUG:   * Vulnerability Rules for JS 9.5.1.16463 (securityjsfrontend)
18:53:44.614 DEBUG:   * COBOL Code Quality 5.0.0.5433 (cobol)
18:53:44.615 DEBUG:   * Vulnerability Rules for Python 9.5.1.16463 (securitypythonfrontend)
18:53:44.615 DEBUG:   * PHP Code Quality and Security 3.23.1.8766 (php)
18:53:44.615 DEBUG:   * ABAP Code Quality and Security 3.10.0.3628 (abap)
18:53:44.615 DEBUG:   * Configuration detection fot Code Quality and Security 1.2.0.267 (config)
18:53:44.616 DEBUG:   * Vulnerability Rules for PHP 9.5.1.16463 (securityphpfrontend)
18:53:44.720 INFO: Loaded core extensions: developer-scanner
...
18:54:07.718 DEBUG: Average line length for backend/controllers/team.js is 32
18:54:07.719 DEBUG: 'backend/controllers/team.js' indexed with language 'js'
18:54:08.017 DEBUG: Average line length for backend/models/team.js is 34
18:54:08.017 DEBUG: 'backend/models/team.js' indexed with language 'js'
18:54:08.157 DEBUG: Average line length for backend/services/database.js is 22
18:54:08.157 DEBUG: 'backend/services/database.js' indexed with language 'js'
...
18:58:47.850 DEBUG: 'backend/models/team.js' generated metadata with charset 'UTF-8'
18:58:47.874 DEBUG: Resolved '../services/database.js' as '/usr/src/backend/services/database.js'
18:58:47.875 DEBUG: Resolved '../services/database.js' as '/usr/src/backend/services/database.js'
18:58:47.875 DEBUG: ... '../services/database.js' was imported in '/usr/src/backend/models/team.js'
18:58:47.886 DEBUG: Resolved '../lib/helpers' as '/usr/src/backend/lib/helpers.js'
18:58:47.886 DEBUG: Resolved '../lib/helpers' as '/usr/src/backend/lib/helpers.js'
18:58:47.886 DEBUG: ... '../lib/helpers' was imported in '/usr/src/backend/models/team.js'
18:58:48.072 DEBUG: Saving issue for rule no-var on line 8
18:58:48.074 DEBUG: Saving issue for rule default-param-last on line 39
18:58:48.076 DEBUG: Saving issue for rule no-var on line 98
18:58:48.078 DEBUG: Saving issue for rule no-var on line 114
18:58:48.080 DEBUG: Saving issue for rule no-var on line 127
...
18:58:53.591 DEBUG: 'backend/services/database.js' generated metadata with charset 'UTF-8'
18:58:53.615 DEBUG: Resolved 'oracledb' as '/usr/src/node_modules/oracledb/index.js'
18:58:53.615 DEBUG: (Resolved oracledb in node_modules): resolvedName 'oracledb' as '/usr/src/node_modules/oracledb/index.js'
18:58:53.615 DEBUG: (Exclude oracledb) Omitting dependency /usr/src/node_modules/oracledb/index.js since it is not in the list of simulated dependencies = undefined
18:58:53.619 DEBUG: Failed to resolve 'oracledb.ts'
18:58:53.619 DEBUG: Resolved 'oracledb' as 'undefined'
18:58:53.619 DEBUG: ... 'oracledb' was imported in '/usr/src/backend/services/database.js'
18:58:53.622 DEBUG: Resolved '../config/db.js' as '/usr/src/backend/config/db.js'
18:58:53.622 DEBUG: Resolved '../config/db.js' as '/usr/src/backend/config/db.js'
18:58:53.622 DEBUG: ... '../config/db.js' was imported in '/usr/src/backend/services/database.js'
18:58:53.695 DEBUG: Saving issue for rule sonar-no-unused-vars on line 5
18:58:53.699 DEBUG: Saving issue for rule no-dead-store on line 5
18:58:53.701 DEBUG: Saving issue for rule no-useless-catch on line 38
18:58:53.704 DEBUG: Saving issue for rule no-unsafe-finally on line 39
18:58:53.716 DEBUG: 'backend/db/team.js' generated metadata with charset 'UTF-8'
18:58:53.924 DEBUG: Saving issue for rule no-empty-function on line 2
...
18:59:16.530 DEBUG: 'backend/controllers/team.js' generated metadata with charset 'UTF-8'
18:59:16.546 DEBUG: Resolved '../models/team' as '/usr/src/backend/models/team.js'
18:59:16.546 DEBUG: Resolved '../models/team' as '/usr/src/backend/models/team.js'
18:59:16.546 DEBUG: ... '../models/team' was imported in '/usr/src/backend/controllers/team.js'
18:59:16.550 DEBUG: Resolved '../lib/helpers' as '/usr/src/backend/lib/helpers.js'
18:59:16.550 DEBUG: Resolved '../lib/helpers' as '/usr/src/backend/lib/helpers.js'
18:59:16.550 DEBUG: ... '../lib/helpers' was imported in '/usr/src/backend/controllers/team.js'
18:59:16.679 DEBUG: Saving issue for rule no-unused-function-argument on line 5
18:59:16.682 DEBUG: Saving issue for rule no-var on line 11
18:59:16.684 DEBUG: Saving issue for rule no-unused-function-argument on line 31
18:59:16.687 DEBUG: Saving issue for rule no-implicit-global on line 33
18:59:16.689 DEBUG: Saving issue for rule no-var on line 39
18:59:16.691 DEBUG: Saving issue for rule no-unused-function-argument on line 51
18:59:16.694 DEBUG: Saving issue for rule no-var on line 59
18:59:16.698 DEBUG: Saving issue for rule sonar-block-scoped-var on line 59
18:59:16.702 DEBUG: Saving issue for rule no-var on line 67
18:59:16.705 DEBUG: Saving issue for rule no-redeclare on line 67
18:59:16.707 DEBUG: Saving issue for rule prefer-for-of on line 73
18:59:16.710 DEBUG: Saving issue for rule no-var on line 92
18:59:16.712 DEBUG: Saving issue for rule no-redeclare on line 92
...

Since the release notes for SonarQube 9.6 mentioned improved React analysis I run another scan with SonarScanner 4.7.0.2747 on EE SonarQube server 9.6.0.59041, but the vulnerability was not detected again.
The “Resolved …” lines also seem to be missing when executing the scan with the same parameters (-Dsonar.verbose=true -Dsonar.javascript.exclusions=“”).

Can a SonarSourcer take a look at this?

Hello @Adam_B ,

Thanks for your message. I’m going to take a look at this and check what are the limitations of the javascript scanner for this specific example.

For now, I can already see this flow:

  • exports.singleComponentFetch is a function taking the tuple (req, res, next)
  • req.query.key contains a tainted variable
  • The tainted variable gets called by Team.getByKeyword("ID", strTeamKey)
  • strSQl gets tainted and implies an SQL injection
  • Then it gets called by database.simpleExecute
  • And it then gets called by await conn.execute from the oracledb library

After a first glance, I need to check whether the exports.singleComponentFetch function is recognized as a source, and then check if we support oracledb sinks (Your logs seem to imply that oracledb’s sinks are not supported yet).

I will investigate this and come back to you as soon as possible.

Thanks a lot!

Loris

Hi @Loris_Sierra,

Thanks for looking into this. Your description of the flow seems to be correct, but I might missed an important bit. The source is defined as an express route handler:
backend/routes/team.js

const express = require("express");

const TeamController = require("../controllers/team");
const checkAuth = require("../middleware/check-auth");

const router = express.Router();

router.get("/component", checkAuth, TeamController.singleComponentFetch);

module.exports = router;

And the applications entry point:
backend/app.js

const http = require("http");
const express = require("express");
const oracledb = require("oracledb");
const database = require("./services/database.js");
...
// Include Routes
const teamsRoutes = require("./routes/team");
...
const app = express();

app.use(compression());
app.use(express.json());
app.use(express.urlencoded({
  extended: true
}));

const distPath = path.join(__dirname, "dist", "applicationname");
app.use("/", express.static(distPath));
app.disable("x-powered-by");

app.use("/api/team", teamsRoutes);
...
const port = normalizePort(process.env.PORT || "3000");
app.set("port", port);

const server = http.createServer(app);
console.log(helper.getCurrentDateTime() + "[INFO] [SYSTEM] System Started");
server.listen(port);
1 Like

Hi Adam,

Perfect. Thank you very much for this clarification, that was exactly what I was going to ask.
Our scanner should correctly follow this route then.

After investigation, it looks like the issue is simple: We don’t support the node-oracledb lib functions (which looks like it’s this one: node-oracledb 5.4.0 Documentation for the Oracle Database Node.js Add-on | node-oracledb).

I will ensure that we support this and create an internal ticket.

In the meantime, I advise that the code you shared gets fixed using node-oracledb’s Prepared Statements to fix this vulnerability.

Thanks a lot for this forum post. It’s going to help us improve our capabilities! :blush:

Have a good day,

Loris

1 Like

Hey,

For the record: Unfortunately, it’s closed-source, but I created SONARSEC-3263 to report and track this issue internally.

Have a good day!

Loris

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.