与go-gorm框架一起使用的动态查询

Question

我正在编写一个动态查询，并使用go-gorm的db.Raw()函数执行它。我想要防止SQL注入攻击我已经构建的查询。

我写这个查询，以获得所有用户与服务器端分页，搜索和过滤。我的查询运行得非常完美，但它有SQL注入攻击的威胁。

// GetUserGridDataWithPagination - gets data to show in users grid to admin with pagination
func (controller Admin) GetUserGridDataWithPagination(
    filterBy string,
    searchBy string,
    sortBy string,
    sortOrder string,
    pageSize uint16,
    pageNumber uint16,
) ([]model.AdminUserGridData, int64, uint16, error) {
    var list []model.AdminUserGridData
    query := `SELECT * FROM users_master`
    query1 := `SELECT count(*) FROM users_master`
    clause := ` WHERE `
    filterCondition := ""
    searchCondition := ""
    sortCondition := ""
    if filterBy != "all" {
        filterCondition = ` WHERE role = '` + filterBy + `'`
        clause = ` AND `
    }
    if searchBy != "" {
        search := "'%" + searchBy + "%'"
        searchCondition = clause +
            `name ilike ` + search + ` OR
                email ilike ` + search + ` OR
                phone ilike ` + search + ` OR
                profession ilike ` + search + ` OR
                role ilike ` + search + ` OR
                kyc_status ilike ` + search
    }
    if sortBy != "" {
        column := ""
        if sortBy == "kycStatus" {
            column = "kyc_status"
        } else {
            column = sortBy
        }
        if sortOrder != "" {
            sortCondition = ` ORDER BY ` + column + ` ` + sortOrder
        }
    }
    if filterCondition != "" {
        query = query + filterCondition
        query1 = query1 + filterCondition
    }
    if searchCondition != "" {
        query = query + searchCondition
        query1 = query1 + searchCondition
    }
    if sortCondition != "" {
        query = query + sortCondition
    }
    query = query + ` LIMIT ? OFFSET ?`
    // fetch records from database
    if err := controller.database.Raw(query, pageSize, (pageSize * (pageNumber - 1))).Scan(&list).Error; err != nil {
        log.Error(err)
        return nil, 0, 0, errors.New("Error while processing your request")
    }
    // fetch total no of records from database
    type RowCount struct {
        Count int64 `json:"count"`
    }
    var rowCount RowCount
    if err := controller.database.Raw(query1).Scan(&rowCount).Error; err != nil {
        log.Error(err)
        return nil, 0, 0, errors.New("Error while processing 
your request")
    }
    return list, rowCount.Count, pageNumber, nil
}

在我的项目中，我已经做过很多次了。因此，我正在寻找一种方法来纠正这个问题，而不需要更改查询，而是使用任何第三方库来纠正这个问题。(就像我们在nodejs中使用可从npm获得的sql-escape-string包一样)

Answer 1

您应该使用gorm查询构建器并在args：Where(query interface{}, args ...interface{}) *DB中传递参数。这将是足够的。由于类似上面的gorm方法正在改变您正在构建的SQL查询的内部状态，因此可以编写如下代码：

var usersMasterList []UsersMaster // gorm Model
if filterBy != "all" {
    controller.database.Where("role = ?", filterBy)
}
if searchBy != "" {
    controller.database.Or("name ilike ?", search)
    controller.database.Or("email ilike ?", search)
    //...
}
//...
controller.database.Find(&usersMasterList)

代码未测试。你可以在这里阅读更多内容：http://gorm.io/docs/query.html

或者，您可以只使用regexp从这些字符串中过滤非字母数字字符：

rx := regexp.MustCompile("[^a-zA-Z0-9]+")
yourString = rx.ReplaceAllString(yourString, "")

强烈建议尽快重写你的代码，代码会更安全，更具可读性。