Using "like" in cursor / query with parameter in python (django)

Question

I know this might be something stupid, but I decided to ask in any way.

I am trying to request something like:

cursor.execute("select col1, col2 \ from my_tablem \ where afield like '%%s%' and secondfield = %s order by 1 desc " % (var1, var2) ) 

But I get an error in a similar sentence. This is not like the extra% I need to get all the results containing the first value of% s.

Ideas?

TIA!

Answer 1

First, why aren't you using Django ORM for this?

 MyClass.objects.filter( aField__contains=var1, secondField__exact=var2 ) 

Secondly, make sure you get the expected SQL.

 stmt= "select... afield like '%%%s%%' and secondfield = '%s'..." % ( var1, var2 ) print stmt cursor.execute( stmt ) 

Third, your method has a security hole called SQL Injection Attack. You really should not do SQL like this.

If you absolutely must do something outside of Django ORM, you need to use the bind variables in your request, not the replacement string. See http://docs.djangoproject.com/en/dev/topics/db/sql/#performing-raw-sql-queries .

Answer 2

can crack the string '%' into the search string?

 var1 = '%' + var1 + '%' then query normally: cursor.execute("select col1, col2 from my_tablem where afield like %s and secondfield = %s order by 1 desc " , [var1, var2] ) 

Answer 3

I had a similar problem. I tried to search among concatenated name fields. My query was something like this:

 sql = """SELECT * from auth_user WHERE lower(first_name) || ' ' || lower(last_name) = '%%%s%%'""" User.objects.raw(sql, [q]) 

The problem was that %% violated my request. The solution I came across was:

 q = '%' + q + '%' sql = """SELECT * from auth_user WHERE lower(first_name) || ' ' || lower(last_name) = %s""" User.objects.raw(sql, [q]) 

Answer 4

 Persona.objects.raw("**SELECT** id,concat_ws(' ',nombre,apellido) **AS** nombre_completo **FROM** persona **GROUP BY** id **HAVING** concat_ws(' ',nombre,apellido) **ILIKE** '%s' " % ('%%' + query + '%%')) 

(Postgresql 9.1)