ConcatenateSqlStrings [anti-pattern] v0.1.0

Constructing a SQL statement by concatenating, interpolating, formatting, or templating data into the query text. Any technique where the resulting query string contains values that originated outside the source code.

Label

Building SQL queries by string concatenation or interpolation

Why Bad

The data and the SQL grammar share one channel; the database has no way to tell ' OR 1=1 -- apart from a legitimate name. Result: auth bypass, data exfiltration, full table dumps, or destructive operations (DROP, UPDATE all rows). SQL injection has been #1 or #2 on the OWASP Top Ten for two decades because it is still introduced daily.

Instead Do

    Use parameter binding via the driver:

      // Wrong:
      db.query(`SELECT * FROM users WHERE email = '${email}'`)

      // Right:
      db.query("SELECT * FROM users WHERE email = ?", [email])

    For dynamic identifiers (column to ORDER BY, table name) where binding
    is not supported, validate against a hard-coded allow-list of legal
    identifiers, then concatenate only the allow-listed value:

      const allowedSort = ['name', 'created_at', 'id']
      if (!allowedSort.includes(sort)) throw new Error('bad sort')
      db.query(`SELECT * FROM users ORDER BY ${sort}`)
  

ConcatenateSqlStrings [anti-pattern] v0.1.0

Constructing a SQL statement by concatenating, interpolating, formatting, or templating data into the query text. Any technique where the resulting query string contains values that originated outside the source code.

Label

Building SQL queries by string concatenation or interpolation

Why Bad

The data and the SQL grammar share one channel; the database has no way to tell ' OR 1=1 -- apart from a legitimate name. Result: auth bypass, data exfiltration, full table dumps, or destructive operations (DROP, UPDATE all rows). SQL injection has been #1 or #2 on the OWASP Top Ten for two decades because it is still introduced daily.

Instead Do

    Use parameter binding via the driver:

      // Wrong:
      db.query(`SELECT * FROM users WHERE email = '${email}'`)

      // Right:
      db.query("SELECT * FROM users WHERE email = ?", [email])

    For dynamic identifiers (column to ORDER BY, table name) where binding
    is not supported, validate against a hard-coded allow-list of legal
    identifiers, then concatenate only the allow-listed value:

      const allowedSort = ['name', 'created_at', 'id']
      if (!allowedSort.includes(sort)) throw new Error('bad sort')
      db.query(`SELECT * FROM users ORDER BY ${sort}`)
  

Label

Building SQL queries by string concatenation or interpolation

Why Bad

The data and the SQL grammar share one channel; the database has no way to tell ' OR 1=1 -- apart from a legitimate name. Result: auth bypass, data exfiltration, full table dumps, or destructive operations (DROP, UPDATE all rows). SQL injection has been #1 or #2 on the OWASP Top Ten for two decades because it is still introduced daily.

Instead Do

    Use parameter binding via the driver:

      // Wrong:
      db.query(`SELECT * FROM users WHERE email = '${email}'`)

      // Right:
      db.query("SELECT * FROM users WHERE email = ?", [email])

    For dynamic identifiers (column to ORDER BY, table name) where binding
    is not supported, validate against a hard-coded allow-list of legal
    identifiers, then concatenate only the allow-listed value:

      const allowedSort = ['name', 'created_at', 'id']
      if (!allowedSort.includes(sort)) throw new Error('bad sort')
      db.query(`SELECT * FROM users ORDER BY ${sort}`)