2

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!

M.S
  • 65
  • 1
  • 7
  • This might address a few of the details as well: https://medium.com/avitotech/how-to-work-with-postgres-in-go-bad2dabd13e4 – Jason Oct 03 '20 at 01:57

1 Answers1

6

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
Kenny Grant
  • 9,360
  • 2
  • 33
  • 47
  • Hi, thanks for the advice.. 1. I forgot to place the problem in question 1, can you please help me in this ? 2. Will do that, I'll try not to use %s 3. I see.. so this is safe right ? i thought if I'm using $1, they can inject something like `1 OR text = "asda"` Thank you! – M.S Oct 02 '20 at 11:44
  • They can't. When you're using the DB's placeholders (like `$1`) it's telling the DB "I'm passing the value of this parameter separately", and then it passes the value. That value is *only* a value, it is not treated as a part of the query; that's why it makes SQL injection impossible. – Adrian Oct 02 '20 at 13:25
  • Adrian is correct, you're safe from injection with $1 (though you can still introduce vulnerabilities when using it by trusting user input, for example allowing access to sort modes or search queries you didn't expect if you naively use user input for order clauses, selecting tables etc - e.g. you take param sort and use it directly for order, letting users sort records on super_secret_key field and extract information). – Kenny Grant Oct 02 '20 at 16:18
  • Ohh i see... thanks guys! Need a bit of advice on a problem, so now I have a query insert which I repetitively append based on len of items like `insert into a (a1,a2) values (%d,%s) ,(%d,%s)` which the %d,%s is repetitive, is there any solution to this ? Or do I just repetitive append this like `values ($1,$2), ($3,$4)` or any other idea ? i try search like `values (?,?), (?,?)` and It doest work on me when i tested it – M.S Oct 04 '20 at 16:55
  • Yes $1 $2 etc should work. Different methods/dbs require different placeholders so check that. – Kenny Grant Oct 05 '20 at 06:37