The __in field lookups generates wrong SQL syntax for MySQL when getting QuerySet.query

[Luis A. Arce]

Having a model similar to the following in an app called profiles_user:

import django.db.models as models

Class User(models.Model):
  username = CharField(max_length=32)

Let's say I run the console the following expression:

>>> User.objects.create(username='ajaest')
>>> User.objects.raw(unicode(User.objects.filter(username__in=['ajaest','a']).query))[0]
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/usr/lib/python2.7/site-packages/django/db/models/query.py", line 1603, in __getitem__
    return list(self)[k]
  File "/usr/lib/python2.7/site-packages/django/db/models/query.py", line 1548, in __iter__
    query = iter(self.query)
  File "/usr/lib/python2.7/site-packages/django/db/models/sql/query.py", line 72, in __iter__
    self._execute_query()
  File "/usr/lib/python2.7/site-packages/django/db/models/sql/query.py", line 86, in _execute_query
    self.cursor.execute(self.sql, self.params)
  File "/usr/lib/python2.7/site-packages/django/db/backends/util.py", line 41, in execute
    return self.cursor.execute(sql, params)
  File "/usr/lib/python2.7/site-packages/django/db/backends/mysql/base.py", line 128, in execute
    six.reraise(utils.DatabaseError, utils.DatabaseError(*tuple(e.args)), sys.exc_info()[2])
  File "/usr/lib/python2.7/site-packages/django/db/backends/mysql/base.py", line 120, in execute
    return self.cursor.execute(query, args)
  File "/usr/lib64/python2.7/site-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
DatabaseError: (1054, "Unknown column 'ajaest' in 'where clause'")

One would expect the expression to work since we are feeding a RawQuerySet with an expression generated by a QuerySet.

This is because the SQL generated for the query set is wrong:

>>> unicode(User.objects.filter(username__in=['ajaest','a']).query
u'SELECT `profiles_user`.`id` FROM `profiles_user` WHERE `profiles_user`.`username` IN (ajaest, a)'

The problem is the string literals inside the IN clause are not quoted.

It should generate something similar to:

SELECT `profiles_user`.`id` FROM `profiles_user` WHERE `profiles_user`.`username` IN ('ajaest','a')

[Luis A. Arce]

Other interesting and note, if a coma is inserted in a string literal in the IN an extra object appears in the final query

>>> unicode(User.objects.filter(username__in=['ajaest,asdasd','a']).query
u'SELECT `profiles_user`.`id` FROM `profiles_user` WHERE `profiles_user`.`username` IN (ajaest,asdasd, a)'

[Anssi Kääriäinen]

The problem here is that str(qs.query) doesn't escape parameters correctly. And we can't fix this as the parameter quoting is done by the backend, and for example MySQL doesn't offer any method to get the generated SQL with parameters applied back.

You could try an alternative where you call qs.query.get_compiler('default').as_sql() - this should return you both the SQL and the parameters. Note that this is private API (as is str(qs.query), too).