tortoise / tortoise/tortoise-orm

Escaping JSON data is incorrect for UPDATEs

Open
#377 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug
Dominant language
Python
Stars
5.6k
Forks
516
Avg merge
2d 21h
Merged PRs (30d)
9

Description

Tortoise 0.16.10, Postgresql 12, JSONB column created with fields.JSONField().

When inserting data initially with model.create, the prepared statement passes the data correctly:

INSERT INTO "usermodel" (...) VALUES (...): 
...
 '{"attributes":{"cn":["William Bush"],"createTimestamp":["2020-04-30 16:20:02+00:00"],"creatorsName":["cn=admin,dc=example,dc=org"],"description":["Lt. William Bush"],
...

This inserts a dict with a bunch of data, as expected.

Updating the data generates incorrectly escaped SQL whether using QuerySet.update or model.save.

Queryset:

UPDATE "usermodel" SET "ldap"='"{\n    \"attributes\": {\n        \"cn\": [\n            \"William Bush\"\n        ],\n        \"createTimestamp\": [\n            \"2020-04-30 16:20:02+00:00\"\n        ],\n   

...

Model:

UPDATE "usermodel" SET "ldap"=$1 WHERE "ident"=$2: ['"{\\n    \\"attributes\\": {\\n        \\"cn\\": [\\n            \\"William Bush\\"\\n        ],\\n        \\"createTimestamp\\": [\\n            \\"2020-04-30 16:20:02+00:00\\"\\n        ],\\n        \\"creatorsName\\": [\\n            \\"cn=admin,dc=example,dc=org\\"\\n        ],

...

As you can see both update statements are wrong: instead of inserting a JSON document, they end up inserting a string. Since a string is a valid JSON document, the update is successful but when attempting to read the data later it's trash.

In the Queryset case since it's not using a prepared statement I guess it has to attempt to escape the string. Is it possible to change it to use prepared statements?

In the model case since it's using a prepared statement there is no reason to quote the data; it should be passed untouched like INSERT does.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Reproduce the issue with a PostgreSQL JSONB field created through fields.JSONField(), comparing model.create with QuerySet.update and model.save. Trace how each update path serializes the value and verify that the completed update stores a JSON object that reads back as the original data rather than a JSON string.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, python
Domain
backend, databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.