Go pgx PostgreSQL SQL Injection

Critical Risk SQL Injection
sql-injectiongopgxpostgresqldatabase

What it is

SQL injection vulnerability in Go applications using the pgx PostgreSQL driver where user input is directly concatenated into SQL queries without proper parameterization.

// VULNERABLE: pgx SQL injection via string concatenation
package main

import (
    "context"
    "fmt"
    "github.com/jackc/pgx/v4/pgxpool"
)

type User struct {
    ID    int
    Name  string
    Email string
}

func getUserByID(pool *pgxpool.Pool, userID string) (*User, error) {
    // VULNERABLE: String concatenation
    query := "SELECT id, name, email FROM users WHERE id = " + userID

    var user User
    err := pool.QueryRow(context.Background(), query).Scan(&user.ID, &user.Name, &user.Email)
    return &user, err
}

func searchUsers(pool *pgxpool.Pool, name string) ([]User, error) {
    // VULNERABLE: fmt.Sprintf
    query := fmt.Sprintf("SELECT id, name, email FROM users WHERE name ILIKE '%%%s%%'", name)

    rows, err := pool.Query(context.Background(), query)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var user User
        rows.Scan(&user.ID, &user.Name, &user.Email)
        users = append(users, user)
    }
    return users, nil
}

// Malicious inputs:
// userID: "1 OR 1=1 --"
// name: "'; DROP TABLE users; --"
// SECURE: pgx with parameterized queries
package main

import (
    "context"
    "fmt"
    "strconv"
    "github.com/jackc/pgx/v4/pgxpool"
)

type User struct {
    ID    int
    Name  string
    Email string
}

func getUserByID(pool *pgxpool.Pool, userIDStr string) (*User, error) {
    // Basic validation
    userID, err := strconv.Atoi(userIDStr)
    if err != nil || userID <= 0 {
        return nil, fmt.Errorf("invalid user ID")
    }

    // SECURE: $1 placeholder
    query := "SELECT id, name, email FROM users WHERE id = $1"

    var user User
    err = pool.QueryRow(context.Background(), query, userID).Scan(&user.ID, &user.Name, &user.Email)
    return &user, err
}

func searchUsers(pool *pgxpool.Pool, name string) ([]User, error) {
    // SECURE: $1 placeholder
    query := "SELECT id, name, email FROM users WHERE name ILIKE $1"

    rows, err := pool.Query(context.Background(), query, "%"+name+"%")
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var user User
        rows.Scan(&user.ID, &user.Name, &user.Email)
        users = append(users, user)
    }
    return users, nil
}

💡 Why This Fix Works

The vulnerable code uses string concatenation and fmt.Sprintf to build pgx SQL queries, allowing injection attacks. The fixed version uses parameterized queries with $1, $2, etc. placeholders, implements comprehensive input validation, uses transactions for consistency, and demonstrates prepared statements for performance.

Why it happens

Direct concatenation of user input into SQL queries when using pgx PostgreSQL driver.

Root causes

String Concatenation with pgx Queries

Direct concatenation of user input into SQL queries when using pgx PostgreSQL driver.

Preview example – GO 
// Vulnerable
query := "SELECT * FROM users WHERE id = " + userID
row := pool.QueryRow(ctx, query)

fmt.Sprintf in pgx Query Building

Using fmt.Sprintf to build SQL queries with user input creates injection vulnerabilities.

Preview example – GO 
// Vulnerable
query := fmt.Sprintf("SELECT * FROM users WHERE name ILIKE '%%%s%%'", name)
rows, _ := pool.Query(ctx, query)

Fixes

1

Use pgx Parameterized Queries

Always use parameterized queries with $1, $2, etc. placeholders when working with pgx.

View implementation – GO 
// Secure
query := "SELECT * FROM users WHERE id = $1"
row := pool.QueryRow(ctx, query, userID)
2

Implement Input Validation

Validate and sanitize input before using it in database queries. Use type checking and whitelisting.

View implementation – GO 
func validateUserID(userIDStr string) (int, error) {
    id, err := strconv.Atoi(userIDStr)
    if err != nil || id <= 0 {
        return 0, fmt.Errorf("invalid user ID")
    }
    return id, nil
}

Detect This Vulnerability in Your Code

Sourcery automatically identifies go pgx postgresql sql injection and many other security issues in your codebase.

Scan Your Code for Free Explore More Vulnerabilities