Prevent SQL injection

Question

Question 1:

I have the below MySQL query which works fine but I've just discovered this is not a safe approach as its open to SQL injection. As you can see the where clause is an issue if I wanted to pass as an argument.

_, err := dbmap.Select(&response.AppsData, "SELECT...", ?)

Any advice much appriciated.

where := ""

for i := 0; i < (len(acl_user_apps)); i++ {
    fmt.Println(acl_user_apps[i].AppId)
    fmt.Println(acl_user_apps[i].Permissions)

    if where == "" {
        where = "WHERE Apps.id=" + strconv.Itoa(acl_user_apps[i].AppId)
    } else {
        where = where + " OR Apps.id=" + strconv.Itoa(acl_user_apps[i].AppId)
    }
}

query := "SELECT Apps.*, GROUP_CONCAT(DISTINCT IFNULL(AppCategoryMatches.category_id,'-1') SEPARATOR ',') as temp,   GROUP_CONCAT(DISTINCT IFNULL(AppCategories.category_name,'-1') SEPARATOR ',') as tmp_name FROM Apps LEFT JOIN AppCategoryMatches ON AppCategoryMatches.app_id=Apps.id LEFT JOIN AppCategories ON (AppCategoryMatches.`category_id` = AppCategories.id) " + where + " GROUP BY Apps.id ORDER BY " + sort_by + " " + order_by + " LIMIT " + limit + " OFFSET " + offset)
_, err := dbmap.Select(&response.AppsData,query)

Question 2: Also just wondering if anyone has ever had issues passing ORDER argument...

_, err := dbmap.Select(&response.AppsData,
        "SELECT Apps.*, GROUP_CONCAT(DISTINCT IFNULL(AppCategoryMatches.category_id,'-1') SEPARATOR ',') as temp, GROUP_CONCAT(DISTINCT IFNULL(AppCategories.category_name,'-1') SEPARATOR ',') as tmp_name FROM Apps LEFT JOIN AppCategoryMatches ON AppCategoryMatches.app_id=Apps.id LEFT JOIN AppCategories ON (AppCategoryMatches.category_id = AppCategories.id) GROUP BY Apps.id ORDER BY ?", "title")

This ORDER is the simplest thing ever... why isnt it working?

Answer 1

You absolutely don't want to be "escaping" any strings on your own, nor concatenating strings to make queries.

1. Go's database/sql (http://golang.org/pkg/database/sql/) package supports parameterised queries by default - e.g. db.Query("SELECT * FROM users WHERE id=? AND active=?", id, userStatus) - where ? acts as a placeholder for mySQL to handle your variables.

2. You can (in combination with parameterised queries) use a query builder like mgutz/dat that can help if you're not great at writing raw SQL. A package like that or sqlx also helps pack/unpack queries to/from structs or maps in your application.

There's also a great guide in this tutorial for using Go's database package. I highly suggest reading it.

Answer 2

I don't know Go language. But most of languages have function for escaping strings (PHP example: http://php.net/manual/en/function.mysql-real-escape-string.php). If you're inserting only integers to your query, you only need to convert values from string to int.

Check this out: http://astaxie.gitbooks.io/build-web-application-with-golang/content/en/09.4.html maybe you'll find some answers.

And about your ORDER - can you put here complete sql query that you're calling?