Code

Opened 7 years ago

Closed 7 years ago

#4855 closed (invalid)

regression: qs.extra(where="foo IN %s",params=tuple) fails with SQL syntax error in SVN 5677

Reported by: Jörg Höhle Owned by: durdinator
Component: Database layer (models, ORM) Version: master
Severity: Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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=% 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.

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

Attachments (0)

Change History (3)

comment:1 Changed 7 years ago by Simon G. <dev@…>

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

comment:2 Changed 7 years ago by durdinator

  • Owner changed from nobody to durdinator

comment:3 Changed 7 years ago by durdinator

  • Resolution set to invalid
  • Status changed from new to closed

This is not a regression.

The fact that it worked at all before was coincidence, as str(tuple([1, 2, 3])) will give you '(1, 2, 3)' (calling repr() on each item in the tuple). Your code should instead be doing this:

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

The python DB API doesn't support tuples or lists as parameters.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.