mirror of https://github.com/django/django.git
96 lines
3.7 KiB
Python
96 lines
3.7 KiB
Python
from django.db.models import F, Sum
|
|
from django.test import TestCase, skipUnlessDBFeature
|
|
|
|
from .models import Company, Employee, JSONFieldModel
|
|
|
|
|
|
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")})
|
|
|
|
@skipUnlessDBFeature("supports_json_field")
|
|
def test_values_expression_alias_sql_injection_json_field(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):
|
|
JSONFieldModel.objects.values(f"data__{crafted_alias}")
|
|
|
|
with self.assertRaisesMessage(ValueError, msg):
|
|
JSONFieldModel.objects.values_list(f"data__{crafted_alias}")
|
|
|
|
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.assertCountEqual(
|
|
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.assertCountEqual(companies, (10, 20, 30))
|