在Postgres中更新json字段 [英] Updating json field in Postgres
问题描述
通过json字段查询Postgres 9.3确实很棒. 但是我找不到正式的方法来更新json对象,为此我使用了基于以前的帖子(
Querying Postgres 9.3 by json field is really great. However i couldn't find a formal way to update the json object, for which i use an internal function written in plpythonu based on previous post (How do I modify fields inside the new PostgreSQL JSON datatype?):
CREATE OR REPLACE FUNCTION json_update(data json, key text, value json)
RETURNS json AS
$BODY$
from json import loads, dumps
if key is None: return data
js = loads(data)
js[key] = value
return dumps(js)
$BODY$
LANGUAGE plpythonu VOLATILE
当我的json更新保持平坦和简单时,它的效果非常好.假设聊天"是在"GO_SESSION"表中归档的json类型,并且包含 {"a":"1","b":"2"} ,以下代码将更改 'b'值,然后将聊天"设置为 {"a":"1","b":"5"}
It works really well when my json updates remains flatten and simple. Say "chat" is a json type filed in "GO_SESSION" table, and contains {"a":"1","b":"2"}, the following code will change 'b' value and turn "chat" to be {"a":"1","b":"5"}
update "GO_SESSION" set chat=json_update(chat,'b','5') where id=3
问题在于,当我尝试关联'b'另一个对象而不是简单的值时:
The problem is when i'm trying to assing 'b' another object rather than a simple value:
update "GO_SESSION" set chat=json_update(chat,'b','{"name":"steve"}') where id=3
数据库中的结果为'b',其中包含转义的字符串而不是实际的json对象:
The result in database is 'b' containing an escaped string rather than a real json object:
{"a":"1","b":"{\" name \:\" steve \}"}
我尝试了不同的方法来转义或转储json以保留对象"b",但找不到解决方案.
I have tried different ways to unescape or dump my json in order to keep 'b' an object, but couldn't find a solution.
谢谢
推荐答案
不需要eval
.您的问题是您没有将值解码为json对象.
No eval
is required. Your issue is that you're not decoding the value as a json object.
CREATE OR REPLACE FUNCTION json_update(data json, key text, value json)
RETURNS json AS
$BODY$
from json import loads, dumps
if key is None: return data
js = loads(data)
# you must decode 'value' with loads too:
js[key] = loads(value)
return dumps(js)
$BODY$
LANGUAGE plpythonu VOLATILE;
postgres=# SELECT json_update('{"a":1}', 'a', '{"innerkey":"innervalue"}');
json_update
-----------------------------------
{"a": {"innerkey": "innervalue"}}
(1 row)
不仅如此,而且使用eval
解码json
是危险且不可靠的.这是不可靠的,因为json
不是Python,它只是经常在很多时候对它进行评估.这是不安全的,因为您永远不知道自己正在评估什么.在这种情况下,您在很大程度上受到PostgreSQL的json解析器的保护:
Not only that, but using eval
to decode json
is dangerous and unreliable. It's unreliable because json
isn't Python, it just happens to evaluate a little bit like it much of the time. It's unsafe because you never know what you might be eval'ing. In this case you are largely protected by PostgreSQL's json parser:
postgres=# SELECT json_update(
postgres(# '{"a":1}',
postgres(# 'a',
postgres(# '__import__(''shutil'').rmtree(''/glad_this_is_not_just_root'')'
postgres(# );
ERROR: invalid input syntax for type json
LINE 4: '__import__(''shutil'').rmtree(''/glad_this_is_not_...
^
DETAIL: Token "__import__" is invalid.
CONTEXT: JSON data, line 1: __import__...
...但是如果有人可以将eval
漏洞利用过去,我将不会感到惊讶.因此,这里的教训是:不要使用eval
.
... but I won't be at all surprised if someone can slip an eval
exploit past that. So the lesson here: don't use eval
.
这篇关于在Postgres中更新json字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!