go.lang.security.audit.sqli.pgx-sqli.pgx-sqli

profile photo of returntocorpreturntocorp
Author
649
Download Count*
LGPL Commons Clause
License

Detected string concatenation with a non-literal variable in a pgx Go 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 instead. You can use parameterized queries like so: (SELECT $1 FROM table, `data1)

Run Locally

Run in CI

Semgrep CI docs

Defintion

Edit RuleView Source
Open in Playground 
rules:
  - id: pgx-sqli
    languages:
      - go
    message: "Detected string concatenation with a non-literal variable in a pgx Go
      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 instead. You can use parameterized
      queries like so: (`SELECT $1 FROM table`, `data1)"
    metadata:
      cwe:
        - "CWE-89: Improper Neutralization of Special Elements used in an SQL
          Command ('SQL Injection')"
      references:
        - https://github.com/jackc/pgx
        - https://pkg.go.dev/github.com/jackc/pgx/v4#hdr-Connection_Pool
      category: security
      technology:
        - pgx
      confidence: LOW
      owasp:
        - A01:2017 - Injection
        - A03:2021 - Injection
      cwe2022-top25: true
      cwe2021-top25: true
      subcategory:
        - vuln
      likelihood: LOW
      impact: HIGH
      license: Commons Clause License Condition v1.0[LGPL-2.1-only]
    patterns:
      - pattern-either:
          - patterns:
              - pattern: $DB.$METHOD(...,$QUERY,...)
              - pattern-either:
                  - pattern-inside: |
                      $QUERY = $X + $Y
                      ...
                  - pattern-inside: |
                      $QUERY += $X
                      ...
                  - pattern-inside: |
                      $QUERY = fmt.Sprintf("...", $PARAM1, ...)
                      ...
              - pattern-not-inside: |
                  $QUERY += "..."
                  ...
              - pattern-not-inside: |
                  $QUERY = "..." + "..."
                  ...
          - pattern: $DB.$METHOD(..., $X + $Y, ...)
          - pattern: $DB.$METHOD(..., fmt.Sprintf("...", $PARAM1, ...), ...)
      - pattern-either:
          - pattern-inside: |
              $DB, ... = pgx.Connect(...)
              ...
          - pattern-inside: |
              $DB, ... = pgx.NewConnPool(...)
              ...
          - pattern-inside: |
              $DB, ... = pgx.ConnectConfig(...)
              ...
          - pattern-inside: |
              func $FUNCNAME(..., $DB *pgx.Conn, ...) {
                ...
              }
      - pattern-not: $DB.$METHOD(..., "..." + "...", ...)
      - metavariable-regex:
          metavariable: $METHOD
          regex: ^(Exec|ExecEx|Query|QueryEx|QueryRow|QueryRowEx)$
    severity: ERROR

Examples

pgx-sqli.go

package main

import "database/sql"
import "fmt"

func bad1() {
    pgxConfig := pgx.ConnConfig{
        Host:     "localhost",
        Database: "quetest",
        User:     "quetest",
    }
    pgxConnPoolConfig := pgx.ConnPoolConfig{pgxConfig, 3, nil}
    conn, err := pgx.NewConnPool(pgxConnPoolConfig)
    if err != nil {
        log.Fatal(err)
    }
    query = "SELECT name FROM users WHERE age=" + req.FormValue("age")
    // ruleid: pgx-sqli
    rows, err := conn.Query(query)
}

func bad2() {
    conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
    if err != nil {
        panic(err)
    }
    query = "SELECT name FROM users WHERE age=" + req.FormValue("age")
    // ruleid: pgx-sqli
    conn.QueryEx(query)
}

func bad3() {
    config, err := pgx.ParseConfig(os.Getenv("DATABASE_URL"))
    if err != nil {
       panic(err)
    }
    config.Logger = log15adapter.NewLogger(log.New("module", "pgx"))

    conn, err := pgx.ConnectConfig(context.Background(), config)

    query = "SELECT name FROM users WHERE age="
    query += req.FormValue("age")
    // ruleid: pgx-sqli
    conn.QueryRow(query)
}

func bad4(conn *pgx.Conn) {
    query = fmt.Sprintf("SELECT * FROM users WHERE email='%s';", email)
    // ruleid: pgx-sqli
    conn.Exec(query)
}

func bad4(conn *pgx.Conn) {
    // ruleid: pgx-sqli
    conn.Exec("SELECT name FROM users WHERE age=" + req.FormValue("age"))
}

func bad5(conn *pgx.Conn) {
    // ruleid: pgx-sqli
    conn.ExecEx(fmt.Sprintf("SELECT * FROM users WHERE email='%s';", email))
}

func ok1(conn *pgx.Conn) {
    query = fmt.Sprintf("SELECT * FROM users WHERE email=hello;")
    // ok: pgx-sqli
    conn.QueryRowEx(query)
}

func ok2(conn *pgx.Conn) {
    query = "SELECT name FROM users WHERE age=" + "3"
    // ok: pgx-sqli
    conn.Query(query)
}

func ok3(conn *pgx.Conn) {
    query = "SELECT name FROM users WHERE age="
    query += "3"
    // ok: pgx-sqli
    conn.QueryRow(query)
}

func ok4(conn *pgx.Conn) {
    // ok: pgx-sqli
    conn.Exec("INSERT INTO users(name, email) VALUES($1, $2)",
  "Jon Calhoun", "jon@calhoun.io")
}

func ok5(conn *pgx.Conn) {
    // ok: pgx-sqli
    conn.Exec("SELECT name FROM users WHERE age=" + "3")
}

func ok6(conn *pgx.Conn) {
    // ok: pgx-sqli
    conn.Exec(fmt.Sprintf("SELECT * FROM users WHERE email=hello;"))
}

func ok7() {
    conf := pgx.ConnPoolConfig{
        ConnConfig: pgx.ConnConfig{
            Host:     "/run/postgresql",
            User:     "postgres",
            Database: "test",
        },
        MaxConnections: 5,
    }
    db, err := pgx.NewConnPool(conf)
    if err != nil {
        panic(err)
    }
    if _, err := db.Prepare("my-query", "select $1::int"); err != nil {
        panic(err)
    }
    // ok: pgx-sqli
    row := db.QueryRow("my-query", 10)
    var i int
    if err := row.Scan(&i); err != nil {
        panic(err)
    }
    fmt.Println(i)
}