2c09e68ec9
Thanks Splunk team: Preston Elder, Jacob Davis, Jacob Moore, Matt Hanson, David Briggs, and a security researcher: Danylo Dmytriiev (DDV_UA) for the report. Backport of 93cae5cb2f9a4ef1514cf1a41f714fef08005200 from main.
72 lines
3.0 KiB
Python
72 lines
3.0 KiB
Python
from django.db.models.aggregates import Sum
|
|
from django.db.models.expressions import F
|
|
from django.test import TestCase
|
|
|
|
from .models import Company, Employee
|
|
|
|
|
|
class ValuesExpressionsTests(TestCase):
|
|
@classmethod
|
|
def setUpTestData(cls):
|
|
Company.objects.create(
|
|
name='Example Inc.', num_employees=2300, num_chairs=5,
|
|
ceo=Employee.objects.create(firstname='Joe', lastname='Smith', salary=10)
|
|
)
|
|
Company.objects.create(
|
|
name='Foobar Ltd.', num_employees=3, num_chairs=4,
|
|
ceo=Employee.objects.create(firstname='Frank', lastname='Meyer', salary=20)
|
|
)
|
|
Company.objects.create(
|
|
name='Test GmbH', num_employees=32, num_chairs=1,
|
|
ceo=Employee.objects.create(firstname='Max', lastname='Mustermann', salary=30)
|
|
)
|
|
|
|
def test_values_expression(self):
|
|
self.assertSequenceEqual(
|
|
Company.objects.values(salary=F('ceo__salary')),
|
|
[{'salary': 10}, {'salary': 20}, {'salary': 30}],
|
|
)
|
|
|
|
def test_values_expression_alias_sql_injection(self):
|
|
crafted_alias = """injected_name" from "expressions_company"; --"""
|
|
msg = (
|
|
"Column aliases cannot contain whitespace characters, quotation marks, "
|
|
"semicolons, or SQL comments."
|
|
)
|
|
with self.assertRaisesMessage(ValueError, msg):
|
|
Company.objects.values(**{crafted_alias: F("ceo__salary")})
|
|
|
|
def test_values_expression_group_by(self):
|
|
# values() applies annotate() first, so values selected are grouped by
|
|
# id, not firstname.
|
|
Employee.objects.create(firstname='Joe', lastname='Jones', salary=2)
|
|
joes = Employee.objects.filter(firstname='Joe')
|
|
self.assertSequenceEqual(
|
|
joes.values('firstname', sum_salary=Sum('salary')).order_by('sum_salary'),
|
|
[{'firstname': 'Joe', 'sum_salary': 2}, {'firstname': 'Joe', 'sum_salary': 10}],
|
|
)
|
|
self.assertSequenceEqual(
|
|
joes.values('firstname').annotate(sum_salary=Sum('salary')),
|
|
[{'firstname': 'Joe', 'sum_salary': 12}]
|
|
)
|
|
|
|
def test_chained_values_with_expression(self):
|
|
Employee.objects.create(firstname='Joe', lastname='Jones', salary=2)
|
|
joes = Employee.objects.filter(firstname='Joe').values('firstname')
|
|
self.assertSequenceEqual(
|
|
joes.values('firstname', sum_salary=Sum('salary')),
|
|
[{'firstname': 'Joe', 'sum_salary': 12}]
|
|
)
|
|
self.assertSequenceEqual(
|
|
joes.values(sum_salary=Sum('salary')),
|
|
[{'sum_salary': 12}]
|
|
)
|
|
|
|
def test_values_list_expression(self):
|
|
companies = Company.objects.values_list('name', F('ceo__salary'))
|
|
self.assertSequenceEqual(companies, [('Example Inc.', 10), ('Foobar Ltd.', 20), ('Test GmbH', 30)])
|
|
|
|
def test_values_list_expression_flat(self):
|
|
companies = Company.objects.values_list(F('ceo__salary'), flat=True)
|
|
self.assertSequenceEqual(companies, (10, 20, 30))
|