doumi7861 2017-11-04 17:54
浏览 133
已采纳

如何从数组动态生成SQL查询列名称和值?

I have about 20 columns in one row and not all columns are required to be filled in when row created also i dont want to cardcode name of every column in SQL query and on http.post request on frontend. All values are from form. My code:

var colNames, values []string

for k, v := range formData {
    colNames = append(colNames, k)
    values = append(values, v)
}

Now i have 2 arrays: one with column names and second with values to be inserted. I want to do something like this:

db.Query("insert into views (?,?,?,?,?,?) values (?,?,?,?,?,?)", colNames..., values...)

or like this:

db.Query("insert into views " + colNames + " values" + values)

Any suggestions? Thanks!

  • 写回答

1条回答 默认 最新

  • dsafew1231 2017-11-04 18:59
    关注

    I assume your code examples are just pseudo code but I'll state the obvious just in case.

    db.Query("insert into views (?,?,?,?,?,?) values (?,?,?,?,?,?)", colNames..., values...)
    

    This is invalid Go since you can only "unpack" the last argument to a function, and also invalid MySQL since you cannot use placeholders (?) for column names.

    db.Query("insert into views " + colNames + " values" + values)
    

    This is also invalid Go since you cannot concatenate strings with slices.


    You could fromat the slices into strings that look like this:

    colNamesString := "(col1, col2, col3)"
    valuesString   := "(val1, val2, val3)"
    

    and now your second code example becomes valid Go and would compile but don't do this. If you do this your app becomes vulnerable to SQL injection and that's something you definitely don't want.


    Instead do something like this:

    // this can be a package level global and you'll need
    // one for each table. Keep in mind that Go maps that
    // are only read from are safe for concurrent use.
    var validColNames = map[string]bool{
         "col1": true,
         "col2": true,
         "col3": true,
         // ...
    }
    
    // ...
    
    var colNames, values []string
    var phs string // placeholders for values
    
    for k, v := range formData {
        // check that column is valid
        if !validColNames[k] {
            return ErrBadColName
        }
        colNames = append(colNames, k)
        values = append(values, v)
        phs += "?,"
    }
    
    if len(phs) > 0 {
        phs = phs[:len(phs)-1] // drop the last comma
    }
    phs = "(" + phs + ")"
    
    colNamesString := "(" + strings.Join(colNames, ",") + ")"
    query := "insert into views " + colNamesString + phs
    db.Query(query, values...)
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 安装gcc遇到的问题
  • ¥15 求购HI3519AV100开发板
  • ¥15 请问1553 RT怎么测试,没有BC有方法吗
  • ¥100 业务编程如何选择学习方向和内容?
  • ¥15 wamp3.3.5安装完成后图标正常显示绿色,鼠标左右键点击图标均无反应。求解决方法。
  • ¥15 鼠标点击的这条记录了什么?
  • ¥15 在写pid调速的程序时,电机始终维持最大速度
  • ¥15 【有偿】调用DXGI截图初始化失败,提示0xf 887a0004
  • ¥15 请问如何查看手机root记录?
  • ¥15 商城小程序订单号重复