id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
4855	"regression: qs.extra(where=""foo IN %s"",params=tuple) fails with SQL syntax error in SVN 5677"	Jörg Höhle	durdinator	"Hi,
I have just upgraded from ~svn-2007-06-26 to 5677 (on 2007-07-12) and my SELECT IN code now fails:

{{{ Bug.objects.extra(where=['parent in %s'],params=[tuple([1,2,3])]) }}}

It now obviously adds 'single quotes ' around '(1,2,3)', where there were none previously (correct syntax). So I consider this a regression.

Of course, I could rewrite my code to say where=[""parent IN %s"" % tuple()], but that does not feel right. Esp, what if somebody does SQL-injection into my tuple?

It seems like tuple type should be recognized (special-cased), and quotes maybe added within each element, according to the result type. But how could Django possibly guess the required type??

{{{
  File ""/usr/lib/python2.4/site-packages/django/db/models/query.py"", line 480, in _get_data
    self._result_cache = list(self.iterator())
  File ""/usr/lib/python2.4/site-packages/django/db/models/query.py"", line 188, in iterator
    cursor.execute(""SELECT "" + (self._distinct and ""DISTINCT "" or """") + "","".join(select) + sql, params)
  File ""/usr/lib/python2.4/site-packages/django/db/backends/util.py"", line 19, in execute
    return self.cursor.execute(sql, params)
  File ""/usr/lib/python2.4/site-packages/django/db/backends/postgresql/base.py"", line 53, in execute
    return self.cursor.execute(smart_str(sql, self.charset), self.format_params(params))
ProgrammingError: FEHLER:  Fehler »Syntaxfehler« bei »'(1, 2, 3)'« at character 101
SELECT ""polls_bug"".""id"",""polls_bug"".""parent_id"",""polls_bug"".""name"" FROM ""polls_bug"" WHERE parent in '(1, 2, 3)'
}}}
I'm using postgresql, as seen in the backtrace.

{{{
#!python
class Bug(models.Model):
    parent   = models.ForeignKey('self',null=True,blank=True)
    name     = models.CharField(maxlength=30)

    def __str__(self):
        return self.name

    class Admin:
        pass
}}}

Regards,
 Jörg Höhle"		closed	Database layer (models, ORM)	dev		invalid			Accepted	0	0	0	0	0	0