31

Building my first web-app and want to understand SQL injection better (https://github.com/astaxie/build-web-application-with-golang/blob/master/en/eBook/09.4.md).

How much protection against SQL injection do I get from just always using the 'database/sql' library and constructing queries using '?' instead of concatting strings? What kind of SQL injection attacks will I still have to worry about in that case?

Shannon Matthews
  • 9,649
  • 7
  • 44
  • 75
John Montague
  • 1,910
  • 7
  • 21
  • 30

3 Answers3

46

As long as you're using Prepare or Query, you're safe.

// this is safe
db.Query("SELECT name FROM users WHERE age=?", req.FormValue("age"))
// this allows sql injection.
db.Query("SELECT name FROM users WHERE age=" + req.FormValue("age"))
OneOfOne
  • 95,033
  • 20
  • 184
  • 185
  • 6
    Exec also allow binding value without using Prepare. Is it safe, my guess is that it makes an implicite Prepare statement but I haven't look into the code – Fougere Oct 18 '14 at 10:07
  • 1
    is there a way to make `fmt.Sprintf` safe. – Thellimist Dec 18 '15 at 00:14
  • @Entei not really, unless you manually process every param, there's no reason not to use prepared queries though. – OneOfOne Dec 18 '15 at 22:10
  • @OneOfOne just wondered if it is possible to use the printf style of string manuplation. Thank you. – Thellimist Dec 18 '15 at 23:14
  • does the same apply with db.Exec? for example: `db.Exec("DELETE from books where pk = ?", r.FormValue("pk"))` – awm Aug 31 '17 at 17:41
  • what about this in golang? https://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection – Hasibul Hasn Apr 30 '18 at 12:12
  • @AnikHasibul I'm not 100% sure, however Go generally handles that kind of things much much much better than php. – OneOfOne Apr 30 '18 at 15:57
  • 1
    @OneOfOne I'm no expert in DBs, I found this issue discussion in the go posgres /lib/pq. Where they discuss that even using Query there are some vulnerabilities? https://github.com/lib/pq/issues/248 – CommonSenseCode Sep 14 '18 at 20:24
7

I agree with @Oneonone's answer.

If you are retrieving data, do something like:

db.Query("SELECT name FROM users WHERE age=?", req.FormValue("age"))

If you have to insert a lot of data safely, using the same query, this is where Prepare comes handy. you can do something like this:

tx, err := db.Begin()
if err != nil {
    return nil,err
}
stmt, err := tx.Prepare("INSERT INTO users VALUES (?, ?)")
if err != nil {
    tx.Rollback()
    return nil,err
}
defer 
for i := 0; i < 10; i++ {
    _, err = stmt.Exec(i, "dummy")
    if err != nil {
        tx.Rollback()
        return nil,err
    }
}
err = tx.Commit()
if err != nil {
    stmt.Close()
    tx.Rollback()
    return nil,err
}
stmt.Close()
return someValue, nil

ref: https://stackoverflow.com/a/46476451/5466534

Sahith Vibudhi
  • 4,935
  • 2
  • 32
  • 34
-1

If prepared statements are not enough, you can use a query builder.

I created the hotcoal package, which helps you secure your handcrafted SQL against injection. It provides a minimal API and you can use it with any SQL library.

motrboat
  • 1