Prevent SQL Injection in Go Postgresql

Question

I research around the forum of postgresql injection in Go and I found some useful information in SQL injection like below:

How to execute an IN lookup in SQL using Golang?

How can I prevent SQL injection attacks in Go while using "database/sql"?

but I still need some advice because my code in Go is using a different kind of code and usecases. some usecase/question i need advice for are like this

1. Using query looping to multiple insert like INSERT INTO a (a1,a2,a3) VALUES (%d,%d,%s) using fmt.Sprintf, I know using sprinft is bad. so is there any solution for this loop query for insert ? Ex: INSERT INTO a (a1,a2,a3) VALUES (%d,%d,%s),(%d,%d,%s),(%d,%d,%s)
2. Is it safe to use fmt.Sprintf to generate query if the param is using %d instead of %s ?
3. Using Prepare statement and Query is safe, but what if I'm using function Select (using $1,$2) and function NamedQuery (using struct named.) Ex: Select * from a where text = $1 -> is using this $1 safe ? and Ex : Select * from a where text = :text -> is this safe in function NamedQuery?

Kindly need your advice guys. Thank you!

Answer 1

Firstly, usually prefer to use the db placeholders ? $1 etc.

1. Yes it is safe to use fmt.Sprintf with integer parameters to build SQL, though worth avoiding if you can, but your third param is %s - avoid that and use ?
2. Yes it is safe to use fmt.Sprintf with integer parameters, but %s or %v is far more risky and I'd avoid, can't think why you'd need it.
3. Use placeholders here, then yes it is safe.

General rules:

• Use placeholders by default, it should be rare to use %d (as in your IN query for example)
• Parse params into types like integer before any validation or use
• Avoid string concat if you can, and be particularly wary of string params
• Always hard code things like column and table names, never generate them from user input (e.g. ?sort=mystringcolname)
• Always validate that the params you get are only those authorised for that user