Correct Postgresql syntax

Question

I'm a postgres newbie and am having some issues querying a text field in postgresql using Python. What is the correct syntax that will allow me to search the content of column "body" from table "jivemessage" out of database "postgres"?

try:
 conn = psycopg2.connect("dbname='postgres' user='postgres' host='localhost' password='<password>'")
except:
 print "cannot connect"
i = 'test'
cur = conn.cursor()
cur.execute('SELECT * from jivemessage WHERE body LIKE "%'+i+'%"')

Keep getting the following error:

ProgrammingError: column "%test%" does not exist

Thanks for any help.

• cmd: if you are searching body like that you may want to look at postgresql's fulltext search index.
• Scott Marlowe: You've got your quotes mixed up. the basic for here is: select * from sometable where somefield like '%text%'; Note the SINGLE QUOTES here.

Answer 1

You are not quoting the query properly. Don't use string concatenation here, use SQL parameters instead:

cur.execute('SELECT * from jivemessage WHERE body LIKE %s', ("%{}%".format(i),))

Here, the %s placeholder signals to the database driver that the first value of the second argument should be placed there when querying.

This leaves the interpolation up to the database driver, giving the database the opportunity to optimize for the query once, even if you were to reuse the same query.

It also prevents SQL injection attacks better than you could yourself, and most of all, guarantees that the correct quoting rules are followed.

• cmd: Unfortunately psycopg2 doesn't use parameters as bind variables. So the protection against SQL injection isn't better then you could do.
• Martijn Pieters: @cmd: I rather rely on the library to do the right thing and think of all the corner cases, than implement quoting myself. Too many options for mistakes leaving the application open to SQL injection attack.
• cmd: Oh I wasn't arguing against using the library to do the interpolation. I was just trying to point out that psycopg2 wasn't actually binding their variables as I had expected when I started using this library.
• Martijn Pieters: I am not sure what you mean here; do you mean that it is not the database that does the binding but the library before it sends the query over the wire? The MySQL adapter certainly does that, for example. But that should not be anything your application should worry about; that's a database optimization issue, not a security problem.
• Mark Hildreth: @cmd: "Unfortunately psycopg2 doesn't use parameters as bind variables." Are you sure about that? According to the documenation, psycopg2 does correctly support parameter binding in queries, even though the syntax LOOKS like it it using string manipulation.