From aa80f498de6d687e613860933ac58433ab71ea4b Mon Sep 17 00:00:00 2001 From: Erik Romijn Date: Sun, 20 Apr 2014 16:32:48 -0400 Subject: [1.4.x] Fixed queries that may return unexpected results on MySQL due to typecasting. This is a security fix. Disclosure will follow shortly. Backport of 75c0d4ea3ae48970f788c482ee0bd6b29a7f1307 from master --- docs/howto/custom-model-fields.txt | 10 ++++++++++ docs/ref/databases.txt | 16 ++++++++++++++++ docs/ref/models/querysets.txt | 10 ++++++++++ docs/topics/db/sql.txt | 10 ++++++++++ 4 files changed, 46 insertions(+) (limited to 'docs') diff --git a/docs/howto/custom-model-fields.txt b/docs/howto/custom-model-fields.txt index daaede8e15..fcbda032b7 100644 --- a/docs/howto/custom-model-fields.txt +++ b/docs/howto/custom-model-fields.txt @@ -482,6 +482,16 @@ For example:: return ''.join([''.join(l) for l in (value.north, value.east, value.south, value.west)]) +.. warning:: + + If your custom field uses the ``CHAR``, ``VARCHAR`` or ``TEXT`` + types for MySQL, you must make sure that :meth:`.get_prep_value` + always returns a string type. MySQL performs flexible and unexpected + matching when a query is performed on these types and the provided + value is an integer, which can cause queries to include unexpected + objects in their results. This problem cannot occur if you always + return a string type from :meth:`.get_prep_value`. + Converting query values to database values ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ diff --git a/docs/ref/databases.txt b/docs/ref/databases.txt index 4c18658304..269197946e 100644 --- a/docs/ref/databases.txt +++ b/docs/ref/databases.txt @@ -432,6 +432,22 @@ MySQL does not support the ``NOWAIT`` option to the ``SELECT ... FOR UPDATE`` statement. If ``select_for_update()`` is used with ``nowait=True`` then a ``DatabaseError`` will be raised. +Automatic typecasting can cause unexpected results +-------------------------------------------------- + +When performing a query on a string type, but with an integer value, MySQL will +coerce the types of all values in the table to an integer before performing the +comparison. If your table contains the values ``'abc'``, ``'def'`` and you +query for ``WHERE mycolumn=0``, both rows will match. Similarly, ``WHERE mycolumn=1`` +will match the value ``'abc1'``. Therefore, string type fields included in Django +will always cast the value to a string before using it in a query. + +If you implement custom model fields that inherit from :class:`~django.db.models.Field` +directly, are overriding :meth:`~django.db.models.Field.get_prep_value`, or use +:meth:`extra() ` or +:meth:`raw() `, you should ensure that you +perform the appropriate typecasting. + .. _sqlite-notes: SQLite notes diff --git a/docs/ref/models/querysets.txt b/docs/ref/models/querysets.txt index 022a251e5c..2decddbc28 100644 --- a/docs/ref/models/querysets.txt +++ b/docs/ref/models/querysets.txt @@ -1041,6 +1041,16 @@ of the arguments is required, but you should use at least one of them. Entry.objects.extra(where=['headline=%s'], params=['Lennon']) +.. warning:: + + If you are performing queries on MySQL, note that MySQL's silent type coercion + may cause unexpected results when mixing types. If you query on a string + type column, but with an integer value, MySQL will coerce the types of all values + in the table to an integer before performing the comparison. For example, if your + table contains the values ``'abc'``, ``'def'`` and you query for ``WHERE mycolumn=0``, + both rows will match. To prevent this, perform the correct typecasting + before using the value in a query. + defer ~~~~~ diff --git a/docs/topics/db/sql.txt b/docs/topics/db/sql.txt index 80038e547f..d387ad5520 100644 --- a/docs/topics/db/sql.txt +++ b/docs/topics/db/sql.txt @@ -69,6 +69,16 @@ options that make it very powerful. database, but does nothing to enforce that. If the query does not return rows, a (possibly cryptic) error will result. +.. warning:: + + If you are performing queries on MySQL, note that MySQL's silent type coercion + may cause unexpected results when mixing types. If you query on a string + type column, but with an integer value, MySQL will coerce the types of all values + in the table to an integer before performing the comparison. For example, if your + table contains the values ``'abc'``, ``'def'`` and you query for ``WHERE mycolumn=0``, + both rows will match. To prevent this, perform the correct typecasting + before using the value in a query. + Mapping query fields to model fields ------------------------------------ -- cgit v1.3