1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
|
from itertools import chain
from django.db.models import F, Sum
from django.test import TestCase, skipUnlessDBFeature
from django.utils.deprecation import RemovedInDjango70Warning
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_containing_percent_sign_deprecation_warns_once(self):
msg = "Using percent signs in a column alias is deprecated."
with self.assertWarnsMessage(RemovedInDjango70Warning, msg) as cm:
Company.objects.values(**{"alias%": F("id")})
self.assertEqual(len(cm.warnings), 1)
def test_values_expression_alias_sql_injection(self):
msg = (
"Column aliases cannot contain whitespace characters, hashes, "
"control characters, quotation marks, semicolons, or SQL comments."
)
for crafted_alias in [
"""injected_name" from "expressions_company"; --""",
# Control characters.
*(f"name{chr(c)}" for c in chain(range(32), range(0x7F, 0xA0))),
]:
with self.subTest(crafted_alias):
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):
msg = (
"Column aliases cannot contain whitespace characters, hashes, "
"control characters, quotation marks, semicolons, or SQL comments."
)
for crafted_alias in [
"""injected_name" from "expressions_company"; --""",
# Control characters.
*(chr(c) for c in chain(range(32), range(0x7F, 0xA0))),
]:
with self.subTest(crafted_alias):
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))
|
