如何防止PostgreSQL JSON/JSONB字段中的SQL注入? [英] How to prevent SQL Injection in PostgreSQL JSON/JSONB field?
问题描述
如何进行防止在使用"database/sql"时在Go中进行SQL注入攻击?
这可以解决单值字段问题,因为您可以删除引号,但是我不能过滤JSON/JSONB字段,如下所示,因为 $ 1
被视为字符串:
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"}'`
以下方法可行,但很容易进行SQL注入:
The following works but it's prone to SQL Injection:
`SELECT * FROM foo WHERE bar @> '{"baz": "` + "qux" + `"}'`
我该如何解决?
在@mkopriva发表评论后
如何使用 jsonb _ *
函数构建此json [{"foo":$ 1}]
?尝试了以下方法,但未成功:
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
没有sql错误.筛选器不起作用.有一种方法可以检查内置的sql吗?我正在使用 database/sql
本机库.
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.
推荐答案
这是您要寻找的吗?
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)
}
}
请注意, Exec
不可用于检索(尽管我为您保留了它,因此解决方案与您的示例相符).在 db.Query
中查看(此处的精彩教程: http://go-database-sql.org/retrieving.html )
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)
这篇关于如何防止PostgreSQL JSON/JSONB字段中的SQL注入?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!