go.lang.security.audit.database.string-formatted-query.string-formatted-query

Community Favorite

semgrep

Author

74,617

Download Count*

License

String-formatted SQL query detected. This could lead to SQL injection if the string is not sanitized properly. Audit this call to ensure the SQL is not manipulable by external data.

Run Locally

Run in CI

Semgrep CI docs

Defintion

Edit Rule View Source

Open in Playground

rules:
  - id: string-formatted-query
    languages:
      - go
    message: String-formatted SQL query detected. This could lead to SQL injection
      if the string is not sanitized properly. Audit this call to ensure the SQL
      is not manipulable by external data.
    severity: WARNING
    metadata:
      owasp:
        - A01:2017 - Injection
        - A03:2021 - Injection
      cwe:
        - "CWE-89: Improper Neutralization of Special Elements used in an SQL
          Command ('SQL Injection')"
      source-rule-url: https://github.com/securego/gosec
      category: security
      technology:
        - go
      confidence: LOW
      references:
        - https://owasp.org/Top10/A03_2021-Injection
      cwe2022-top25: true
      cwe2021-top25: true
      subcategory:
        - audit
      likelihood: LOW
      impact: HIGH
      license: Commons Clause License Condition v1.0[LGPL-2.1-only]
      vulnerability_class:
        - SQL Injection
    patterns:
      - metavariable-regex:
          metavariable: $OBJ
          regex: (?i).*(db|database)
      - pattern-not-inside: |
          $VAR = "..." + "..."
          ...
          $OBJ.$SINK(..., $VAR, ...)
      - pattern-not: $OBJ.Exec("...")
      - pattern-not: $OBJ.ExecContext($CTX, "...")
      - pattern-not: $OBJ.Query("...")
      - pattern-not: $OBJ.QueryContext($CTX, "...")
      - pattern-not: $OBJ.QueryRow("...")
      - pattern-not: $OBJ.QueryRow($CTX, "...")
      - pattern-not: $OBJ.QueryRowContext($CTX, "...")
      - pattern-either:
          - pattern: $OBJ.Exec($X + ...)
          - pattern: $OBJ.ExecContext($CTX, $X + ...)
          - pattern: $OBJ.Query($X + ...)
          - pattern: $OBJ.QueryContext($CTX, $X + ...)
          - pattern: $OBJ.QueryRow($X + ...)
          - pattern: $OBJ.QueryRow($CTX, $X + ...)
          - pattern: $OBJ.QueryRowContext($CTX, $X + ...)
          - pattern: $OBJ.Exec(fmt.$P("...", ...))
          - pattern: $OBJ.ExecContext($CTX, fmt.$P("...", ...))
          - pattern: $OBJ.Query(fmt.$P("...", ...))
          - pattern: $OBJ.QueryContext($CTX, fmt.$P("...", ...))
          - pattern: $OBJ.QueryRow(fmt.$P("...", ...))
          - pattern: $OBJ.QueryRow($CTX, fmt.$U("...", ...))
          - pattern: $OBJ.QueryRowContext($CTX, fmt.$P("...", ...))
          - patterns:
              - pattern-either:
                  - pattern: $QUERY = fmt.Fprintf($F, "$SQLSTR", ...)
                  - pattern: $QUERY = fmt.Sprintf("$SQLSTR", ...)
                  - pattern: $QUERY = fmt.Printf("$SQLSTR", ...)
                  - pattern: $QUERY = $X + ...
              - pattern-either:
                  - pattern-inside: |
                      func $FUNC(...) {
                          ...
                          $OBJ.Query($QUERY, ...)
                          ...
                      }
                  - pattern-inside: |
                      func $FUNC(...) {
                          ...
                          $OBJ.ExecContext($CTX, $QUERY, ...)
                          ...
                      }
                  - pattern-inside: |
                      func $FUNC(...) {
                          ...
                          $OBJ.Exec($QUERY, ...)
                          ...
                      }
                  - pattern-inside: |
                      func $FUNC(...) {
                          ...
                          $OBJ.QueryRow($CTX, $QUERY)
                          ...
                      }
                  - pattern-inside: |
                      func $FUNC(...) {
                          ...
                          $OBJ.QueryRow($QUERY)
                          ...
                      }
                  - pattern-inside: |
                      func $FUNC(...) {
                          ...
                          $OBJ.QueryContext($CTX, $QUERY)
                          ...
                      }
                  - pattern-inside: |
                      func $FUNC(...) {
                          ...
                          $OBJ.QueryRowContext($CTX, $QUERY, ...)
                          ...
                      }

Examples

string-formatted-query.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"http"

	"github.com/jackc/pgx/v4"
)

var db *sql.DB
var postgresDb *pgx.Conn

