如何使用参数化查询按列名搜索列

Question

我的用户应该能够通过在输入框中输入搜索词并从下拉框中选择他们想要搜索的列来搜索数据库。

但是，我找不到在golang中参数化查询的方法，以便将列名识别为列名而不是值。我举个例子:

rows, err := db.Query("SELECT * FROM mytable WHERE ? = ?", col, searchStr)

我打开MySQL日志记录，结果如下:

   41 Execute   SELECT *
    FROM mytable 
    WHERE 'Username' = 'Foo'

显然Username不应该有单引号。

如何参数化这个查询?我是否必须编写应用程序逻辑来检查每个查询是否有正确的列名，并使用字符串操作将列名添加到查询中(可能使用一些占位符字符来查找和替换)?

Answer 1

这是为了帮助你远离坏参数(防止SQL注入之类的事情)，但不是为了替换值以外的任何东西而设计的。您希望它插入一个表名。不幸的是，你的代码意识到col的类型(字符串)，并引用它，因为在SQL中它是nvarchar，这就是他们的文字是如何写的，在单引号中封闭。可能看起来有点像黑客，但你需要这个代替;

db.Query(fmt.Sprintf("SELECT * FROM mytable WHERE %s = ?", col), searchStr)

在将表名传递给Query之前将其放入查询字符串中，因此它不会被视为参数(即在where子句中使用的值)。

Answer 2

您应该看看这个包。https://github.com/gocraft/dbr

对你想做的事来说很好。

import "github.com/gocraft/dbr"
// Simple data model
type Suggestion struct {
    Id        int64
    Title     string
    CreatedAt dbr.NullTime
}
var connection *dbr.Connection
func main() {
    db, _ := sql.Open("mysql","root@unix(/Applications/MAMP/tmp/mysql/mysql.sock)/dbname")
    connection = dbr.NewConnection(db, nil)
    dbrSess := connection.NewSession(nil)
    // Get a record
    var suggestion Suggestion
    err := dbrSess.Select("id, title").From("suggestions").
        Where("id = ?", 13).
        LoadStruct(&suggestion)
    if err != nil {
             fmt.Println(err.Error())
    } else {
             fmt.Println("Title:", suggestion.Title)
    }
}

Answer 3

按参数名查询或执行参数。

