Re: [Django] #35093: Allow SQL injection characters in QuerySet.annotate() aliases (was: Handling Special Characters in ORM 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
-------------------------------------+-------------------------------------
Changes (by Tim Graham):
* status: new => closed
* resolution: => wontfix
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. 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 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.
--
Comment:
Did you look into why the restriction was added? (to prevent SQL
injection: 93cae5cb2f9a4ef1514cf1a41f714fef08005200)
Something like `.annotate(**{"VALUE_WITH_APOSTROPHE'-2":
Count('attribute_name')}, allow_sql_injecdtion=True)` is probably not
going to fly. Feel free to make a proposal on the DevelopersMailingList if
you want to start a discussion.
--
Ticket URL: <https://code.djangoproject.com/ticket/35093#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.