func dbExec(r *http.Request) {
	customerId := r.URL.Query().Get("id")
	// ruleid: string-formatted-query
	query := "SELECT number, expireDate, cvv FROM creditcards WHERE customerId = " + customerId

	row, _ := db.Exec(query)

	// ok: string-formatted-query
	out, err := sshClient.Exec(fmt.Sprintf("sudo bash %s", scriptPath))
}

func okDbExec(r *http.Request) {
	customerId := r.URL.Query().Get("id")
	// ok: string-formatted-query
	query := "SELECT number, expireDate, cvv FROM creditcards WHERE customerId = customerId"

	row, _ := db.Exec(query)
}

func dbQuery1(r *http.Request) {
    // ruleid: string-formatted-query
    _, err = db.Query("INSERT into users (username, password) VALUES(" + username + ", " + password)
    if err != nil {
        http.Error("mistake")
    }
}

func dbQuery2(r *http.Request, username string, password string) {
    // ruleid: string-formatted-query
    query = "INSERT into users (username, password) VALUES(" + username + ", " + password
    _, err = db.QueryRow(query)
    if err != nil {
        http.Error("mistake")
    }
}

func dbQuery3(r *http.Request, username string) {
    // ruleid: string-formatted-query
    query = username + " AND INSERT into users (username, password)"
    _, err = db.Exec(query)
    if err != nil {
        http.Error("mistake")
    }
}

func dbQuery4(r *http.Request, username string) {
    // ruleid: string-formatted-query
    query := fmt.Sprintf("%s AND INSERT into users (username, password)", username)
    _, err = db.Exec(query)
    if err != nil {
        http.Error("mistake")
    }
}

func dbQuery5(r *http.Request, username string, password string) {
	// ruleid: string-formatted-query
	query := fmt.Sprintf("INSERT into users (username, password) VALUES(%s, %s)", username, password)
	_, err = db.QueryRow(query)
	if err != nil {
		http.Error("mistake")
	}
}

func okDbQuery1(r *http.Request) {
    // ok: string-formatted-query
    _, err = db.Exec("INSERT into users (username, password) VALUES(" + "username" + ", " + "smth)")
    if err != nil {
        http.Error("mistake")
    }
}

func dbExecContext(r *http.Request) {
	ctx := context.Background()
	customerId := r.URL.Query().Get("id")
	// ruleid: string-formatted-query
	query := "SELECT number, expireDate, cvv FROM creditcards WHERE customerId = " + customerId

	row, _ := db.ExecContext(ctx, query)
}

func dbQuery4(r *http.Request) {
	customerId := r.URL.Query().Get("id")
	// ruleid: string-formatted-query
	query := "SELECT number, expireDate, cvv FROM creditcards WHERE customerId = " + customerId

	row, _ := db.Query(query)
}

func dbQueryContext(r *http.Request) {
	ctx := context.Background()
	customerId := r.URL.Query().Get("id")
	// ruleid: string-formatted-query
	query := "SELECT number, expireDate, cvv FROM creditcards WHERE customerId = " + customerId

	row, _ := db.QueryContext(ctx, query)
}

func dbQueryRow(r *http.Request) {
	customerId := r.URL.Query().Get("id")
	// ruleid: string-formatted-query
	query := "SELECT number, expireDate, cvv FROM creditcards WHERE customerId = " + customerId

	row, _ := db.QueryRow(query)
}

func dbQueryRowContext(r *http.Request) {
	ctx := context.Background()
	customerId := r.URL.Query().Get("id")
	// ruleid: string-formatted-query
	query := "SELECT number, expireDate, cvv FROM creditcards WHERE customerId = " + customerId

	row, _ := db.QueryRowContext(ctx, query)
}

func dbExecFmt(r *http.Request) {
	customerId := r.URL.Query().Get("id")
	query := "SELECT number, expireDate, cvv FROM creditcards WHERE customerId = %s"
	// ruleid: string-formatted-query
    query = fmt.Printf(query, customerId)

	row, _ := db.Exec(query)
}

func dbExecContextFmt(r *http.Request) {
	ctx := context.Background()
	customerId := r.URL.Query().Get("id")
	query := "SELECT number, expireDate, cvv FROM creditcards WHERE customerId = %s"
	// ruleid: string-formatted-query
    query = fmt.Printf(query, customerId)

	row, _ := db.ExecContext(ctx, query)
}

func dbQueryFmt(r *http.Request) {
	customerId := r.URL.Query().Get("id")
	query := "SELECT number, expireDate, cvv FROM creditcards WHERE customerId = %s"
	// ruleid: string-formatted-query
    query = fmt.Printf(query, customerId)

	row, _ := db.Query(query)
}

