Syntax error in Django queryset extra query

Question

The following contains a syntax error:

date = datetime.now()-timedelta(minutes=30)
articles = Article.objects.filter(published=True).extra(select = {
  "views" : """
  SELECT COUNT(*)
  FROM myapp_readership
    JOIN myapp_article on myapp_readership.which_article_id = myapp_article.id
  WHERE myapp_readership.reader_id = myapp_user.id
  AND myapp_readership.what_time > %s """ % date,
}).order_by("-views")

The error being: syntax error at or near "01" (where "01" was the datetime object inside extra). It's not much to go on. Can someone point out what's going on?

Background: The models relating to the above code are:

class Article(models.Model):
    author = models.ForeignKey(User)
    published = models.BooleanField(default=False)

class Readership(models.Model):
    reader = models.ForeignKey(User)
    which_article = models.ForeignKey(Article)
    what_time = models.DateTimeField(auto_now_add=True)

I wrote the above code because I was trying to get all published articles, sorted by unique readership each article experienced in the last 30 mins. I.e. counting how many distinct (unique) views each published article got in the last half an hour, and then producing a list of articles sorted by these distinct views. My code is missing handling the 'distinct' part of the requirement - I don't know how to accomplish that here.

Try wrapping %s in quotes (so that is myapp_readership.what_time > '%s'). If that helps, consider some other way of building queries - string formatting is the worst (practical) one — J0HN
– J0HN, Commented Nov 19, 2015 at 9:27

Alasdair · Accepted Answer · 2015-11-19 09:40:38Z

3

You should never ever use string substitution in an SQL query, whether that is part of an extra call or not. You've discovered one reason why not - because values are not quoted properly - but the more important reason is that it leaves you open to SQL injection.

Instead, use the facilities that the db api gives you: in this case, use select_params:

.extra(
  select={
    "views" : """
      SELECT COUNT(*)
      FROM myapp_readership
        JOIN myapp_article on myapp_readership.which_article_id = myapp_article.id
      WHERE myapp_readership.reader_id = myapp_user.id
      AND myapp_readership.what_time > %s """
    },
    select_params=(date,),
)

Also see the note in the docs; the extra method is deprecated, and the query can certainly be expressed using other ORM methods.

edited Nov 19, 2015 at 9:40

Alasdair

310k59 gold badges605 silver badges534 bronze badges

answered Nov 19, 2015 at 9:32

Daniel Roseman

602k68 gold badges911 silver badges924 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Syntax error in Django queryset extra query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related