Django raw SQL query using LIKE on PostgreSQL

Question

I'm trying to do a Raw SELECT in Django using LIKE in the PostgreSQL database with Psycopg2 driver.

I've tested pretty much what I've found on the web, but nothing have worked.

The situation is the following. I need to perform a SELECT like this:

select distinct on (name, adm1.name, adm2.name_local)
gn.geonameid,
case when altnm.iso_language = 'pt' then altnm.alternate_name else gn.name end as name,
adm1.name as zona,
adm2.name_local as municipio
from location_geonameslocal gn
join location_geonameszone adm1 on adm1.code = gn.country || '.' || gn.admin1
join location_geonamesmunicipality adm2 on adm2.code = gn.country || '.' || gn.admin1 || '.' || gn.admin2
left join location_geonamesalternatenames altnm on altnm.geonameid = gn.geonameid
where
(gn.fclass = 'P' or gn.fclass = 'A')
and (altnm.iso_language = 'pt' or altnm.iso_language = 'link' or altnm.iso_language is null or altnm.iso_language = '')
and gn.country = 'PT'
and (gn.name like '%Lisboa%' or altnm.alternate_name like '%Lisboa%')
order by name, adm1.name, adm2.name_local;

The important/problem part of the SELECT is this one:

and (gn.name like '%Lisboa%' or altnm.alternate_name like '%Lisboa%')

I've write a simple view to test the SELECT, it looks like this:

def get_citiesjson_view(request):
    word = "Lisboa"
    term   = "%" + word + "%"

    cursor = connection.cursor()
    cursor.execute("select distinct on (name, adm1.name, adm2.name_local)\
          gn.geonameid,\
          case when altnm.iso_language = 'pt' then altnm.alternate_name else gn.name end as name,\
          adm1.name as zona,\
          adm2.name_local as municipio\
          from location_geonameslocal gn\
          join location_geonameszone adm1 on adm1.code = gn.country || '.' || gn.admin1\
          join location_geonamesmunicipality adm2 on adm2.code = gn.country || '.' || gn.admin1 || '.' || gn.admin2\
          left join location_geonamesalternatenames altnm on altnm.geonameid = gn.geonameid\
          where\
          (gn.fclass = 'P' or gn.fclass = 'A')\
          and (altnm.iso_language = 'pt' or altnm.iso_language = 'link' or altnm.iso_language is null or altnm.iso_language = '')\
          and gn.country = 'PT'\
          and (gn.name like %s or altnm.alternate_name like %s)\
          order by name, adm1.name, adm2.name_local;", [term, term])

    data = cursor.fetchone()

    mimetype = 'application/json'
    return HttpResponse(data, mimetype)

Unfortunately this does not work and I can't find way to make it work. Some clues?

UPDATE: This form is actually working:

cursor.execute("... and (gn.name like %s or altnm.alternate_name like %s)... ",  ['%'+term+'%', '%'+term+'%'])

Please provide the error message you get and your schema definition. — Eelke
– Eelke, Commented Dec 18, 2012 at 19:57
So do you consider it fixed? If not what do you mean by does not work? — Clodoaldo Neto
– Clodoaldo Neto, Commented Dec 18, 2012 at 20:29

André · Accepted Answer · 2012-12-18 19:59:17Z

2

This form is actually working:

cursor.execute("... and (gn.name like %s or altnm.alternate_name like %s)... ",  ['%'+term+'%', '%'+term+'%'])

answered Dec 18, 2012 at 19:59

André

25.7k44 gold badges125 silver badges181 bronze badges

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

Comments

Luiz Felipe Lima · Accepted Answer · 2021-02-23 18:23:59Z

0

You should not use the default Python formatting to construct SQL query with parameters, to use the raw SQL LIKE clause you could do something like this:

sql = 'SELECT id FROM table WHERE 1 = 1'
params = []

if 'descricao' in args.keys():  # your validation
    # build sql query and params correctly
    sql += ' AND descricao LIKE %s'
    params.append('%'+args['descricao']+'%')


with connections['default'].cursor() as cursor:
    cursor.execute(sql, params)

This way you will be safe agaist SQL Injection vulnerability

answered Feb 23, 2021 at 18:23

Luiz Felipe Lima

712 bronze badges

Collectives™ on Stack Overflow

Django raw SQL query using LIKE on PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related