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

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.

Zope-DB mailing list

Reply via email to