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

returntocorp

Author

649

Download Count*

License

Detected string concatenation with a non-literal variable in a go-pg ORM SQL statement. This could lead to SQL injection if the variable is user-controlled and not properly sanitized. In order to prevent SQL injection, do not use strings concatenated with user-controlled input. Instead, use parameterized statements.

Run Locally

Run in CI

Semgrep CI docs

Defintion

Edit Rule View Source

Open in Playground

rules:
  - id: pg-orm-sqli
    patterns:
      - pattern-inside: |
          import (
            ...
            "$IMPORT"
          )
          ...
      - metavariable-regex:
          metavariable: $IMPORT
          regex: .*go-pg
      - 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.$INTFUNC1(...).$METHOD(..., $X + $Y, ...).$INTFUNC2(...)
          - pattern: |
              $DB.$METHOD(..., fmt.Sprintf("...", $PARAM1, ...), ...)
          - pattern-inside: |
              $DB = pg.Connect(...)
              ...
          - pattern-inside: |
              func $FUNCNAME(..., $DB *pg.DB, ...) {
                ...
              }
      - pattern-not-inside: |
          $QUERY = fmt.Sprintf("...", ...,"...", ...)
          ...
      - pattern-not-inside: |
          $QUERY += "..."
          ...
      - pattern-not: $DB.$METHOD(...,"...",...)
      - pattern-not: |
          $DB.$INTFUNC1(...).$METHOD(..., "...", ...).$INTFUNC2(...)
      - pattern-not-inside: |
          $QUERY = "..." + "..."
      - pattern-not: |
          "..."
      - pattern-not: path.Join(...)
      - pattern-not: filepath.Join(...)
      - metavariable-regex:
          metavariable: $METHOD
          regex: ^(Where|WhereOr|Join|GroupExpr|OrderExpr|ColumnExpr)$
    languages:
      - go
    message: Detected string concatenation with a non-literal variable in a go-pg
      ORM SQL statement. This could lead to SQL injection if the variable is
      user-controlled and not properly sanitized. In order to prevent SQL
      injection, do not use strings concatenated with user-controlled input.
      Instead, use parameterized statements.
    metadata:
      cwe:
        - "CWE-89: Improper Neutralization of Special Elements used in an SQL
          Command ('SQL Injection')"
      references:
        - https://pg.uptrace.dev/queries/
      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

Examples

pg-orm-sqli.go

package main

import (
    "fmt"
    "path"

    "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-orm-sqli
    err := db.Model(book).
        Where("id > ?", 100).
        WhereOr(query).
        Limit(1).
        Select()
}

func bad2() {
    db := pg.Connect(opt)
    query = fmt.Sprintf("SELECT * FROM users WHERE email='%s';", email)
    story := new(Story)
    // ruleid: pg-orm-sqli
    err = db.Model(story).
        Relation("Author").
        From("Hello").
        Where("SELECT name FROM users WHERE age=" + req.FormValue("age")).
        Select()
    if err != nil {
        panic(err)
    }
}

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-orm-sqli
    err := db.Model(book).
    Where(query).
    WhereGroup(func(q *pg.Query) (*pg.Query, error) {
        q = q.WhereOr("id = 1").
            WhereOr("id = 2")
        return q, nil
    }).
    Limit(1).
    Select()
}

func bad4(db *pg.DB) {
    query = fmt.Sprintf("SELECT * FROM users WHERE email='%s';", email)
    // ruleid: pg-orm-sqli
    err := db.Model((*Book)(nil)).
    Column("author_id").
    ColumnExpr(query).
    Group("author_id").
    Order("book_count DESC").
    Select(&res)
}

func bad5(db *pg.DB) {
    // ruleid: pg-orm-sqli
    err = db.Model((*Book)(nil)).
    Column("title", "text").
    Where("SELECT name FROM users WHERE age=" + req.FormValue("age")).
    Select()
}

func bad6(db *pg.DB) {
    // ruleid: pg-orm-sqli
    err = db.Model((*Book)(nil)).
    Column("title", "text").
    Where(fmt.Sprintf("SELECT * FROM users WHERE email='%s';",    email)).
    Select()
}

func ok1(db *pg.DB) {
    query = fmt.Sprintf("SELECT * FROM users WHERE email=hello;")
    // ok: pg-orm-sqli
    err = db.Model((*Book)(nil)).
    Column("title", "text").
    Where(query).
    Select()
}

func ok2(db *pg.DB) {
    query = "SELECT name FROM users WHERE age=" + "3"
    // ok: pg-orm-sqli
    err = db.Model((*Book)(nil)).
    Column("title", "text").
    ColumnExpr(query).
    Select()
}

func ok3(db *pg.DB) {
    query = "SELECT name FROM users WHERE age="
    query += "3"
    // ok: pg-orm-sqli
    err = db.Model((*Book)(nil)).
    Column("title", "text").
    Where(query).
    Select()
}

func ok4(db *pg.DB) {
    // ok: pg-orm-sqli
    err := db.Model((*Book)(nil)).
    Column("title", "text").
    Where("id = ?", 1).
    Select(&title, &text)
}

func ok5(db *pg.DB) {
    // ok: pg-orm-sqli
    err := db.Model((*Book)(nil)).
    Column("title", "text").
    Where("SELECT name FROM users WHERE age=" + "3").
    Select(&title, &text)
}

func ok6(db *pg.DB) {
    // ok: pg-orm-sqli
    err := db.Model().
    ColumnExpr(fmt.Sprintf("SELECT * FROM users WHERE email=hello;"))
}

func ok7() {
    // ok: pg-orm-sqli
    path.Join("foo", fmt.Sprintf("%s.baz", "bar"))
}

func ok8() {
    // ok: pg-orm-sqli
    filepath.Join("foo", fmt.Sprintf("%s.baz", "bar"))
}

Short Link: https://sg.run/6rA6