Simple query in Django

Question

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.

• sphoenix: Try ... LIKE LOWER(%%{1}%%)". If it works I'll give an explanation.
• willeM_ Van Onsem: Do not do that! This is vulnerable for SQL injection...
• sphoenix: Are you saying about putting a %%? Isn't one % use for formatting and his raw sql already contain a %? As far as I know in this scenario, you've to put another %. And if you're mentioning about his sql then definitely it's vulnerable for sql injection.
• Jota: @WillemVanOnsem the real query is User.objects.raw("SELECT id,username,first_name,last_name FROM app_user WHERE id != {0} AND LOWER(username) LIKE LOWER('%{1}%') OR LOWER(first_name || ' ' || last_name) LIKE LOWER('%{2}%')".format(str(request.user.pk),q,q)) and you can't do that in Django ORM I think.
• willeM_ Van Onsem: @Jota: you can definitely do that with annotation :).

Answer 1

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.

• sphoenix: shouldn't it be ('%%s%%') ?
• willeM_ Van Onsem: @sphoenix: no since otherwise it will be seen as %% s %%, and thus not do %s formatting. With %%%s%%, we get %% %s %%, and the %% is mapped to a literal %.