#34553: Can't create CheckConstraint with percent characters in values on
postgresql due to broken quoting
-------------------------------------+-------------------------------------
Reporter: Thomas | Owner: nobody
Kolar |
Type: Bug | Status: new
Component: Database | Version: 4.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
It is currently impossible to create check constraints that reference
values containing percent characters (`%`).
Consider the following model definition:
{{{#!python
from django.db import models
class Dolor(models.Model):
sit = models.CharField(max_length=42069)
class Meta:
constraints = (
models.CheckConstraint(
check=models.Q(sit="%"),
name="amet",
),
)
}}}
This will indeed result in a constraint being created. However, the
percent character will be doubled, and constrain `sit` to contain `%%`,
not `%`:
{{{#!python
>>> from ipsum.models import *
>>> Dolor.objects.create(sit="%")
Traceback (most recent call last):
(... snip ...)
psycopg2.errors.CheckViolation: new row for relation "ipsum_dolor"
violates check constraint "amet"
DETAIL: Failing row contains (1, %).
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
(... snip ...)
django.db.utils.IntegrityError: new row for relation "ipsum_dolor"
violates check constraint "amet"
DETAIL: Failing row contains (1, %).
>>> Dolor.objects.create(sit="%%")
<Dolor: Dolor object (2)>
>>>
}}}
This may be a super contrived example, but using an `__in` lookup in the
check with a list of allowed values also does not work (`choices` are not
a suitable alternative here as that isn't enforced at the DB level).
This was likely introduced with the fix for #30484 - here, the schema
editor's `quote_value` method was changed to always double percent
characters.
I'd also like to note that creating a `CheckConstraint` like this DOES
work in Django 2.2. Likely, that stopped being the case when #30060 was
fixed. I can attest that this is a serious roadblock when moving a project
from 2.2 to a modern and supported version of Django.
Honestly, I'm surprised that this doesn't break in way more instances.
"Quoting" like this should only be correct specifically for `LIKE` (and
the likes of it).
Suggested fix: remove the percentage character doubling from the general-
purpose escaping method, and reintroduce it in a targeted way where
needed.
I unfortunately don't know enough about the ORM's internals to make a more
detailed suggestion, or know for just how much headache creation I am to
apologize. But the current behavior is obviously incorrect.
Additional notes:
- This affects django 3.2 as well
- This almost certainly affects the oracle backend as well
- This works as expected on sqlite3
I have attached a sample django project that reproduces the issue.
Steps to reproduce (require docker installation):
- Spin up a postgres instance: `docker run -e POSTGRES_USER=lorem -e
POSTGRES_PASSWORD=lorem -e POSTGRES_DB=lorem -p 5432:5432 postgres`
- Extract `lorem.zip`
- Create a virtual environment
- Install `django==4.2.1`, `psycopg2-binary==2.9.6`
- Activate the virtual environment
- Extract `lorem.zip` and change into django directory
- `python -m manage migrate`
- `python -m manage shell`
- `from ipsum.models import *`
- `Dolor.objects.create(sit="%")`
- expected behavior: This should return a model instance
- actual behavior: exception (as described above)
- `Dolor.objects.create(sit="%%").sit`
- expected behavior: exception (as described above)
- actual behavior: returns "%%"
--
Ticket URL: <https://code.djangoproject.com/ticket/34553>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018800a80914-6bb2702a-174e-4072-96d6-c1d924738b6e-000000%40eu-central-1.amazonses.com.
