我应该在Sql数据库查询中使用Sql.Stmt还是字符串？

Question

Hi in database/sql package I can execute a query in two ways:

• First way: using Sql.Stmt

  var DeletePermissionStmt *sql.Stmt
  DeletePermissionStmt, err = database.Prepare(`DELETE FROM permission WHERE permission_id=$1`)
  
  if err != nil {
       log.Errorf("can't prepare delete permission statement: %s", err.Error())
  }
  
  transaction, err := database.Begin()  // assume postgres database is defined previously
  
  if err != nil {
       log.WithFields(logFields).Errorf("can't start transaction: %s", err.Error())
  
  
  return err
  }
   _, err := transaction.Stmt(DeletePermissionStmt).Exec(permission_id)
  

• Second way: using string

  var DeletePermissionStmt string
  DeletePermissionStmt = `DELETE FROM permission WHERE permission_id=$1`
  
  transaction, err := database.Begin()  // assume postgres database is defined previously
  
  if err != nil {
  log.WithFields(logFields).Errorf("can't start transaction: %s", err.Error())
  
  
  return err
  }
   _, err := transaction.Exec(DeletePermissionStmt,permission_id)
  

The only difference That I know that its is not possible to use sql.Stmt when you are returning something for example Insert Into FOO(f1,f2,f3) Values(v1,v2,v3) returning f_id Is there any other differences? and when should I use each one?

Answer 1

Using the Stmt helps you avoid sql injection from the user.

From wikipedia:

Prepared statements are resilient against SQL injection, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.