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

[Maciej Zięba]

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.
> 
> 
>