psycopg2 strange behavior

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.exe开发者_Python百科cute( sql1 ) #why exception: IndexError: tuple index out of range ?
cursor.execute( sql2 ) #works ok

---

You need to QUOTE properly your SQL arguments.

And by quoting properly I mean using the quote facility provided by DBAPI, not adding a ' around your string, which is useless.

Correct code :

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 normal string argument. psycopg will rewrite it as '%a\'bc%' as it should.

If q may contain "%" and you want to search for it, then use the second one.

---

Using direct string manipulation will almost certainly lead to improper SQL that is vulnerable to SQL Injection attacks (see psycopg2's comments on the subject).

What I think you're looking to do is try and perform a 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 of psycopg2 2.4.1, the SQL that is executed on the server is:

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

---

You need to QUOTE properly your SQL command:

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

And by quoting properly I mean using single quotes with LIKE expressions.