How to prevent SQL Injection in PostgreSQL JSON/JSONB field?

Question

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.

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
– mkopriva, Commented Jan 9, 2019 at 20:42
Are there no prepared statements in Go? That's the king's way to avoiding SQL injection. — Laurenz Albe
– Laurenz Albe, Commented Jan 10, 2019 at 7:09
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-
– Ry- ♦, Commented Jan 10, 2019 at 7:35

syntaqx · Accepted Answer · 2019-01-10 07:32:06Z

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)

answered Jan 10, 2019 at 7:32

syntaqx

2,91627 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rafael Barros Over a year ago

I had to tweak a bit, and put a json tag to format the key in the struct, but it worked. Thanks. The @Ry-'s comment in the question also worked.

syntaqx Over a year ago

Glad you were able to get what you needed friend.

Collectives™ on Stack Overflow

How to prevent SQL Injection in PostgreSQL JSON/JSONB field?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related