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
|
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.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))
|
