Go: Goのデータベース操作：database/sql、CRUD、インジェクション防止のためのプリコンパイル済みステート…

// Bad code: string concatenation SQL, fatal vulnerability
func searchProducts(w http.ResponseWriter, r *http.Request) {
    search := r.URL.Query().Get("q")
    // If search = "' OR 1=1; DROP TABLE products;--"
    // Final SQL: SELECT * FROM products WHERE name LIKE '%' OR 1=1; DROP TABLE products;--%'
    query := fmt.Sprintf("SELECT * FROM products WHERE name LIKE '%%%s%%'", search)
    rows, err := db.Query(query)  // Nightmare begins
}

// Good code: precompiled, parameters automatically escaped
func searchProducts(w http.ResponseWriter, r *http.Request) {
    search := r.URL.Query().Get("q")

    // ? is a placeholder; the database driver automatically escapes parameters
    // The search value is always treated as a string, never as part of SQL syntax
    stmt, err := db.Prepare("SELECT * FROM products WHERE name LIKE ?")
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }
    defer stmt.Close()

    rows, err := stmt.Query("%" + search + "%")
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }
    defer rows.Close()

    // Process results...
}

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/mattn/go-sqlite3"  // Import driver (_ means only init is executed, not directly used)
)

func main() {
    // Open database (actual connection is established on first query)
    db, err := sql.Open("sqlite3", "./test.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Test if connection is reachable
    err = db.Ping()
    if err != nil {
        log.Fatal("Cannot connect to database:", err)
    }

    fmt.Println("Database connected successfully")
}

package main

import (
    "database/sql"
    "fmt"
    "log"
    "time"

    _ "github.com/go-sql-driver/mysql"
)

func main() {
    // DSN format: user:password@tcp(host:port)/dbname?params
    dsn := "root:password@tcp(127.0.0.1:3306)/shop?charset=utf8mb4&parseTime=true"

    db, err := sql.Open("mysql", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Connection pool configuration
    db.SetMaxOpenConns(25)               // Maximum open connections
    db.SetMaxIdleConns(10)               // Maximum idle connections
    db.SetConnMaxLifetime(5 * time.Minute) // Maximum connection lifetime
    db.SetConnMaxIdleTime(2 * time.Minute) // Maximum idle connection lifetime

    if err = db.Ping(); err != nil {
        log.Fatal("Cannot connect to database:", err)
    }
    fmt.Println("MySQL connected successfully")
}

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/mattn/go-sqlite3"
)

func main() {
    db, err := sql.Open("sqlite3", "./shop.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Create table
    createSQL := `
    CREATE TABLE IF NOT EXISTS products (
        id    INTEGER PRIMARY KEY AUTOINCREMENT,
        name  TEXT NOT NULL,
        price REAL NOT NULL,
        stock INTEGER NOT NULL DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`
    _, err = db.Exec(createSQL)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("Table created successfully")

    // Insert data (Exec returns Result)
    result, err := db.Exec(
        "INSERT INTO products (name, price, stock) VALUES (?, ?, ?)",
        "Laptop", 999.99, 10,
    )
    if err != nil {
        log.Fatal(err)
    }

    id, _ := result.LastInsertId()
    affected, _ := result.RowsAffected()
    fmt.Printf("Inserted: id=%d, rows affected=%d\n", id, affected)
}

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/mattn/go-sqlite3"
)

type Product struct {
    ID    int
    Name  string
    Price float64
    Stock int
}

func main() {
    db, err := sql.Open("sqlite3", "./shop.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // QueryRow: query a single row
    var p Product
    err = db.QueryRow("SELECT id, name, price, stock FROM products WHERE id = ?", 1).
        Scan(&p.ID, &p.Name, &p.Price, &p.Stock)
    if err == sql.ErrNoRows {
        fmt.Println("Product not found")
    } else if err != nil {
        log.Fatal(err)
    } else {
        fmt.Printf("Product: %+v\n", p)
    }

    // Query: query multiple rows
    rows, err := db.Query("SELECT id, name, price, stock FROM products WHERE price < ?", 500)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var pr Product
        err := rows.Scan(&pr.ID, &pr.Name, &pr.Price, &pr.Stock)
        if err != nil {
            log.Fatal(err)
        }
        fmt.Printf("  Product: %+v\n", pr)
    }
    // Check if iteration encountered an error
    if err = rows.Err(); err != nil {
        log.Fatal(err)
    }
}

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/mattn/go-sqlite3"
)

type Product struct {
    Name  string
    Price float64
    Stock int
}