func dbQueryContextFmtReassign(r *http.Request) {
	ctx := context.Background()
	customerId := r.URL.Query().Get("id")
	query := "SELECT number, expireDate, cvv FROM creditcards WHERE customerId = %s"
	// ruleid: string-formatted-query
    query = fmt.Printf(query, customerId)

	row, _ := db.QueryContext(ctx, query)
}


func dbQueryContextFmt(r *http.Request) {
	ctx := context.Background()
	customerId := r.URL.Query().Get("id")
	// ruleid: string-formatted-query
	query := fmt.Sprintf("SELECT number, expireDate, cvv FROM creditcards WHERE customerId = %s", customerId)
	row, _ := db.QueryContext(ctx, query)
}

func dbQueryRowFmt(r *http.Request) {
	customerId := r.URL.Query().Get("id")
	query := "SELECT number, expireDate, cvv FROM creditcards WHERE customerId = %s"
		// ruleid: string-formatted-query
	query = fmt.Printf(query, customerId)

	row, _ := db.QueryRow(query)
}

func dbQueryRowContextReassign(r *http.Request) {
	ctx := context.Background()
	customerId := r.URL.Query().Get("id")
	query := "SELECT number, expireDate, cvv FROM creditcards WHERE customerId = %s"
	// ruleid: string-formatted-query
    query = fmt.Printf(query, customerId)

	row, _ := db.QueryRowContext(ctx, query)
}

func dbQueryRowContextFmt(r *http.Request) {
	ctx := context.Background()
	customerId := r.URL.Query().Get("id")
	// ruleid: string-formatted-query
	query := fmt.Sprintf("SELECT number, expireDate, cvv FROM creditcards WHERE customerId = %s", customerId)

	row, _ := db.QueryRowContext(ctx, query)
}

func unmodifiedString() {
	// ok: string-formatted-query
	query := "SELECT number, expireDate, cvv FROM creditcards WHERE customerId = 1234"
	row, _ := db.Query(query)
}

func unmodifiedStringDirectly() {
    // ok: string-formatted-query
	row, _ := db.Query("SELECT number, expireDate, cvv FROM creditcards WHERE customerId = 1234")
}

func badDirectQueryAdd(r *http.Request) {
    ctx := context.Background()
    customerId := r.URL.Query().Get("id")

	// ruleid: string-formatted-query
    row, _ := db.QueryRowContext(ctx, "SELECT number, expireDate, cvv FROM creditcards WHERE customerId = " + customerId)
}

func badDirectQueryFmt(r *http.Request) {
    ctx := context.Background()
    customerId := r.URL.Query().Get("id")

	// ruleid: string-formatted-query
    row, _ := db.QueryRowContext(ctx, fmt.Printf("SELECT number, expireDate, cvv FROM creditcards WHERE customerId = %s", customerId))
}

func postgresBadDirectQueryFmt(r *http.Request) {
    ctx := context.Background()
    customerId := r.URL.Query().Get("id")

	// ruleid: string-formatted-query
    row, _ := postgresDb.QueryRow(ctx, fmt.Printf("SELECT number, expireDate, cvv FROM creditcards WHERE customerId = %s", customerId))
}

func postgresQueryFmt(r *http.Request) {
    ctx := context.Background()
    customerId := r.URL.Query().Get("id")
	// ruleid: string-formatted-query
	query := fmt.Sprintf("SELECT number, expireDate, cvv FROM creditcards WHERE customerId = %s", customerId)

    row, _ := postgresDb.QueryRow(ctx, query)
}

package main

import (
    "context"
    "database/sql"
    "fmt"
    "http"

    "github.com/jackc/pgx/v4"
)
// cf. https://github.com/returntocorp/semgrep-rules/issues/1249
func new() {
	// ok: string-formatted-query
    var insertSql string = "insert into t_ad_experiment (exp_layer,buckets,opposite_buckets,is_transparent, " +
    " description,is_full,start_time,end_time,creat_time,update_time,update_user,white_list,extra,status)" +
    " value (?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
    t := time.Now().Unix()
    InsertResult, err := DbConn.Exec(insertSql, info.Exp_layer, info.Buckets, info.Opposite_buckets,
        info.Is_transparent, info.Description, info.Is_full, info.Start_time, info.End_time, t, t,
        session.User, info.White_list, info.Extra, 0)
}

func new2() {
	// ok: string-formatted-query
    var insertSql string = "insert into t_ad_experiment (exp_layer,buckets,opposite_buckets,is_transparent, description,is_full,start_time,end_time,creat_time,update_time,update_user,white_list,extra,status) value (?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
    t := time.Now().Unix()
    InsertResult, err := DbConn.Exec(insertSql, info.Exp_layer, info.Buckets, info.Opposite_buckets,
        info.Is_transparent, info.Description, info.Is_full, info.Start_time, info.End_time, t, t,
        session.User, info.White_list, info.Extra, 0)
}

Short Link: https://sg.run/ydEr