tortoise / tortoise/tortoise-orm
Escaping JSON data is incorrect for UPDATEs
Nobody has claimed this yet.
- 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
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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