func main() {
    db, err := sql.Open("sqlite3", "./shop.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Precompile SQL statement
    stmt, err := db.Prepare("INSERT INTO products (name, price, stock) VALUES (?, ?, ?)")
    if err != nil {
        log.Fatal(err)
    }
    defer stmt.Close()

    // Batch insert (SQL compiled only once)
    products := []Product{
        {"Mouse", 29.99, 100},
        {"Keyboard", 79.99, 50},
        {"Monitor", 299.99, 20},
        {"USB-C Hub", 49.99, 200},
    }

    for _, p := range products {
        result, err := stmt.Exec(p.Name, p.Price, p.Stock)
        if err != nil {
            log.Printf("Insert failed %s: %v", p.Name, err)
            continue
        }
        id, _ := result.LastInsertId()
        fmt.Printf("Inserted successfully: %s (id=%d)\n", p.Name, id)
    }
}

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/mattn/go-sqlite3"
)

func transferFunds(db *sql.DB, fromID, toID int, amount float64) error {
    // Begin transaction
    tx, err := db.Begin()
    if err != nil {
        return fmt.Errorf("failed to begin transaction: %w", err)
    }
    // Rollback on transaction failure
    defer tx.Rollback()  // If Commit succeeds, Rollback is a no-op

    // 1. Deduct from fromID
    result, err := tx.Exec(
        "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?",
        amount, fromID, amount,
    )
    if err != nil {
        return fmt.Errorf("deduction failed: %w", err)
    }
    affected, _ := result.RowsAffected()
    if affected == 0 {
        return fmt.Errorf("insufficient balance or account does not exist")
    }

    // 2. Add to toID
    result, err = tx.Exec(
        "UPDATE accounts SET balance = balance + ? WHERE id = ?",
        amount, toID,
    )
    if err != nil {
        return fmt.Errorf("credit failed: %w", err)
    }
    affected, _ = result.RowsAffected()
    if affected == 0 {
        return fmt.Errorf("recipient account does not exist")
    }

    // Commit transaction
    return tx.Commit()
}

