在Golang中执行SQL查询

Question

I've seen two ways of people executing queries using Golang builtin database/sql queries. One of them is using fmt.Sprintf:

func (db *DB) CreateUserTable() (sql.Result, error) {
    statement := "CREATE TABLE %s (%s, %s, %s, %s, %s)"
    v := []interface{}{"User", "ID int PRIMARY KEY NOT NULL", "Name varchar(100) UNIQUE", "Email varchar(100) UNIQUE", "Address varchar(100) ",  "Username varchar(100) UNIQUE"}
    return db.Exec(fmt.Sprintf(statement, v...))
}

and the other one is using prepared statement:

func (db *DB) CreateUserTable() (sql.Result, error) {    
    statement, err := db.Prepare("INSERT INTO User(tbl1,tbl2,tbl3) VALUES(?,?,?)")
    if err != nil {
        log.Fatal(err)
    }
    return statement.Exec("value1", "value2", "value3")
}

The first gives benefit by enabling you to dynamically set the table name, column name, and the values. But the second one only for values. What's the difference? Which one should I use?

Answer 1

Never build SQL from strings that come from outside your system.

Always use the ? syntax.

If you must set SQL parts like table names, prepare multiple, complete SQL statements that contain ? for the values. Select the SQL to execute, maybe based on user input, but never build SQL from user input.