Prevent SQL injection in beego

Question

I am using a select query to fetch some rows from my table using the following code.

func (f *UserFilter) ListAllUsers(srch string) (cnt int64, l []*ListResp, err error) {
    o := orm.NewOrm()
    var args []interface{}
    var w string
    q := `SELECT * FROM users WHERE 1 = 1`
    if srch != "" {
        q += ` AND (LOWER(first_name) LIKE %?% OR LOWER(last_name) LIKE %?% OR id = ?)`
        args = append(args, srch, srch, srch)
    }
    _, err = o.Raw(q, args).QueryRows(&l)

    return
}

Though I'm using prepared statements to bind the values they are not properly escaped for fields first_name and last_name. For example, if the value srch is Tes't it will break the query. Is there any way to escape these values so that I can prevent SQL injection while using MySql driver?

Any help is much appreciated. Thanks in advance.

Answer 1

The % wildcards should be inside of the string, not outside of it, i.e. ... LIKE %'foo'% ... is invalid, ... LIKE '%foo%' ... is valid. More on LIKE.

func (f *UserFilter) ListAllUsers(srch string) (cnt int64, l []*ListResp, err error) {
    o := orm.NewOrm()
    var args []interface{}
    var w string
    q := `SELECT * FROM users WHERE 1 = 1`
    if srch != "" {
        q += ` AND (LOWER(first_name) LIKE ? OR LOWER(last_name) LIKE ? OR id = ?)`
        args = append(args, "%"+srch+"%", "%"+srch+"%", srch)
    }
    _, err = o.Raw(q, args...).QueryRows(&l)

    return
}

or using mysql's CONCAT:

func (f *UserFilter) ListAllUsers(srch string) (cnt int64, l []*ListResp, err error) {
    o := orm.NewOrm()
    var args []interface{}
    var w string
    q := `SELECT * FROM users WHERE 1 = 1`
    if srch != "" {
        q += ` AND (LOWER(first_name) LIKE CONCAT('%', ?, '%') OR LOWER(last_name) LIKE CONCAT('%', ?, '%') OR id = ?)`
        args = append(args, srch, srch, srch)
    }
    _, err = o.Raw(q, args...).QueryRows(&l)

    return
}