Dynamic query to use with go-gorm framework

Question

I am writing a dynamic query and executing it using go-gorm's db.Raw() function. I want to prevent SQL Injection attacks on the query I have build.

I am writing this query to get all users with server side pagination, search and filter. My query is working very perfect but it has a threat of SQL Injection attack.

// 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
}

I have done this many times in my project. So, I am finding a way to correct this without changing the query but using any third party library to correct this. ( like we do in nodejs using sql-escape-string package available at npm)

asyndrige asyndrige · Accepted Answer · 2019-01-17T07:20:07

You should use gorm query builder and pass parameters in args: Where(query interface{}, args ...interface{}) *DB. It will be enough. Since gorm methods like above are changing the inner state of SQL query you are building, its possible to write code like this:

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)

Code not tested. You can read more here: http://gorm.io/docs/query.html

Or you can just filter non-alphanumeric chars from these strings with regexp:

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

Strongly recommend to rewrite your code as soon as possible, the code will be more safe and readable.

Dynamic query to use with go-gorm framework

1 Answers