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

Question

您好，在 database/sql 包中，我可以通过两种方式执行查询:

• 第一种方式:使用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)
  

• 第二种方式:使用字符串

  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)
  

唯一的区别我知道当你返回一些东西时不可能使用 sql.Stmt 例如 Insert Into FOO(f1,f2,f3) Values(v1,v2,v3) returning f_id还有其他区别吗？我应该什么时候使用它们？

Answer 1

使用 Stmt 可以帮助您避免来自用户的 sql 注入(inject)。

来自 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.