[Tickets #9153] Re: BYTEA DB escaping for postgres driver
bugs at horde.org
bugs at horde.org
Sat May 14 00:06:13 UTC 2011
BITTE NICHT AUF DIESE NACHRICHT ANTWORTEN. NACHRICHTEN AN DIESE
E-MAIL-ADRESSE WERDEN NICHT GELESEN.
Ticket-URL: http://bugs.horde.org/ticket/9153
------------------------------------------------------------------------------
Ticket | 9153
Aktualisiert Von | torben at dannhauer.info
Zusammenfassung | BYTEA DB escaping for postgres driver
Warteschlange | Horde Framework Packages
Version | Git master
Typ | Bug
Status | Resolved
Priorität | 3. High
Milestone | 4.0
Patch |
Zuständige | Michael Slusarz, Chuck Hagenbuch, Jan Schneider
------------------------------------------------------------------------------
torben at dannhauer.info (2011-05-14 00:06) hat geschrieben:
> As you say your horde works on PGSQL 9.0 I looked a liitle deeper at
> my horde database.
>
> I have two questions how you set up your database:
>
> 1) What is the encoding my database should use? currently it is
> SQL_ASCII ( ENCODING = 'SQL_ASCII' ). Should it be UTF-8 ( ENCODING
> = 'UTF8' )?
>
> 2) Do I have to configure the bytea encoding in Postgres as
> described in http://drupal.org/node/926636#comment-3625380 ?
> ( ALTER DATABASE horde SET bytea_output = 'escape';)
>
> Thank you for your help,
>
> Torben
>
Changing the Database encoding did not solve the problem. But I made
some progress in understanding the bytea matter:
Horde/turba has to convert the bytea values from object_logo and
object_photo column
it extracts from attributes.phph that it is type='image' and generates
the 'blobFields' array with the coumn names that horde should convert
after DB query correctly.
That 'blobFields' arry contains object_photo and object_logo as expected.
/usr/share/php/www/horde/turba/lib/Driver/Sql.php uses that blob Array
in _parseRead() and decides according to that blobFlieds array if a
convert by using 'binaryToString(..)' has to by done.
That "binaryToString" function seems to be defined in
/usr/share/php/Horde/Db/Adapter/Postgresql/Column.php.
So, to recapitulate:
* Horde knows which columns are bytea and have to be converted
* Horde calls binaryToString() for the values which has to be
converted but it doen't work
* It seems that a) the query itself does not work or a) binaryToString
function does not convert the correctly queried data...
-> because it fails with the database following query at the last
cited WHERE condition:
1SELECT object_id, owner_id, object_type, object_members,
object_uid,#012#011 object_firstname, object_lastname,
object_middlenames,#012#011 object_nameprefix, object_namesuffix,
object_alias, object_bday,#012#011 object_photo, object_phototype,
object_homestreet, object_homepob,#012#011 object_homecity,
object_homeprovince, object_homepostalcode,#012#011
object_homecountry, object_workstreet, object_workpob,#012#011
object_workcity, object_workprovince, object_workpostalcode,#012#011
object_workcountry, object_tz, object_email, object_homephone,#012#011
object_workphone, object_cellphone, object_fax,
object_pager,#012#011 object_title, object_role, object_company,
object_logo,#012#011 object_logotype, object_category, object_notes,
object_url,#012#011 object_freebusyurl, object_pgppublickey,
object_smimepublickey FROM#012#011 turba_objects WHERE (owner_id =
'torben at dannhauer.de' AND#012#011 ((object_nameprefix ILIKE '%Sseth%'
OR object_firstname ILIKE#012#011 '%Sseth%' OR object_middlenames
ILIKE '%Sseth%' OR object_lastname#012#011 ILIKE '%Sseth%' OR
object_namesuffix ILIKE '%Sseth%') AND#012#011 object_lastname ILIKE
'%%' AND object_firstname ILIKE '%Sseth%' AND#012#011
object_middlenames ILIKE '%%' AND object_nameprefix ILIKE '%%'
AND#012#011 object_namesuffix ILIKE '%%' AND object_homestreet ILIKE
'%%' AND#012#011 object_homecity ILIKE '%%' AND object_homeprovince
ILIKE '%%' AND#012#011 object_homepostalcode ILIKE '%%' AND
object_workstreet ILIKE '%%' AND#012#011 object_workcity ILIKE '%%'
AND object_workprovince ILIKE '%%' AND#012
May 14 01:04:44 jonathan HORDE: HORDE [turba] SQL #012#011SELECT
object_id, owner_id, object_type, object_members, object_uid,#012#011
object_firstname, object_lastname, object_middlenames,#012#011
object_nameprefix, object_namesuffix, object_alias,
object_bday,#012#011 object_photo, object_phototype,
object_homestreet, object_homepob,#012#011 object_homecity,
object_homeprovince, object_homepostalcode,#012#011
object_homecountry, object_workstreet, object_workpob,#012#011
object_workcity, object_workprovince, object_workpostalcode,#012#011
object_workcountry, object_tz, object_email, object_homephone,#012#011
object_workphone, object_cellphone, object_fax,
object_pager,#012#011 object_title, object_role, object_company,
object_logo,#012#011 object_logotype, object_category, object_notes,
object_url,#012#011 object_freebusyurl, object_pgppublickey,
object_smimepublickey FROM#012#011 turba_objects WHERE (owner_id =
'torben at dannhauer.de' AND#012#011 ((object_nameprefix ILIKE '%Sseth%'
OR object_firstname ILIKE#012#011 '%Sseth%' OR object_middlenames
ILIKE '%Sseth%' OR object_lastname#012#011 ILIKE '%Sseth%' OR
object_namesuffix ILIKE '%Sseth%') AND#012#011 object_lastname ILIKE
'%%' AND object_firstname ILIKE '%Sseth%' AND#012#011
object_middlenames ILIKE '%%' AND object_nameprefix ILIKE '%%'
AND#012#011 object_namesuffix ILIKE '%%' AND object_homestreet ILIKE
'%%' AND#012#011 object_homecity ILIKE '%%' AND object_homeprovince
ILIKE '%%' AND#012#011 object_homepostalcode ILIKE '%%' AND
object_workstreet ILIKE '%%' AND#012#011 object_workcity ILIKE '%%'
AND object_workprovince ILIKE '%%' AND#012#011 object_workpostalcode
ILIKE '%%' AND object_title ILIKE '%%' AND#012#011 object_company
ILIKE '%%' AND object_notes ILIKE '%%' AND object_url#012#011 ILIKE
'%%' AND object_homephone ILIKE '%%' AND object_workphone
ILIKE#012#011 '%%' AND object_fax ILIKE '%%' AND object_pager ILIKE
'%%' AND#012#011 object_cellphone ILIKE '%%' AND object_photo ILIKE
'%%' AND
I assume the query it self is not properly escaped.
If I modify the query to [...] AND object_photo::text ILIKE '%%' AND [...]
it works..
Has anyone an idea why the bytea datatype is not properly escaped?
best regards,
Torben
More information about the bugs
mailing list