Parameterized Queries & ACID Transactions

Why Parameterized Queries Are Mandatory

Never interpolate strings into SQL statements:

// ❌ DANGEROUS: SQL Injection Vulnerability
query := fmt.Sprintf("SELECT * FROM users WHERE email = '%s'", email)

// ✅ SAFE: Database drivers use server-side parameter substitution
query := "SELECT id, name, price FROM products WHERE id = $1"
row := db.QueryRowContext(ctx, query, productID)

Safe Rows Iteration Pattern

rows, err := db.QueryContext(ctx, "SELECT id, name, price FROM products")
if err != nil {
    return err
}
defer rows.Close() // Mandatory: releases DB connection back to the pool

for rows.Next() {
    var p Product
    if err := rows.Scan(&p.ID, &p.Name, &p.Price); err != nil {
        return err
    }
    products = append(products, p)
}

// Check for errors encountered during iteration
if err = rows.Err(); err != nil {
    return err
}

Atomic Transactions with db.BeginTx

tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
if err != nil {
    return err
}
defer tx.Rollback() // Safe no-op if tx.Commit() succeeds

// Execute operations on tx
_, err = tx.ExecContext(ctx, "UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, fromID)
if err != nil {
    return err
}

_, err = tx.ExecContext(ctx, "UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, toID)
if err != nil {
    return err
}

return tx.Commit()