Maciej Zięba wrote:
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
Zope-DB mailing list