possible SQL injection problem in ZPsycopgDA

Hi,
there seems to be a problem with the ZOPE database adapter ZPsycopgDA --
obviously ZPsycopgDA should replace the default function
Shared.DC.ZRDB.ConnectionConnection.sql_quote__  with a version suitable
for PostgreSQL/psycopg. By default ZOPE escapes single quotes by
doubling them which is not correct here.

--
Best regards
Philipp H. v. Loewenfeld


-------- Weitergeleitete Nachricht --------
Betreff: Re: [Security-response] SQL Injection despite dtml-sqltest
Datum: Sun, 26 Jun 2016 14:18:21 -0400
Von: Tres Seaver <tseaver@palladion.com>
An: Philipp H. v. Loewenfeld <phvl@tum.de>, security-response@zope.org

On 06/26/2016 04:27 AM, Philipp H. v. Loewenfeld wrote:

> there seems to be a problem that dtml-sqltest does not correctly
> escape single quotes in a query. Using Zope 2.13.24 I created a Z SQL
> Method (on my Z Psycopg 2 Database Connection) with one argument "tag"
> to be
>
> SELECT * FROM some_table WHERE <dtml-sqltest tag op="eq" type="nb"
> optional>;
>
>
> Calling this method with query string
> tag=XXX%00%27%7C%7CSLeeP%283%29%26%26%271 which is the encoded
> version of "XXX\x00'||SLeeP(3)&&'1" results in the query
>
> SELECT * FROM zope_news_tags_cache WHERE tag =
> 'XXX''||SLeeP(3)&&''1';
>
> And an error from the database that the query is malformed.

Thanks for the report!  The 'sqltest' and 'sqlvar' tags delegate the SQL
quoting to their connection object.  The default implementation,
'Shared.DC.ZRDB.ConnectionConnection.sql_quote__', does the escaping you
are noting here:  apostrophes (aka "single quotes") are doubled, and then
the value is wrapped in apostrophes to make an SQL string literal.

If that quoting is inappropriate for your database backend, then the
connection object provided by your DA product should override
'sql_quote__' and perform whatever quoting *is* appropriate for its backend
.

I don't believe this is a Zope security issue.  Please report it as a bug
against your DA product:  at a guess, that would be:

  https://github.com/psycopg/ZPsycopgDA


Tres.
--
===================================================================
Tres Seaver          +1 540-429-0999          tseaver@palladion.com
Palladion Software   "Excellence by Design"    http://palladion.com