2

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

This solves the single value field problem because you can remove the quotes, but I can't do that filtering a JSON/JSONB field, like in the following because the $1 is considered a string:

`SELECT * FROM foo WHERE bar @> '{"baz": "$1"}'`

The following works but it's prone to SQL Injection:

`SELECT * FROM foo WHERE bar @> '{"baz": "` + "qux" + `"}'`

How do I solve this?

EDITED after @mkopriva's comment:

How would I build this json [{"foo": $1}] with the jsonb_* functions? Tried the below without success:

jsonb_build_array(0, jsonb_build_object('foo', $1::text))::jsonb

There's no sql error. The filter just doesn't work. There's a way that I can check the builded sql? I'm using the database/sql native lib.

Rafael Barros
  • 1,043
  • 18
  • 25
  • 4
    By using `json_build_object`/`jsonb_build_object` and similar functions instead of constructing the json from strings. E.g. `... bar @> json_build_object("bar", $1)` – mkopriva Jan 09 '19 at 20:42
  • Are there no prepared statements in Go? That's the king's way to avoiding SQL injection. – Laurenz Albe Jan 10 '19 at 07:09
  • 2
    Remove the `0,` at the start of the `jsonb_build_array`; you’re creating `[0, {"foo": …}]`. Also, no need to cast `jsonb_build_array` to `jsonb`. – Ry- Jan 10 '19 at 07:35

1 Answers1

2

Is this what you're looking for?

type MyStruct struct {
    Baz string
}

func main() {
    db, err := sql.Open("postgres", "postgres://...")
    if err != nil {
        log.Panic(err)
    }

    s := MyStruct{
        Baz: "qux",
    }

    val, _ := json.Marshal(s)
    if err != nil {
        log.Panic(err)
    }

    if _, err := db.Exec("SELECT * FROM foo WHERE bar @> ?", val); err != nil {
        log.Panic(err)
    }
}

As a side note, Exec isn't for retrieval (although I kept it for you so the solution would match your example). Check out db.Query (Fantastic tutorial here: http://go-database-sql.org/retrieving.html)

syntaqx
  • 2,636
  • 23
  • 29