CODEWITHSUNDEEP
djangopostgresqlpsycopg2sql-like
André
André

Reputation: 25554

Django raw SQL query using LIKE on PostgreSQL

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+'%'])

Upvotes: 3

Views: 3329

Answers (2)

Luiz Felipe Lima
Luiz Felipe Lima

Reputation: 71

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

Upvotes: 0

André
André

Reputation: 25554

This form is actually working:

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

Upvotes: 2

Related Questions