Mailing List Archive

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
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

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
>

_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Maciej Ziêba wrote:
> 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$)'

mm, interesting as I've never worked with this form of quoting. Normally
you can use a <dtml-var> rather than <dtml-sqlvar> to stop ZSQL quoting
a value for you but this is not advisable or safe.

Can you supply an example of something you need to do that is causing
problems?

Charlie
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Maciej Zi-Bêba wrote at 2006-8-9 12:53 +0200:-A
>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!

If you call your "ZSQLMethod" with a parameter "src__=1", then
it will return the generated SQL.

Look at it and check whether the string was properly SQL quoted.
If not, file a bug report.



--
Dieter
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Hi :)

@Dieter:
Thank you so very much for telling me about the "src__=1" parameter! How
I wish I had known about it earlier :)

@Charlie:
Unfortunatelly I cannot give you the exact data I'm working on because
it's confidential :-/ But in general it's a text that is some kind of a
report on not working sql querries and stored procedures. So it has a
lot of "normal text", with many SQL-language "spots" (those querries and
procedures).

The text has to be too hard for DTML to quote...

Either way I've finally found a solution :-)

$long_description$<dtml-var long_description>$long_description$

You can read about the "dollar-quoting" here:
http://www.postgresql.org/docs/8.1/static/sql-syntax.html#SQL-SYNTAX-CONSTANTS

In general it's a PostgreSQL specific that makes it treat everything
betweeen $tag$ and $tag$ as text, no matter what's inside (something
like an "absoulute quotation"). That's why it should be fairly safe to
use it with <dtml-var > and not <dtml-sqlvar >

I hope someone else will also find this helpful.

Best regards and thank you for your help,
Maciej


Dieter Maurer wrote:
> Maciej Ziêba wrote at 2006-8-9 12:53 +0200:-A
>> 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!
>
> If you call your "ZSQLMethod" with a parameter "src__=1", then
> it will return the generated SQL.
>
> Look at it and check whether the string was properly SQL quoted.
> If not, file a bug report.
>
>
>
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db