CODEWITHSUNDEEP
databasegoprepared-statement
mfathirirhas
mfathirirhas

Reputation: 2287

Executing SQL query in Golang

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?

Upvotes: 5

Views: 12980

Answers (2)

Himanshu
Himanshu

Reputation: 12675

It is cleaner to use prepared statements so that whenever a requirement changes you can easily modify the statements. Also to prevent SQL injections.

Prepared statements is much better than concatenating strings, for all the usual reasons (avoiding SQL injection attacks, for example).

In MySQL, the parameter placeholder is ?, and in PostgreSQL it is $N, where N is a number. SQLite accepts either of these.

One more thing is Prepared statements can be used for repetitive approach, can be executed multiple times and can be destroyed.

stmt, err := db.Prepare("select id, name from users where id = ?")
if err != nil {
    log.Fatal(err)
}
defer stmt.Close() // closing the statement
rows, err := stmt.Query(1)

And you are using interfaces 

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...))
}

which can take any type of parameter under the hood which can be vulnerable

For more detailed information Go for this Link

Upvotes: 3

user9455968
user9455968

Reputation:

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

enter image description here

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.

Upvotes: 17

Related Questions