티스토리 수익 글 보기

티스토리 수익 글 보기

[1.6.x] Fixed queries that may return unexpected results on MySQL due… · django/django@5f0829a · GitHub
Skip to content

Commit 5f0829a

Browse files
mxsashatimgraham
mxsasha
authored and
timgraham
committed
[1.6.x] Fixed queries that may return unexpected results on MySQL due to typecasting.
This is a security fix. Disclosure will follow shortly. Backport of 75c0d4e from master
1 parent d63e209 commit 5f0829a

File tree

6 files changed

+157
–2
lines changed

6 files changed

+157
–2
lines changed

‎django/db/models/fields/__init__.py‎

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1013,6 +1013,12 @@ def __init__(self, verbose_name=None, name=None, path='', match=None,
10131013
kwargs['max_length'] = kwargs.get('max_length', 100)
10141014
Field.__init__(self, verbose_name, name, **kwargs)
10151015

1016+
def get_prep_value(self, value):
1017+
value = super(FilePathField, self).get_prep_value(value)
1018+
if value is None:
1019+
return None
1020+
return six.text_type(value)
1021+
10161022
def formfield(self, **kwargs):
10171023
defaults = {
10181024
'path': self.path,
@@ -1120,6 +1126,12 @@ def __init__(self, *args, **kwargs):
11201126
kwargs['max_length'] = 15
11211127
Field.__init__(self, *args, **kwargs)
11221128

1129+
def get_prep_value(self, value):
1130+
value = super(IPAddressField, self).get_prep_value(value)
1131+
if value is None:
1132+
return None
1133+
return six.text_type(value)
1134+
11231135
def get_internal_type(self):
11241136
return "IPAddressField"
11251137

@@ -1158,12 +1170,14 @@ def get_db_prep_value(self, value, connection, prepared=False):
11581170
return value or None
11591171

11601172
def get_prep_value(self, value):
1173+
if value is None:
1174+
return value
11611175
if value and ':' in value:
11621176
try:
11631177
return clean_ipv6_address(value, self.unpack_ipv4)
11641178
except exceptions.ValidationError:
11651179
pass
1166-
return value
1180+
return six.text_type(value)
11671181

11681182
def formfield(self, **kwargs):
11691183
defaults = {

‎docs/howto/custom-model-fields.txt‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -501,6 +501,16 @@ For example::
501501
return ''.join([''.join(l) for l in (value.north,
502502
value.east, value.south, value.west)])
503503

504+
.. warning::
505+
506+
If your custom field uses the ``CHAR``, ``VARCHAR`` or ``TEXT``
507+
types for MySQL, you must make sure that :meth:`.get_prep_value`
508+
always returns a string type. MySQL performs flexible and unexpected
509+
matching when a query is performed on these types and the provided
510+
value is an integer, which can cause queries to include unexpected
511+
objects in their results. This problem cannot occur if you always
512+
return a string type from :meth:`.get_prep_value`.
513+
504514
Converting query values to database values
505515
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
506516

‎docs/ref/databases.txt‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -504,6 +504,22 @@ MySQL does not support the ``NOWAIT`` option to the ``SELECT ... FOR UPDATE``
504504
statement. If ``select_for_update()`` is used with ``nowait=True`` then a
505505
``DatabaseError`` will be raised.
506506

507+
Automatic typecasting can cause unexpected results
508+
--------------------------------------------------
509+
510+
When performing a query on a string type, but with an integer value, MySQL will
511+
coerce the types of all values in the table to an integer before performing the
512+
comparison. If your table contains the values ``'abc'``, ``'def'`` and you
513+
query for ``WHERE mycolumn=0``, both rows will match. Similarly, ``WHERE mycolumn=1``
514+
will match the value ``'abc1'``. Therefore, string type fields included in Django
515+
will always cast the value to a string before using it in a query.
516+
517+
If you implement custom model fields that inherit from :class:`~django.db.models.Field`
518+
directly, are overriding :meth:`~django.db.models.Field.get_prep_value`, or use
519+
:meth:`extra() <django.db.models.query.QuerySet.extra>` or
520+
:meth:`raw() <django.db.models.Manager.raw>`, you should ensure that you
521+
perform the appropriate typecasting.
522+
507523
.. _sqlite-notes:
508524

509525
SQLite notes

‎docs/ref/models/querysets.txt‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1132,6 +1132,16 @@ of the arguments is required, but you should use at least one of them.
11321132

11331133
Entry.objects.extra(where=['headline=%s'], params=['Lennon'])
11341134

1135+
.. warning::
1136+
1137+
If you are performing queries on MySQL, note that MySQL's silent type coercion
1138+
may cause unexpected results when mixing types. If you query on a string
1139+
type column, but with an integer value, MySQL will coerce the types of all values
1140+
in the table to an integer before performing the comparison. For example, if your
1141+
table contains the values ``'abc'``, ``'def'`` and you query for ``WHERE mycolumn=0``,
1142+
both rows will match. To prevent this, perform the correct typecasting
1143+
before using the value in a query.
1144+
11351145
defer
11361146
~~~~~
11371147

‎docs/topics/db/sql.txt‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -66,6 +66,16 @@ options that make it very powerful.
6666
database, but does nothing to enforce that. If the query does not
6767
return rows, a (possibly cryptic) error will result.
6868

69+
.. warning::
70+
71+
If you are performing queries on MySQL, note that MySQL's silent type coercion
72+
may cause unexpected results when mixing types. If you query on a string
73+
type column, but with an integer value, MySQL will coerce the types of all values
74+
in the table to an integer before performing the comparison. For example, if your
75+
table contains the values ``'abc'``, ``'def'`` and you query for ``WHERE mycolumn=0``,
76+
both rows will match. To prevent this, perform the correct typecasting
77+
before using the value in a query.
78+
6979
Mapping query fields to model fields
7080
------------------------------------
7181

‎tests/model_fields/tests.py‎

Lines changed: 96 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,14 @@
77
from django import forms
88
from django.core.exceptions import ValidationError
99
from django.db import connection, models, IntegrityError
10-
from django.db.models.fields.files import FieldFile
10+
from django.db.models.fields import (
11+
AutoField, BigIntegerField, BinaryField, BooleanField, CharField,
12+
CommaSeparatedIntegerField, DateField, DateTimeField, DecimalField,
13+
EmailField, FilePathField, FloatField, IntegerField, IPAddressField,
14+
GenericIPAddressField, NullBooleanField, PositiveIntegerField,
15+
PositiveSmallIntegerField, SlugField, SmallIntegerField, TextField,
16+
TimeField, URLField)
17+
from django.db.models.fields.files import FileField, ImageField
1118
from django.utils import six
1219
from django.utils import unittest
1320

@@ -494,6 +501,94 @@ def test_genericipaddressfield_formfield_protocol(self):
494501
self.assertRaises(ValidationError, form_field.clean, '127.0.0.1')
495502

496503

504+
class PrepValueTest(test.TestCase):
505+
def test_AutoField(self):
506+
self.assertIsInstance(AutoField(primary_key=True).get_prep_value(1), int)
507+
508+
@unittest.skipIf(six.PY3, "Python 3 has no `long` type.")
509+
def test_BigIntegerField(self):
510+
self.assertIsInstance(BigIntegerField().get_prep_value(long(9999999999999999999)), long)
511+
512+
def test_BinaryField(self):
513+
self.assertIsInstance(BinaryField().get_prep_value(b''), bytes)
514+
515+
def test_BooleanField(self):
516+
self.assertIsInstance(BooleanField().get_prep_value(True), bool)
517+
518+
def test_CharField(self):
519+
self.assertIsInstance(CharField().get_prep_value(''), six.text_type)
520+
self.assertIsInstance(CharField().get_prep_value(0), six.text_type)
521+
522+
def test_CommaSeparatedIntegerField(self):
523+
self.assertIsInstance(CommaSeparatedIntegerField().get_prep_value('1,2'), six.text_type)
524+
self.assertIsInstance(CommaSeparatedIntegerField().get_prep_value(0), six.text_type)
525+
526+
def test_DateField(self):
527+
self.assertIsInstance(DateField().get_prep_value(datetime.date.today()), datetime.date)
528+
529+
def test_DateTimeField(self):
530+
self.assertIsInstance(DateTimeField().get_prep_value(datetime.datetime.now()), datetime.datetime)
531+
532+
def test_DecimalField(self):
533+
self.assertIsInstance(DecimalField().get_prep_value(Decimal('1.2')), Decimal)
534+
535+
def test_EmailField(self):
536+
self.assertIsInstance(EmailField().get_prep_value('mailbox@domain.com'), six.text_type)
537+
538+
def test_FileField(self):
539+
self.assertIsInstance(FileField().get_prep_value('filename.ext'), six.text_type)
540+
self.assertIsInstance(FileField().get_prep_value(0), six.text_type)
541+
542+
def test_FilePathField(self):
543+
self.assertIsInstance(FilePathField().get_prep_value('tests.py'), six.text_type)
544+
self.assertIsInstance(FilePathField().get_prep_value(0), six.text_type)
545+
546+
def test_FloatField(self):
547+
self.assertIsInstance(FloatField().get_prep_value(1.2), float)
548+
549+
def test_ImageField(self):
550+
self.assertIsInstance(ImageField().get_prep_value('filename.ext'), six.text_type)
551+
552+
def test_IntegerField(self):
553+
self.assertIsInstance(IntegerField().get_prep_value(1), int)
554+
555+
def test_IPAddressField(self):
556+
self.assertIsInstance(IPAddressField().get_prep_value('127.0.0.1'), six.text_type)
557+
self.assertIsInstance(IPAddressField().get_prep_value(0), six.text_type)
558+
559+
def test_GenericIPAddressField(self):
560+
self.assertIsInstance(GenericIPAddressField().get_prep_value('127.0.0.1'), six.text_type)
561+
self.assertIsInstance(GenericIPAddressField().get_prep_value(0), six.text_type)
562+
563+
def test_NullBooleanField(self):
564+
self.assertIsInstance(NullBooleanField().get_prep_value(True), bool)
565+
566+
def test_PositiveIntegerField(self):
567+
self.assertIsInstance(PositiveIntegerField().get_prep_value(1), int)
568+
569+
def test_PositiveSmallIntegerField(self):
570+
self.assertIsInstance(PositiveSmallIntegerField().get_prep_value(1), int)
571+
572+
def test_SlugField(self):
573+
self.assertIsInstance(SlugField().get_prep_value('slug'), six.text_type)
574+
self.assertIsInstance(SlugField().get_prep_value(0), six.text_type)
575+
576+
def test_SmallIntegerField(self):
577+
self.assertIsInstance(SmallIntegerField().get_prep_value(1), int)
578+
579+
def test_TextField(self):
580+
self.assertIsInstance(TextField().get_prep_value('Abc'), six.text_type)
581+
self.assertIsInstance(TextField().get_prep_value(0), six.text_type)
582+
583+
def test_TimeField(self):
584+
self.assertIsInstance(
585+
TimeField().get_prep_value(datetime.datetime.now().time()),
586+
datetime.time)
587+
588+
def test_URLField(self):
589+
self.assertIsInstance(URLField().get_prep_value('http://domain.com'), six.text_type)
590+
591+
497592
class CustomFieldTests(unittest.TestCase):
498593

499594
def test_14786(self):

0 commit comments

Comments
 (0)