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

[Maciej Zięba]

Hi :-)

Thank you for your suggestions I think I will have to try that...

In the meantime I've managed to get the "dollar-quotation" to work -
well, kind of. Previously I was making a very stupid mistake - the $$
should be in template and not in the string itself. I mean something
like this:


'INSERT INTO records (zope_id, title, long_description)
VALUES (<dtml-sqlvar zope_id type="string">,
<dtml-sqlvar title type="string">,
$long_description$<dtml-sqlvar long_description
type="string">$long_description$)'

($long_description$ is the important part)


The problem is that this introduces an additional quote (') at the
beginning and end of the "long_description". I can always strip it off
when reading the data, but why is it at all there? :-O Am I still making
something wrong?

Best regards,
Maciej

Charlie Clark wrote:
> 
> 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
>