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

Author
649
Download Count*
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
Defintion
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)
}
Short Link: https://sg.run/okKN