SQL操作

SQL操作

1.【必须】SQL语句默认使用预编译并绑定变量

• 使用database/sql的prepare、Query或使用GORM等ORM执行SQL操作

  import (
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
  )

  type Product struct {
    gorm.Model
    Code string
    Price uint
  }
  ...
  var product Product
  db.First(&product, 1)

• 使用参数化查询，禁止拼接SQL语句，另外对于传入参数用于order by或表名的需要通过校验

// bad
  import (
      "database/sql"
      "fmt"
      "net/http"
  )

  func handler(db *sql.DB, req *http.Request) {
      q := fmt.Sprintf("SELECT ITEM,PRICE FROM PRODUCT WHERE ITEM_CATEGORY='%s' ORDER BY PRICE",
          req.URL.Query()["category"])
      db.Query(q)
  }

// good
func handlerGood(db *sql.DB, req *http.Request) {
    //使用?占位符
      q := "SELECT ITEM,PRICE FROM PRODUCT WHERE ITEM_CATEGORY='?' ORDER BY PRICE"
      db.Query(q, req.URL.Query()["category"])
}