Re: [Django] #35093: Allow SQL injection characters in QuerySet.annotate() aliases
Django
-------------------------------------+-------------------------------------
Reporter: Eyal Segal | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: ORM, annotations | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Eyal Segal:
Old description:
> Encountered a limitation in Django ORM when using annotations with column
> aliases containing special characters like apostrophes or spaces. For
> example:
>
> `Model.objects.annotate(**{"VALUE_WITH_APOSTROPHE'-2":
> Count('attribute_name')})`
>
> This generates a `ValueError: Column aliases cannot contain whitespace
> characters, quotation marks, semicolons, or SQL comments.` While SQL
> syntax allows such aliases, Django ORM does not, leading to a lack of
> flexibility and unexpected errors in ORM interactions.
>
> Impact:
> - Causes flow errors during higher-level ORM interactions.
> - Limits application of ORM to real-world naming use cases.
> - Decreases reliability and user experience when special characters are
> present.
>
> Proposed Solution:
> Enhance the ORM's handling of annotate aliases to allow common special
> characters, aligning with real-world naming scenarios. Options could
> include:
> - Automatically transforming aliases before passing to SQL.
> - Configurable rules for "safe" special characters at the ORM level.
>
> Interested in community and maintainers' perspectives on potential
> solutions to provide ORM-level flexibility for end-user applications.
> Looking forward to contributing to the discussion and development of this
> feature.
New description:
Encountered a limitation in Django ORM when using annotations with column
aliases containing special characters like apostrophes or spaces. For
example:
`Model.objects.annotate(**{"VALUE_WITH_APOSTROPHE'-2":
Count('attribute_name')})`
This generates a `ValueError: Column aliases cannot contain whitespace
characters, quotation marks, semicolons, or SQL comments.` While SQL
syntax allows such value for fields, Django ORM does not, leading to a
lack of flexibility and unexpected errors in ORM interactions.
Impact:
- Causes flow errors during higher-level ORM interactions.
- Limits application of ORM to real-world naming use cases.
- Decreases reliability and user experience when special characters are
present.
Proposed Solution:
Enhance the ORM's handling of annotate aliases to allow common special
characters, aligning with real-world naming scenarios. Options could
include:
- Automatically transforming aliases before passing to SQL.
- Configurable rules for "safe" special characters at the ORM level.
Interested in community and maintainers' perspectives on potential
solutions to provide ORM-level flexibility for end-user applications.
Looking forward to contributing to the discussion and development of this
feature.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/35093#comment:2>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.