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

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

Detected string concatenation with a non-literal variable in a node-postgres 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: client.query('SELECT $1 from table', [userinput])

Run Locally

Run in CI

Semgrep CI docs

Defintion

Edit RuleView Source
Open in Playground 
rules:
  - id: node-postgres-sqli
    message: "Detected string concatenation with a non-literal variable in a
      node-postgres 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:
      `client.query('SELECT $1 from table', [userinput])`"
    metadata:
      owasp:
        - A08:2021 - Software and Data Integrity Failures
      cwe:
        - "CWE-915: Improperly Controlled Modification of Dynamically-Determined
          Object Attributes"
      references:
        - https://node-postgres.com/features/queries
      category: security
      technology:
        - node-postgres
      subcategory:
        - vuln
      likelihood: MEDIUM
      impact: MEDIUM
      confidence: LOW
      license: Commons Clause License Condition v1.0[LGPL-2.1-only]
      vulnerability_class:
        - Mass Assignment
    languages:
      - javascript
      - typescript
    severity: WARNING
    mode: taint
    pattern-sources:
      - patterns:
          - pattern-inside: |
              function ... (...,$FUNC,...) {
                ...
              }
          - focus-metavariable: $FUNC
          - pattern-not-inside: |
              $F. ... .$SOURCE(...)
    pattern-sinks:
      - patterns:
          - pattern-either:
              - pattern-inside: |
                  const { $CLIENT } = require('pg')
                   ...
              - pattern-inside: |
                  var { $CLIENT } = require('pg')
                   ...
              - pattern-inside: |
                  let { $CLIENT } = require('pg')
                   ...
          - pattern-either:
              - pattern-inside: |
                  $DB = new $CLIENT(...)
                  ...
              - pattern-inside: |
                  $NEWPOOL = new $CLIENT(...)
                  ...
                  $NEWPOOL.connect((..., $DB, ...) => {
                      ...
                  })
          - pattern: $DB.query($QUERY,...)
          - focus-metavariable: $QUERY

Examples

node-postgres-sqli.js

function  bad1(userInput) {
    const { Client } = require('pg')
    const client = new Client()
    await client.connect()
    let query = "SELECT name FROM users WHERE age=" + userInput
    // ruleid: node-postgres-sqli
    const res = await client.query(query)
    console.log(res.rows[0].message) // Hello world!
    await client.end()
}


function bad2(req) {
    const { Client, Pool } = require('pg')
    const pool = new Pool()
    let query = "SELECT name FROM users WHERE age="
    query += req.FormValue("age")
    // ruleid: node-postgres-sqli
    const res = await pool.query(query)
    console.log(res.rows[0].message) // Hello world!
    await client.end()
}

function bad3(userinput) {
    const { Client } = require('pg')
    const client = new Client()
    await client.connect()
    let query = "SELECT name FROM users WHERE age=".concat(userinput)
    // passes on 0.111.0 and higher
    // ruleid: node-postgres-sqli
    const res = await client.query(query)
    console.log(res.rows[0].message) // Hello world!
    await client.end()
}

function bad4(req) {
    const { Pool } = require('pg')
    const pool = new Pool()
    pool.on('error', (err, client) => {
      console.error('Unexpected error on idle client', err)
      process.exit(-1)
    })
    pool.connect((err, client, done) => {
      if (err) throw err
      // passes on 0.111.0 and higher
      // ruleid: node-postgres-sqli
      client.query("SELECT name FROM users WHERE age=" + req.FormValue("age"), (err, res) => {
        done()
        if (err) {
          console.log(err.stack)
        } else {
          console.log(res.rows[0])
        }
      })
    })
}

function bad5(userinput) {
    const { Pool } = require('pg')
    const pool = new Pool()
    pool
      // ruleid: node-postgres-sqli
      .query('SELECT * FROM users WHERE id ='.concat(userinput))
      .then(res => console.log('user:', res.rows[0]))
      .catch(err =>
        setImmediate(() => {
          throw err
        })
      )
}

function bad6(userinput) {
    const { Pool } = require('pg')
    const pool = new Pool()
    pool
      // ruleid: node-postgres-sqli
      .query('SELECT * FROM users WHERE id =' + userinput)
      .then(res => console.log('user:', res.rows[0]))
      .catch(err =>
        setImmediate(() => {
          throw err
        })
      )
}

function ok1() {
    const { Client } = require('pg')
    const client = new Client()
    await client.connect()
    query = "SELECT * FROM users WHERE email=".concat("hello")
    // ok: node-postgres-sqli
    client.query(query)
}

function ok2() {
    const { Client } = require('pg')
    const client = new Client()
    await client.connect()
    query = "SELECT name FROM users WHERE age=" + "3"
    // ok: node-postgres-sqli
    client.query(query)
}

function ok3() {
    const { Client } = require('pg')
    const client = new Client()
    await client.connect()
    query = "SELECT name FROM users WHERE age="
    query += "3"
    // ok: node-postgres-sqli
    client.query(query)
}

function ok4() {
    const { Client } = require('pg')
    const client = new Client()
    await client.connect()
    // ok: node-postgres-sqli
    client.query("INSERT INTO users(name, email) VALUES($1, $2)",
  ["Jon Calhoun", userinput])
}

function ok5() {
    const { Client } = require('pg')
    const client = new Client()
    await client.connect()
    // ok: node-postgres-sqli
    client.query("SELECT name FROM users WHERE age=" + "3")
}

function ok6() {
    const { Client } = require('pg')
    const client = new Client()
    await client.connect()
    // ok: node-postgres-sqli
    client.query("SELECT * FROM users WHERE email=".concat("hello"))
}

function ok7() {
    const { Client } = require('pg')
    const client = new Client()
    const query = {
      // give the query a unique name
      name: 'fetch-user',
      text: 'SELECT * FROM user WHERE id = $1',
      values: [userinput],
    }
    // ok: node-postgres-sqli
    client.query(query, (err, res) => {
      if (err) {
        console.log(err.stack)
      } else {
        console.log(res.rows[0])
      }
    })
}