Updating json field in Postgres

Question

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

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

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

The result in database is 'b' containing an escaped string rather than a real json object:

{"a": "1", "b": "{\"name\":\"steve\"}"}

I have tried different ways to unescape or dump my json in order to keep 'b' an object, but couldn't find a solution.

Thank you

Answer 1

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)

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__...

... 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.