func main() {
    db, err := sql.Open("sqlite3", "./bank.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Initialize accounts
    db.Exec("CREATE TABLE IF NOT EXISTS accounts (id INTEGER PRIMARY KEY, balance REAL)")
    db.Exec("INSERT OR IGNORE INTO accounts VALUES (1, 1000.00)")
    db.Exec("INSERT OR IGNORE INTO accounts VALUES (2, 500.00)")

    // Transfer 200 from account 1 to 2
    err = transferFunds(db, 1, 2, 200)
    if err != nil {
        log.Printf("Transfer failed: %v\n", err)
    } else {
        fmt.Println("Transfer successful!")
    }
}

package main

import (
    "database/sql"
    "fmt"
    "time"

    _ "github.com/go-sql-driver/mysql"
)

func configurePool(db *sql.DB) {
    // Maximum open connections (new requests queue when this limit is reached)
    db.SetMaxOpenConns(25)

    // Maximum idle connections (kept open but unused)
    db.SetMaxIdleConns(10)

    // Maximum connection lifetime (prevents long-running connections from being dropped by the database)
    db.SetConnMaxLifetime(5 * time.Minute)

    // Maximum idle connection lifetime
    db.SetConnMaxIdleTime(2 * time.Minute)
}

func main() {
    db, _ := sql.Open("mysql", "user:pass@/dbname")
    configurePool(db)
    fmt.Println("Connection pool configured")
}

// inventory.go
package main

import (
    "database/sql"
    "fmt"
    "log"
    "sync"
    "time"

    _ "github.com/mattn/go-sqlite3"
)

// ---------- Store ----------

type InventoryStore struct {
    db *sql.DB
    mu sync.RWMutex
}

func NewInventoryStore(dbPath string) (*InventoryStore, error) {
    db, err := sql.Open("sqlite3", dbPath)
    if err != nil {
        return nil, fmt.Errorf("failed to open database: %w", err)
    }

    // Connection pool configuration
    db.SetMaxOpenConns(10)
    db.SetMaxIdleConns(5)
    db.SetConnMaxLifetime(5 * time.Minute)

    store := &InventoryStore{db: db}
    if err := store.initSchema(); err != nil {
        return nil, fmt.Errorf("failed to initialize schema: %w", err)
    }
    return store, nil
}

func (s *InventoryStore) initSchema() error {
    schema := `
    CREATE TABLE IF NOT EXISTS products (
        id    INTEGER PRIMARY KEY AUTOINCREMENT,
        name  TEXT NOT NULL,
        price REAL NOT NULL,
        stock INTEGER NOT NULL DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS orders (
        id         INTEGER PRIMARY KEY AUTOINCREMENT,
        product_id INTEGER NOT NULL,
        quantity   INTEGER NOT NULL,
        total      REAL NOT NULL,
        status     TEXT NOT NULL DEFAULT 'pending',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (product_id) REFERENCES products(id)
    );`
    _, err := s.db.Exec(schema)
    return err
}

// ---------- Product Operations ----------

func (s *InventoryStore) AddProduct(name string, price float64, stock int) (int64, error) {
    result, err := s.db.Exec(
        "INSERT INTO products (name, price, stock) VALUES (?, ?, ?)",
        name, price, stock,
    )
    if err != nil {
        return 0, err
    }
    return result.LastInsertId()
}

func (s *InventoryStore) GetProduct(id int) (product, error) {
    var p product
    err := s.db.QueryRow(
        "SELECT id, name, price, stock, created_at FROM products WHERE id = ?", id,
    ).Scan(&p.ID, &p.Name, &p.Price, &p.Stock, &p.CreatedAt)
    if err == sql.ErrNoRows {
        return p, fmt.Errorf("product not found")
    }
    return p, err
}

type product struct {
    ID        int
    Name      string
    Price     float64
    Stock     int
    CreatedAt time.Time
}

// ---------- Order Operations (transaction) ----------

type OrderRequest struct {
    ProductID int
    Quantity  int
}

func (s *InventoryStore) PlaceOrder(req OrderRequest) (int64, error) {
    s.mu.Lock()         // Prevent overselling: only one order deduction at a time
    defer s.mu.Unlock()

    tx, err := s.db.Begin()
    if err != nil {
        return 0, fmt.Errorf("failed to begin transaction: %w", err)
    }
    defer tx.Rollback() // Safe rollback

    // 1. Query product and lock row (⚠️ SQLite does not support FOR UPDATE; use MySQL/PostgreSQL for true row locking)
    var price float64
    var stock int
    err = tx.QueryRow(
        "SELECT price, stock FROM products WHERE id = ? FOR UPDATE",
        req.ProductID,
    ).Scan(&price, &stock)
    if err == sql.ErrNoRows {
        return 0, fmt.Errorf("product not found")
    }
    if err != nil {
        return 0, err
    }

    // 2. Check stock
    if stock < req.Quantity {
        return 0, fmt.Errorf("insufficient stock: need %d, have %d", req.Quantity, stock)
    }

    // 3. Deduct stock
    result, err := tx.Exec(
        "UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?",
        req.Quantity, req.ProductID, req.Quantity,
    )
    if err != nil {
        return 0, err
    }
    affected, _ := result.RowsAffected()
    if affected == 0 {
        return 0, fmt.Errorf("concurrent stock shortage")
    }

    // 4. Create order
    total := price * float64(req.Quantity)
    result, err = tx.Exec(
        "INSERT INTO orders (product_id, quantity, total) VALUES (?, ?, ?)",
        req.ProductID, req.Quantity, total,
    )
    if err != nil {
        return 0, err
    }

    orderID, _ := result.LastInsertId()

    // 5. Commit
    if err := tx.Commit(); err != nil {
        return 0, fmt.Errorf("failed to commit transaction: %w", err)
    }

    return orderID, nil
}

// ---------- Report ----------

func (s *InventoryStore) LowStockReport(threshold int) ([]product, error) {
    rows, err := s.db.Query(
        "SELECT id, name, price, stock, created_at FROM products WHERE stock < ? ORDER BY stock ASC",
        threshold,
    )
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var products []product
    for rows.Next() {
        var p product
        if err := rows.Scan(&p.ID, &p.Name, &p.Price, &p.Stock, &p.CreatedAt); err != nil {
            return nil, err
        }
        products = append(products, p)
    }
    return products, rows.Err()
}

// ---------- Main ----------

func main() {
    store, err := NewInventoryStore("./inventory.db")
    if err != nil {
        log.Fatal(err)
    }

    // Add products
    laptopID, _ := store.AddProduct("Laptop", 999.99, 10)
    mouseID, _ := store.AddProduct("Mouse", 29.99, 100)
    fmt.Printf("Products added: laptop=%d, mouse=%d\n", laptopID, mouseID)

    // Place order (transaction-safe)
    orderID, err := store.PlaceOrder(OrderRequest{ProductID: int(laptopID), Quantity: 2})
    if err != nil {
        log.Printf("Order failed: %v\n", err)
    } else {
        fmt.Printf("Order placed successfully: order=%d\n", orderID)
    }

    // Low stock report
    lowStock, _ := store.LowStockReport(20)
    fmt.Printf("Low stock products (%d items):\n", len(lowStock))
    for _, p := range lowStock {
        fmt.Printf("  %s: stock=%d\n", p.Name, p.Stock)
    }
}