Debian Codegraph @

tests/expressions/test_queryset_values.py

raw
1 from itertools import chain
2
3 from django.db.models import F, Sum
4 from django.test import TestCase, skipUnlessDBFeature
5
6 from .models import Company, Employee, JSONFieldModel
7
8
9 class ValuesExpressionsTests(TestCase):
10 @classmethod
11 def setUpTestData(cls):
12 Company.objects.create(
13 name="Example Inc.",
14 num_employees=2300,
15 num_chairs=5,
16 ceo=Employee.objects.create(firstname="Joe", lastname="Smith", salary=10),
17 )
18 Company.objects.create(
19 name="Foobar Ltd.",
20 num_employees=3,
21 num_chairs=4,
22 ceo=Employee.objects.create(firstname="Frank", lastname="Meyer", salary=20),
23 )
24 Company.objects.create(
25 name="Test GmbH",
26 num_employees=32,
27 num_chairs=1,
28 ceo=Employee.objects.create(
29 firstname="Max", lastname="Mustermann", salary=30
30 ),
31 )
32
33 def test_values_expression(self):
34 self.assertSequenceEqual(
35 Company.objects.values(salary=F("ceo__salary")),
36 [{"salary": 10}, {"salary": 20}, {"salary": 30}],
37 )
38
39 def test_values_expression_alias_sql_injection(self):
40 msg = (
41 "Column aliases cannot contain whitespace characters, hashes, "
42 "control characters, quotation marks, semicolons, or SQL comments."
43 )
44 for crafted_alias in [
45 """injected_name" from "expressions_company"; --""",
46 # Control characters.
47 *(f"name{chr(c)}" for c in chain(range(32), range(0x7F, 0xA0))),
48 ]:
49 with self.subTest(crafted_alias):
50 with self.assertRaisesMessage(ValueError, msg):
51 Company.objects.values(**{crafted_alias: F("ceo__salary")})
52
53 @skipUnlessDBFeature("supports_json_field")
54 def test_values_expression_alias_sql_injection_json_field(self):
55 msg = (
56 "Column aliases cannot contain whitespace characters, hashes, "
57 "control characters, quotation marks, semicolons, or SQL comments."
58 )
59 for crafted_alias in [
60 """injected_name" from "expressions_company"; --""",
61 # Control characters.
62 *(chr(c) for c in chain(range(32), range(0x7F, 0xA0))),
63 ]:
64 with self.subTest(crafted_alias):
65 with self.assertRaisesMessage(ValueError, msg):
66 JSONFieldModel.objects.values(f"data__{crafted_alias}")
67
68 with self.assertRaisesMessage(ValueError, msg):
69 JSONFieldModel.objects.values_list(f"data__{crafted_alias}")
70
71 def test_values_expression_group_by(self):
72 # values() applies annotate() first, so values selected are grouped by
73 # id, not firstname.
74 Employee.objects.create(firstname="Joe", lastname="Jones", salary=2)
75 joes = Employee.objects.filter(firstname="Joe")
76 self.assertSequenceEqual(
77 joes.values("firstname", sum_salary=Sum("salary")).order_by("sum_salary"),
78 [
79 {"firstname": "Joe", "sum_salary": 2},
80 {"firstname": "Joe", "sum_salary": 10},
81 ],
82 )
83 self.assertSequenceEqual(
84 joes.values("firstname").annotate(sum_salary=Sum("salary")),
85 [{"firstname": "Joe", "sum_salary": 12}],
86 )
87
88 def test_chained_values_with_expression(self):
89 Employee.objects.create(firstname="Joe", lastname="Jones", salary=2)
90 joes = Employee.objects.filter(firstname="Joe").values("firstname")
91 self.assertSequenceEqual(
92 joes.values("firstname", sum_salary=Sum("salary")),
93 [{"firstname": "Joe", "sum_salary": 12}],
94 )
95 self.assertSequenceEqual(
96 joes.values(sum_salary=Sum("salary")), [{"sum_salary": 12}]
97 )
98
99 def test_values_list_expression(self):
100 companies = Company.objects.values_list("name", F("ceo__salary"))
101 self.assertCountEqual(
102 companies, [("Example Inc.", 10), ("Foobar Ltd.", 20), ("Test GmbH", 30)]
103 )
104
105 def test_values_list_expression_flat(self):
106 companies = Company.objects.values_list(F("ceo__salary"), flat=True)
107 self.assertCountEqual(companies, (10, 20, 30))