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.

if you are searching body like that you may want to look at postgresql's fulltext search index. — cmd
– cmd, Commented Jan 14, 2014 at 15:51
You've got your quotes mixed up. the basic for here is: select * from sometable where somefield like '%text%'; Note the SINGLE QUOTES here. — Scott Marlowe
– Scott Marlowe, Commented Jan 14, 2014 at 21:57

Martijn Pieters · Accepted Answer · 2014-01-14 15:47:57Z

2

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.

answered Jan 14, 2014 at 15:47

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

cmd Over a year ago

Unfortunately psycopg2 doesn't use parameters as bind variables. So the protection against SQL injection isn't better then you could do.

Martijn Pieters Over a year ago

@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 Over a year ago

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 Over a year ago

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 Over a year ago

@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.

|

Collectives™ on Stack Overflow

Correct Postgresql syntax

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related