Go中db.Prepare("INSERT INTO ? VALUES ()")无法运行问题求助

Hey there! Let's walk through what's going wrong with your current code and how to get it working properly.

The Root Cause #

The key issue here is that SQL prepared statement placeholders (?) are only meant for values, not identifiers like table names or column names.

When you try to use db.Prepare("INSERT INTO ? VALUES ()"), the database driver treats the ? as a string parameter. That means your final SQL ends up looking something like:

INSERT INTO 'your_table' VALUES ()

Which is invalid SQL—table names shouldn't be wrapped in single quotes. MySQL will throw a syntax error because it doesn't recognize 'your_table' as a valid table identifier.

Plus, prepared statements are designed to prevent SQL injection by escaping values, but they don't handle structural parts of the query like table names.

The Solution #

To make this work, you need to construct the SQL query with the table name directly, but you must first validate the table name to avoid SQL injection risks. Here's a step-by-step approach:

1. Validate the Table Name (Critical for Security) #

Never directly use user-provided or untrusted input as a table name without checking it against a whitelist. This stops attackers from injecting malicious SQL.

2. Construct the Query Safely #

Wrap the table name in backticks (`) to handle cases where the table name is a MySQL keyword (like user or order).

3. Execute the Query #

Since there are no value parameters to bind, you can use db.Exec() directly instead of preparing a statement (though you could still prepare it if needed, it's unnecessary here).

Full Code Example #

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

func insertDefaultRow(db *sql.DB, tableName string) (sql.Result, error) {
    // Step 1: Validate table name against a whitelist
    allowedTables := map[string]bool{
        "users":    true,
        "products": true,
        "orders":   true,
    }
    if !allowedTables[tableName] {
        return nil, fmt.Errorf("invalid or disallowed table name: %q", tableName)
    }

    // Step 2: Build the safe SQL query
    query := fmt.Sprintf("INSERT INTO `%s` VALUES ()", tableName)

    // Step 3: Execute the query
    result, err := db.Exec(query)
    if err != nil {
        return nil, fmt.Errorf("failed to insert default row: %w", err)
    }

    return result, nil
}

func main() {
    // Initialize your DB connection here
    db, err := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/dbname")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Example usage
    res, err := insertDefaultRow(db, "users")
    if err != nil {
        panic(err)
    }
    id, _ := res.LastInsertId()
    fmt.Printf("Inserted row with ID: %d\n", id)
}

Important Notes #

• Table Structure Requirement: This query only works if every column in your table has a default value, or allows NULL. If any column has no default and isn't nullable, MySQL will throw an error.
• Backticks Matter: Using backticks around the table name ensures compatibility with MySQL keywords and table names containing special characters.
• Whitelist is Non-Negotiable: Skipping the whitelist check opens you up to SQL injection attacks. Always validate untrusted input.

内容的提问来源于stack exchange，提问作者Raphaël Balet