#14091: PATCH: fix incorrect quoting in connection.queries for MySQL
------------------------------------------+---------------------------------
Reporter: danielr | Owner: nobody
Status: new | Milestone:
Component: Database layer (models, ORM) | Version: 1.2
Keywords: | Stage: Unreviewed
Has_patch: 1 |
------------------------------------------+---------------------------------
The default implementation of `DatabaseOperations.last_executed_query`
returns incorrectly quoted values for many common queries in MySQL. This
means that {{{connection.queries}}} does not properly reflect the query
that was actually executed by the database, leading to hard-to-debug
errors.
For example, given this model:
{{{
class Foo(models.Model):
name = models.CharField(max_length=10)
added = models.DateTimeField()
}}}
doing this query:
{{{
Foo.objects.filter(added__lte=datetime.datetime.today())
}}}
gives this result from connection.queries:
{{{
[{'sql': "SELECT `foo_foo`.`id`, `foo_foo`.`name`, `foo_foo`.`added` FROM
`foo_foo` WHERE `foo_foo`.`added` <= 2010-08-11 02:58:21 LIMIT 21",
'time': '0.052'}]
}}}
which is invalid SQL because the timestamp is not quoted at all - although
the query actually sent to the db is correctly quoted. More seriously,
doing something like this:
{{{
values = ((u'1', '2010-08-09T15:55:28'),
(u'2', '2010-08-10T15:55:28'),
(u'3', '2010-08-11T15:55:28'))
Foo.objects.extra(where=['(name, added) in %s'], params=[values])
}}}
wrongly reports that the `values` tuple has been correctly quoted:
{{{
[{'sql': "SELECT `foo_foo`.`id`, `foo_foo`.`name`, `foo_foo`.`added` FROM
`foo_foo` WHERE (name, added) in ((u'0', '2010-08-08T15:55:28'),
(u'1', '2010-08-09T15:55:28'), (u'2', '2010-08-10T15:55:28'), (u'3',
'2010-08-11T15:55:28')) LIMIT 21", 'time': '0.052'}]
}}}
when in fact the query sent to the database is
{{{
SELECT `foo_foo`.`id`, `foo_foo`.`name`, `foo_foo`.`added` FROM `foo_foo`
WHERE (name, added) in (("\'0\'", "\'2010-08-09T15:55:28\'"),
("\'0\'", "\'2010-08-09T15:55:28\'"), ("\'0\'",
"\'2010-08-09T15:55:28\'"), ("\'0\'", "\'2010-08-09T15:55:28\'"),
("\'0\'", "\'2010-08-09T15:55:28\'"),
("\'0\'", "\'2010-08-09T15:55:28\'"), ("\'0\'",
"\'2010-08-09T15:55:28\'"), ("\'0\'", "\'2010-08-09T15:55:28\'"),
("\'0\'", "\'2010-08-09T15:55:28\'"))
}}}
which is obviously not what was intended.
The Postgres backend overrides the `last_executed_query` method to use the
`cursor.query` atribute, ensuring that the query is correctly represented.
MySQLdb actually provides a similar attribute, `cursor._executed`, and
this should be used by the MySQL backend. Attached patch uses this, but
falls back to the default implementation if the attribute is not populated
(the current version of MySQLdb occasionally does not populate
`_executed`, although this appears to be fixed in the forthcoming version
2.)
No tests attached, because I couldn't work out where to put them - happy
to do so with some advice.
--
Ticket URL: <http://code.djangoproject.com/ticket/14091>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en.
