Prevent SQL injection

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?

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.

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?