Custom SQL query with LIKE statement

[Michal]

Hello,
I need to write some custom SQL in Django:

from django.db import connection
cursor = connection.cursor()
cursor.execute("SELECT note FROM journals_journal WHERE LENGTH(note)
> 0 AND note LIKE %s GROUP BY note ORDER BY note;", [q+'%'])

where q is string, for example 'foo'.

I have problems with it so I print out connection.queries and I was
suprised, because foo% wasn't surrounded by ' or " :

SELECT note FROM journals_journal WHERE LENGTH(note) > 0 AND note
LIKE foo% GROUP BY note ORDER BY note;'

Is this normal? Isn't there possibility for SQL inject?

Regards
Michal

PS: I am using PostgreSQL 7.4.12 and psycopg-1.1.21

[Malcolm Tredinnick]

On Thu, 2007-06-21 at 13:47 +0200, Michal wrote:
> Hello,
> I need to write some custom SQL in Django:
>
> from django.db import connection
> cursor = connection.cursor()
> cursor.execute("SELECT note FROM journals_journal WHERE LENGTH(note)
> > 0 AND note LIKE %s GROUP BY note ORDER BY note;", [q+'%'])
>
> where q is string, for example 'foo'.
>
> I have problems with it so I print out connection.queries and I was
> suprised, because foo% wasn't surrounded by ' or " :
>
> SELECT note FROM journals_journal WHERE LENGTH(note) > 0 AND note
> LIKE foo% GROUP BY note ORDER BY note;'
>
> Is this normal? Isn't there possibility for SQL inject?

No, because of the reason described in this email:

http://groups.google.com/group/django-users/msg/0f3f9d729413ee32

Regards,
Malcolm

[Michal]

>> Is this normal? Isn't there possibility for SQL inject?
>
> No, because of the reason described in this email:
>
> http://groups.google.com/group/django-users/msg/0f3f9d729413ee32

Thak you for quick answer Malcolm.

Regards
Michal

[Tim Chase]

> I need to write some custom SQL in Django:
>
> from django.db import connection
> cursor = connection.cursor()
> cursor.execute("SELECT note FROM journals_journal WHERE LENGTH(note)
> > 0 AND note LIKE %s GROUP BY note ORDER BY note;", [q+'%'])
>
> where q is string, for example 'foo'.

This should be writable in a slightly more Djangoic (I suppose if
Python code is Pythonic, Django code is Djangoic? Djanonic?
Djangonical? Djangoish?) fashion:

entries = Journal.objects.filter(note__startswith = q)
entries = entries.extra(where='length(note) > 0')

(you might have to tweak that "length(note) > 0" bit, as Django
mungs field-names a bit in the query).

I'm not sure why you're GROUPing BY "note" as you don't have any
aggregate functions in play in your example code.

http://www.djangoproject.com/documentation/0.95/db-api/#startswith

and the "where" section of

http://www.djangoproject.com/documentation/0.95/db-api/#extra-select-none-where-none-params-none-tables-none

> I have problems with it so I print out connection.queries and I was
> suprised, because foo% wasn't surrounded by ' or " :
>
> SELECT note FROM journals_journal WHERE LENGTH(note) > 0 AND note
> LIKE foo% GROUP BY note ORDER BY note;'
>
> Is this normal? Isn't there possibility for SQL inject?

As Malcom noted, it's not quite what was sent to the DB.
However, it /would/ be nice for debugging purposes to have the
*exact* query sent to the DB. However, this would have to be
supported on a per-backend basis. :(

-tim

[Bryan L. Fordham]

> This should be writable in a slightly more Djangoic (I suppose if
> Python code is Pythonic, Django code is Djangoic? Djanonic?
> Djangonical? Djangoish?) fashion:
>

my vote would be to user "superfly," as in:
This should be writable in a slightly more superfly fashion

> entries = Journal.objects.filter(note__startswith = q)
> entries = entries.extra(where='length(note) > 0')
>

I've spent some time recently rewriting some of my custom managers to
use the extra method, and I highly recommend it. I know it may not be
possible in all circumstances, but for things like sorting and using
generic views it made my life a lot easier. I do suggest spending time
playing with it in the shell to get the hang of it.

So, nothing of real substance to impart here, just to encourage you to
check out the extras() method.

[Michal]

Tim Chase wrote:

> I'm not sure why you're GROUPing BY "note" as you don't have any
> aggregate functions in play in your example code.

I need grouping, because in the table could be several rows with the
same note and I want only "unique" note results.

Regards

Michal

[Tim Chase]

>> I'm not sure why you're GROUPing BY "note" as you don't have any
>> aggregate functions in play in your example code.
>
> I need grouping, because in the table could be several rows with the
> same note and I want only "unique" note results.

Then using distinct() might do the trick:

entries = entries.distinct()

Or, if you stick with using raw SQL, it's likely better to do

SELECT DISTINCT note
FROM journals_journal
WHERE length(note) > 0 and note like 'whatever%'
ORDER BY note

as this tells the DB exactly what your intentions are, and it can
optimize accordingly.

Unfortunately, for all these cases, Length() is a
non-standard/non-portable function and is sometimes len() but
othertimes length() depending on your back-end. Sigh. :(

If one needed to workaround that and could assume that you had
some sort of alpha-numeric data in the field, it could be reduced
to a single test of

WHERE note ILIKE '%[a-z0-9]%'

or

...filter(note__icontains='[a-z0-9]')

Just a few more ideas,

-tim

[Michal]

> Then using distinct() might do the trick:
>
> entries = entries.distinct()

I try this too in the past, but it doesn't work, because I want to
filter out duplicate data in column note. In the other columns might be
different values, so in query result isn't 2 or more absolutely
identical rows.

Am I right, that distinct filter out only identical rows in result?

> Or, if you stick with using raw SQL, it's likely better to do
>
> SELECT DISTINCT note
> FROM journals_journal
> WHERE length(note) > 0 and note like 'whatever%'
> ORDER BY note
>
> as this tells the DB exactly what your intentions are, and it can
> optimize accordingly.

Thank you, my SQL knowledge isn't very strong. I compare your and my
(GROUP BY) version with EXPLAIN ANALYZE statement in DB, and your
variant is faster. I rewrite my code. Thank you again.

Regards

Michal