Pssycopg2 strange behavior

Question

from django.db import connection q = 'some value' sql1 = 'SELECT * FROM table WHERE field LIKE %%%s%%' % q sql2 = 'SELECT * FROM table WHERE field LIKE %%'+ q +'%%' cursor = connection.cursor() cursor.execute( sql1 ) #why exception: IndexError: tuple index out of range ? cursor.execute( sql2 ) #works ok 

Answer 1

You need to READ your SQL arguments correctly.

And by correctly quoting, I mean using the quote feature provided by DBAPI, rather than adding "around your line", which is useless.

The correct code is:

 q = "%"+q+"%" cursor.execute( 'SELECT * FROM table WHERE field LIKE %s', (q,) ) 

Really correct code:

 q = "%"+q.replace("%","%%")+"%" cursor.execute( 'SELECT * FROM table WHERE field LIKE %s', (q,) ) 

Suppose q = "a'bc" First rewrite this as "% a'bc%", then use it as a regular string argument. psycopg will rewrite it as "% a \ bc%", as it should be.

If q can contain "%" and you want to find it, use the second.

Answer 2

Using direct string processing will almost certainly result in incorrect SQL that is vulnerable to SQL Injection attacks ( see psycopg2 related comments ).

What I think you want to do is try and execute LIKE '% some value%' in django, right ?:

 from django.db import connection q = '%some value%' cur = connection.cursor() cur.execute("SELECT * FROM table WHERE field LIKE %(my_like)s", {'my_like': q}) 

As in psycopg2 2.4.1, the SQL that runs on the server:

 SELECT * FROM table WHERE field LIKE '%some value%' 

Answer 3

You need to READ your SQL command correctly:

 sql1 = "SELECT * FROM table WHERE field LIKE '%%%s%%'" % q sql2 = "SELECT * FROM table WHERE field LIKE '%"+ q +"%'" 

And with the right quote, I mean using single quotes with LIKE expressions.