javascript.lang.security.audit.sqli.node-mssql-sqli.node-mssql-sqli

profile photo of semgrepsemgrep
Author
137
Download Count*
LGPL Commons Clause
License

Detected string concatenation with a non-literal variable in a mssql JS SQL statement. This could lead to SQL injection if the variable is user-controlled and not properly sanitized. In order to prevent SQL injection, use parameterized queries or prepared statements instead. You can use parameterized statements like so: $REQ.input('USER_ID', mssql.Int, id);

Run Locally

Run in CI

Semgrep CI docs

Defintion

Edit RuleView Source
Open in Playground 
rules:
  - id: node-mssql-sqli
    message: "Detected string concatenation with a non-literal variable in a `mssql`
      JS SQL statement. This could lead to SQL injection if the variable is
      user-controlled and not properly sanitized. In order to prevent SQL
      injection, use parameterized queries or prepared statements instead. You
      can use parameterized statements like so: `$REQ.input('USER_ID',
      mssql.Int, id);`"
    metadata:
      owasp:
        - A01:2017 - Injection
        - A03:2021 - Injection
      cwe:
        - "CWE-89: Improper Neutralization of Special Elements used in an SQL
          Command ('SQL Injection')"
      category: security
      technology:
        - mssql
      license: Commons Clause License Condition v1.0[LGPL-2.1-only]
      references:
        - https://www.npmjs.com/package/mssql
      cwe2022-top25: true
      cwe2021-top25: true
      subcategory:
        - vuln
      likelihood: HIGH
      impact: MEDIUM
      confidence: LOW
      vulnerability_class:
        - SQL Injection
    languages:
      - javascript
      - typescript
    severity: WARNING
    mode: taint
    pattern-sources:
      - patterns:
          - pattern-inside: |
              function ... (...,$FUNC,...) {
                ...
              }
          - focus-metavariable: $FUNC
    pattern-sinks:
      - patterns:
          - pattern-either:
              - pattern-inside: |
                  require('mssql');
                  ...
              - pattern-inside: |
                  import 'mssql';
                  ...
          - pattern-inside: |
              $REQ = $POOL.request(...)
              ...
          - pattern: |
              $REQ.query($QUERY,...)
          - focus-metavariable: $QUERY

Examples

node-mssql-sqli.js

const express = require('express');
const mssql = require('mssql');

(async () => {
  let pool;
  try {
    pool = await new mssql.ConnectionPool({
      user: process.env?.MSSQL_USERNAME ?? '',
      password: process.env?.MSSQL_PASSWORD ?? '',
      server: 'mssql',
      port: 1433,
      database: 'foobar',
      options: {
        trustServerCertificate: true,
        enableArithAbort: true,
      },
    });
  } catch (err) {
    console.log('ERROR: creating new pool SQL error', err.message, err);
    process.exit(1);
  }
  try {
    await pool.connect();
  } catch (err) {
    console.log('ERROR: connecting pool SQL error', err.message, err);
    pool.close();
    process.exit(1);
  }

  const app = express();

  app.get('/', (req, res) => {
    res.send('It Works!');
  });

  app.get('/get-a-user-by-id', async (req, res) => {
    const { id } = req.query;
    console.log('get-a-user-by-id id: ', id);
    let results;
    try {
      const request = pool.request();
      // ruleid: node-mssql-sqli
      const dbResult = await request.query(`SELECT * FROM [foobar].[dbo].[users] WHERE user_id = ${id}`);
      // ok: node-mssql-sqli
      const dbResult = await request.query(`SELECT * FROM [foobar].[dbo].[users] WHERE user_id = ?`);
      results = dbResult?.recordset ?? [];
    } catch (err) {
      console.log('get-user-by-id error', err.message, err);
      res.status(500).json({ errors: [err.message] });
      return;
    }
    res.status(200).json({ results });
  });

  app.listen(8080, () => {
    console.log('Example foobar app listening at http://localhost:8080');
  }).on('error', (err) => {
    console.log('express error: ', err.message, err);
  });
})();

async function test1(userInput) {
  const pool = await new mssql.ConnectionPool({server: 'localhost'});
  const request = pool.request();
  // ruleid: node-mssql-sqli
  const dbResult = await request.query("SELECT * FROM [foobar].[dbo].[users] WHERE user_id =" + userInput);
  return dbResult;
}

async function testOk1() {
  const pool = await new mssql.ConnectionPool({server: 'localhost'});
  const request = pool.request();
  const query = "SELECT * FROM [foobar].[dbo].[users] WHERE user_id = 1";
  // ok: node-mssql-sqli
  const dbResult = await request.query(query);
  return dbResult;
}