CODEWITHSUNDEEP
pythonsqldjango
Jota
Jota

Reputation: 737

Simple query in Django

I'm trying to do a RAW Query like this:

User.objects.raw("SELECT username FROM app_user WHERE id != {0} AND LOWER(username) LIKE LOWER('%{1}%')".format('1','john'))

I get this error:

django.db.utils.ProgrammingError: not enough arguments for format string

The query works perfectly in SQLite but does not work in MySQL.

Upvotes: 1

Views: 190

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477607

After you performed the formatting, Django obtains a query like:

SELECT username FROM app_user WHERE id != 1 AND LOWER(username) LIKE LOWER('%john%')

As you can see this string contains %j and %). This is part of another way to format strings in Python that Django will use to inject parameters the proper way. It thus looks for extra parameters. But it can not find any.

But regardless what happens, this is not a good idea, since such queryes are vulnerable to SQL injection. If later 'John' is replaced with '); DROP TABLE app_user -- (or something similar), then somebody can remove the entire table.

If you want to perform such query, it should look like:

User.objects.raw(
    "SELECT username FROM app_user WHERE id != %s AND LOWER(username) LIKE LOWER('%%%s%%')",
    ['1','john']
)

Or better: use the Django ORM:

User.objects.exclude(id=1).filter(
    username__icontains='john'
).values_list('username', flat=True)

Or we can encode the full query like:

User.objects.exclude(id=request.user.pk).annotate(
    flname=Concat('first_name', Value(' '), 'last_name')
).filter(
    Q(username__icontains=q) | Q(flname__icontains=q)
).values_list('id', 'username', 'first_name', 'last_name')

If you are after the User objects, and thus not that much the id, username, etc. columns itself, the by dropping the .values_list(..) you get the User objects, not a QuerySet of lists.

Upvotes: 2

Related Questions