[Zope-DB] Properly quoting string in ZSQLMethod for Postgresql (psycopg2)

[Charlie Clark]

Maciej Zięba wrote:
> Hi :)
> 
> I need to insert a python string into Postgresql's text field. I'm using
> a ZSQLMethod with ZPsycopgDA and the template looks like this:
> 
> 
> 'INSERT INTO records (zope_id, title, long_description)
> VALUES (<dtml-sqlvar zope_id type="string">,
> <dtml-sqlvar title type="string">,
> <dtml-sqlvar long_description type="string">)'
> 
> 
> long_description is the text field.
> 
> With most of my data it works fine, but there are some "long
> descriptions" that are really complicated with lots of quotes (both
> singular ', as well as double ") and what's worse - they have SQL syntax
> inside!

ooh, that might indeed be fun! There are a couple of possibilities for 
the source of the error: incorrect quoting or simply that the field is 
too long. Zope tries to quote parameters for you and might be choking on 
any SQL code. Have you tried any test inserts in Python just using psycopg2?

cursor.execute("INSERT INTO records (zope_id, title, long_description)
  VALUES (%s, %s, %s)", (zope_id, title, long_description) )

This uses PostgreSQL's own escaping functions to prevent SQL injection. 
I've also had problems with long strings in which case you might need to 
use the explicit bytea type.

If you are still having trouble you might also want to look at our 
mxODBCZopeDA which allows you to use bound parameters on Zope connection 
objects.

Charlie