/*Examples:
  ParamReplacingMode = 0  // no replacing params
  isql:=InitSql(db,"SELECT * FROM table WHERE price+vat>:Sum and country=:C") // by Oracle
  Params(isql,
    ":C", "USA",                    // the order for Params is not bound, you can add params any order, case insensitive
    ":Sum", 130.5)
  res,err:= SqlQuery(isql)          // result: db.Query("SELECT * FROM table WHERE price+vat>:Sum and country=:C", 130.5,"USA")
  if err!=nil {
    fmt.Println("Error:"+fmt.Sprint(err)+SqlStr(isql))
  }
or
  ParamReplacingMode = 1  // MySQL  - replacing params to "?"
  isql:=InitSql(db,"SELECT * FROM table WHERE price+vat>:Sum and country=:C") // by Oracle convert to Mysql
  Params(isql,
    ":C", "USA",                    // the order for Params is not bound, you can add params any order, case insensitive
    ":Sum", 130.5)
  res,err:= SqlQuery(isql)          // result: db.Query("SELECT * FROM table WHERE price+vat>? and country=?", 130.5,"USA") //replacing params to "?"
or
  ParamReplacingMode = 0  //no replacing params
  isql:=InitSql(db,"SELECT * FROM table WHERE price+vat>$1 and country=$2")   // by Postgre
  AddParam(isql,"$1", 130.5)        // AddParam() is the same as Params(), but only for 1 param
  AddParam(isql,"$2", "USA")        // the order for AddParams is not bound, you can add params any order
  res,err:= SqlQuery(isql)          // result: db.Query("SELECT * FROM table WHERE price+vat>$1 and country=$2", 130.5,"USA")
or
  ParamReplacingMode = 2  // mode Oracle to Postgre, replacing params to <$Number>
  isql:=InitSql(db,"SELECT * FROM table WHERE price+vat>:Sum and country=:C") // by Oracle convert to Postgre
  AddParam(isql,":C","USA")        // AddParam()) is the same as Params(), but only for 1 param
  AddParam(isql,":Sum",130.5)
  res,err:= SqlQuery(isql)         // result: db.Query("SELECT * FROM table WHERE price+vat>$1 and country=$2", 130.5,"USA")
SqlExec() is similar as SqlQuery(), but call db.Exec(...)
  Example , add NULL value:
  isql:=InitSql(db,"INSERT INTO table (id, name) VALUES (:ID,:Name)" )
  Params(isql, ":ID", 1, ":Name", nil)     //NULL
  res,err:= SqlExec(isql)
  if err!=nil {
    fmt.Println("Error:"+fmt.Sprint(err)+SqlStr(isql))
  }
*/
type (
    TisqlMode int32
    TisqlAt   struct {
        ParamName string
        Pos       int
        ParamVal  any
    }
    Tisql struct {
        Sql       string
        ResultSql string
        DB        *sql.DB
        Params    map[string]any
        At        []TisqlAt
    }
)
const (
    Oracle  TisqlMode = iota //0, no replacing params
    Mysql                    //1, "SELECT * FROM table WHERE price+vat>:Sum and country=:C" -> db.Query("SELECT * FROM table WHERE price+vat>? and country=?", 130.5,"USA")
    Postgre                  //2, "SELECT * FROM table WHERE price+vat>:Sum and country=:C" -> db.Query("SELECT * FROM table WHERE price+vat>$1 and country=$2", 130.5,"USA")
)
var ParamReplacingMode TisqlMode = -1 //-1 = unknown,  0 = no replacing params,  1 = to MySql,  2 = to Postgre
func indexAt(pStr, pSubStr string, pFrom ...int) int { //Index from position
    pos := 0
    if len(pFrom) > 0 {
        pos = pFrom[0]
    }
    if pos >= len(pStr) {
        return -1
    }
    if pos < 0 {
        pos = 0
    }
    idx := strings.Index(pStr[pos:], pSubStr)
    if idx > -1 {
        idx += pos
    }
    return idx
}
func InitSql(db *sql.DB, sql string) *Tisql {
    if ParamReplacingMode < 0 { // unknow
        _, err := db.Exec("?")
        if err != nil {
            s := strings.ToLower(fmt.Sprint(err))
            if indexAt(s, "mysql") > 0 {
                ParamReplacingMode = 1
            } else {
                ParamReplacingMode = 0
            }
        }
    }
    var isql Tisql
    isql.Sql = sql
    isql.DB = db
    isql.Params = make(map[string]any)
    return &isql
}
func AddParam(isql *Tisql, pParam string, pValue any) bool {
    pParam = strings.TrimSpace(pParam)
    if len(pParam) > 1 {
        isql.Params[pParam] = pValue
        return true
    }
    return false
}
func Params(isql *Tisql, pParam ...any) (added int) { //https://stackoverflow.com/questions/6996704/how-to-check-variable-type-at-runtime-in-go-language
    c := len(pParam)
    if c%2 == 1 {
        c = c - 1
    }
    cnt, i, s := 0, 0, ""
    for i < c {
        s = strings.TrimSpace(pParam[i].(string))
        if len(s) > 1 {
            isql.Params[s] = pParam[i+1]
            cnt++
        }
        i += 2
    }
    return cnt
}
func paramOrder(isql *Tisql, pCheckParamCount bool) error {
    var at TisqlAt
    isql.ResultSql = isql.Sql
    t := ""
    b := strings.ToLower(isql.Sql) + " "
    mMode := ParamReplacingMode
    var p, p1, p2 int
    for name, v := range isql.Params {
        p1 = 0
        for p1 >= 0 {
            p = indexAt(b, strings.ToLower(name), p1)
            if p < 0 {
                p1 = -1
                continue
            } else {
                p2 = p + len(name)
                t = b[p2 : p2+1] //char after param
                if indexAt(" :,;!?%$<>^*+-/()[]{}=|'`"rnt", t) < 0 {
                    p1 = p + 1
                    continue
                }
                p1 = -1
            }
        }
        if p >= 0 {
            at.Pos = p
            at.ParamVal = v
            at.ParamName = name
            isql.At = append(isql.At, at)
        }
    }
    if pCheckParamCount && len(isql.At) != len(isql.Params) {
        return fmt.Errorf("Different count of params %d / %d", len(isql.At), len(isql.Params))
    }
    if len(isql.At) > 1 {
        sort.Slice(isql.At,
            func(i, j int) bool {
                return isql.At[i].Pos < isql.At[j].Pos
            })
    }
    mLen := len(isql.Sql)
    switch mMode {
    case 1: //to Mysql
        {
            p1, p2, s := 0, 0, ""
            for _, at := range isql.At {
                p2 = at.Pos
                if p2 >= 0 && p2 <= mLen {
                    if p2 > p1 {
                        s += isql.Sql[p1:p2] + "?"
                    }
                    p1 = p2 + len(at.ParamName)
                }
            }
            if p1 < len(isql.Sql) {
                s += isql.Sql[p1:len(isql.Sql)]
            }
            isql.ResultSql = s
        }
    case 2: //to Postgre
        {
            p1, p2, s := 0, 0, ""
            for i, at := range isql.At {
                p2 = at.Pos
                if p2 >= 0 && p2 <= mLen {
                    if p2 > p1 {
                        s += isql.Sql[p1:p2] + "$" + fmt.Sprint(i+1)
                    }
                    p1 = p2 + len(at.ParamName)
                }
            }
            if p1 < len(isql.Sql) {
                s += isql.Sql[p1:len(isql.Sql)]
            }
            isql.ResultSql = s
        }
    }
    return nil
}
func ParamsStr(isql *Tisql) string {
    s := ""
    for i, at := range isql.At {
        s += "[" + fmt.Sprint(i+1) + ". " + at.ParamName + "="" + fmt.Sprint(at.ParamVal) + ""]"
    }
    return s
}
func SqlStr(isql *Tisql) string {
    s := " SQL:[" + isql.ResultSql + "]"
    if len(isql.At) > 0 {
        s += " Params:" + ParamsStr(isql)
    }
    return s
}
func SqlExec(isql *Tisql, opt ...bool) (sql.Result, error) {
    checkParamCount := false
    if len(opt) > 0 {
        checkParamCount = opt[0]
    }
    err := paramOrder(isql, checkParamCount)
    if err != nil {
        return nil, err
    }
    mLen := len(isql.At)
    mVal := make([]any, mLen)
    for i := range mVal {
        mVal[i] = isql.At[i].ParamVal
    }
    return isql.DB.Exec(isql.ResultSql, mVal...)
}
func SqlQuery(isql *Tisql, opt ...bool) (*sql.Rows, error) {
    checkParamCount := false
    if len(opt) > 0 {
        checkParamCount = opt[0]
    }
    err := paramOrder(isql, checkParamCount)
    if err != nil {
        return nil, err
    }
    mLen := len(isql.At)
    mVal := make([]any, mLen)
    for i := range mVal {
        mVal[i] = isql.At[i].ParamVal
    }
    return isql.DB.Query(isql.ResultSql, mVal...)
}