douliao7354
douliao7354
采纳率0%
2019-01-09 20:25

如何防止PostgreSQL JSON / JSONB字段中的SQL注入?

已采纳

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • duanhuang7591 duanhuang7591 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)

    点赞 评论 复制链接分享