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

Community Favorite

returntocorp

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]
    patterns:
      - 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.$P("...", ...))
          - pattern: $OBJ.QueryRowContext($CTX, fmt.$P("...", ...))
          - pattern: |
              $QUERY = "..."
              ...
              $QUERY = $FXN(..., $QUERY, ...)
              ...
              $OBJ.Exec($QUERY, ...)
          - pattern: |
              $QUERY = "..."
              ...
              $QUERY = $FXN(..., $QUERY, ...)
              ...
              $OBJ.Query($QUERY, ...)
          - pattern: |
              $QUERY = "..."
              ...
              $QUERY = $FXN(..., $QUERY, ...)
              ...
              $OBJ.ExecContext($CTX, $QUERY, ...)
          - pattern: |
              $QUERY = "..."
              ...
              $QUERY = $FXN(..., $QUERY, ...)
              ...
              $OBJ.QueryContext($CTX, $QUERY, ...)
          - pattern: |
              $QUERY = "..."
              ...
              $QUERY = $FXN(..., $QUERY, ...)
              ...
              $OBJ.QueryRow($QUERY)
          - pattern: |
              $QUERY = "..."
              ...
              $QUERY = $FXN(..., $QUERY, ...)
              ...
              $OBJ.QueryRow($CTX, $QUERY)
          - pattern: |
              $QUERY = "..."
              ...
              $QUERY = $FXN(..., $QUERY, ...)
              ...
              $OBJ.QueryRowContext($CTX, $QUERY, ...)
          - pattern: |
              $QUERY = "..."
              ...
              $OTHER = $FXN(..., $QUERY, ...)
              ...
              $OBJ.Exec($OTHER, ...)
          - pattern: |
              $QUERY = "..."
              ...
              $OTHER = $FXN(..., $QUERY, ...)
              ...
              $OBJ.Query($OTHER, ...)
          - pattern: |
              $QUERY = "..."
              ...
              $OTHER = $FXN(..., $QUERY, ...)
              ...
              $OBJ.ExecContext($CTX, $OTHER, ...)
          - pattern: |
              $QUERY = "..."
              ...
              $OTHER = $FXN(..., $QUERY, ...)
              ...
              $OBJ.QueryContext($CTX, $OTHER, ...)
          - pattern: |
              $QUERY = "..."
              ...
              $OTHER = $FXN(..., $QUERY, ...)
              ...
              $OBJ.QueryRow($OTHER)
          - pattern: |
              $QUERY = "..."
              ...
              $OTHER = $FXN(..., $QUERY, ...)
              ...
              $OBJ.QueryRow($CTX, $OTHER)
          - pattern: |
              $QUERY = "..."
              ...
              $OTHER = $FXN(..., $QUERY, ...)
              ...
              $OBJ.QueryRowContext($CTX, $OTHER, ...)
          - pattern: |
              $QUERY = $X + ...
              ...
              $OBJ.Exec($QUERY, ...)
          - pattern: |
              $QUERY = $X + ...
              ...
              $OBJ.Query($QUERY, ...)
          - pattern: |
              $QUERY = $X + ...
              ...
              $OBJ.ExecContext($CTX, $QUERY, ...)
          - pattern: |
              $QUERY = $X + ...
              ...
              $OBJ.QueryContext($CTX, $QUERY, ...)
          - pattern: |
              $QUERY = $X + ...
              ...
              $OBJ.QueryRow($QUERY)
          - pattern: |
              $QUERY = $X + ...
              ...
              $OBJ.QueryRow($CTX, $QUERY)
          - pattern: |
              $QUERY = $X + ...
              ...
              $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)
}

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 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")
	// ruleid: string-formatted-query
	query := "SELECT number, expireDate, cvv FROM creditcards WHERE customerId = %s"
    	query = fmt.Printf(query, customerId)

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

func dbExecContextFmt(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 = %s"
    	query = fmt.Printf(query, customerId)

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

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

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

func dbQueryContextFmt(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 = %s"
    	query = fmt.Printf(query, customerId)

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

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

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

func dbQueryRowContextFmt(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 = %s"
    	query = fmt.Printf(query, 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))
}

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