go.lang.security.audit.sqli.pg-sqli.pg-sqli

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

Detected string concatenation with a non-literal variable in a go-pg 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 of string concatenation. You can use parameterized queries like so: '(SELECT ? FROM table, data1)'

Run Locally

Run in CI

Semgrep CI docs

Defintion

Edit RuleView Source
Open in Playground 
rules:
  - id: pg-sqli
    languages:
      - go
    message: "Detected string concatenation with a non-literal variable in a go-pg
      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 of string concatenation. You
      can use parameterized queries like so: '(SELECT ? FROM table, data1)'"
    metadata:
      cwe:
        - "CWE-89: Improper Neutralization of Special Elements used in an SQL
          Command ('SQL Injection')"
      references:
        - https://pg.uptrace.dev/
        - https://pkg.go.dev/github.com/go-pg/pg/v10
      category: security
      technology:
        - go-pg
      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]
    severity: ERROR
    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 = pg.Connect(...)
              ...
          - pattern-inside: |
              func $FUNCNAME(..., $DB *pg.DB, ...) {
                ...
              }
      - pattern-not: $DB.$METHOD(..., "..." + "...", ...)
      - metavariable-regex:
          metavariable: $METHOD
          regex: ^(Exec|ExecContext|ExecOne|ExecOneContext|Query|QueryOne|QueryContext|QueryOneContext)$

Examples

pg-sqli.go

package main

import (
    "fmt"

    "github.com/go-pg/pg/v10"
    "github.com/go-pg/pg/v10/orm"
)

func bad1() {
    db := pg.Connect(&pg.Options{
        Addr:     ":5432",
        User:     "user",
        Password: "pass",
        Database: "db_name",
    })
    query = "SELECT name FROM users WHERE age=" + req.FormValue("age")
    // ruleid: pg-sqli
    rows, err := db.ExecContext(query)
}

func bad2() {
    opt, err := pg.ParseURL("postgres://user:pass@localhost:5432/db_name")
    if err != nil {
       panic(err)
    }

    db := pg.Connect(opt)

    query = "SELECT name FROM users WHERE age=" + req.FormValue("age")
    // ruleid: pg-sqli
    rows, err := db.Exec(ctx, query)
}

func bad3() {
    opt, err := pg.ParseURL("postgres://user:pass@localhost:5432/        db_name")
    if err != nil {
        panic(err)
    }

    db := pg.Connect(opt)
    query = "SELECT name FROM users WHERE age="
    query += req.FormValue("age")
    // ruleid: pg-sqli
    db.QueryContext(ctx, query)
}

func bad4(db *pg.DB) {
    query = fmt.Sprintf("SELECT * FROM users WHERE email='%s';", email)
    // ruleid: pg-sqli
    db.Query(ctx, query)
}

func bad5(db *pg.DB) {
    // ruleid: pg-sqli
    db.Exec(ctx, "SELECT name FROM users WHERE age=" + req.FormValue("age"))
}

func bad6(db *pg.DB) {
    // ruleid: pg-sqli
    db.QueryOne(ctx, fmt.Sprintf("SELECT * FROM users WHERE email='%s';", email))
}

func ok1(db *pg.DB) {
    query = fmt.Sprintf("SELECT * FROM users WHERE email=hello;")
    // ok: pg-sqli
    db.QueryContext(ctx, query)
}

func ok2(db *pg.DB) {
    query = "SELECT name FROM users WHERE age=" + "3"
    // ok: pg-sqli
    db.Query(ctx, query)
}

func ok3(db *pg.DB) {
    query = "SELECT name FROM users WHERE age="
    query += "3"
    // ok: pg-sqli
    db.QueryRowContext(ctx, query)
}

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

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

func ok6(db *pg.DB) {
    // ok: pg-sqli
    db.Exec(ctx, fmt.Sprintf("SELECT * FROM users WHERE email=hello;"))
}

func ok7() {
    opt, err := pg.ParseURL("postgres://user:pass@localhost:5432/db_name")
    if err != nil {
       panic(err)
    }

    db := pg.Connect(opt)
    if _, err := db.Prepare("my-query", "select $1::int"); err != nil {
        panic(err)
    }
    // ok: pg-sqli
    row := db.QueryContext(ctx, "my-query", 10)
}