Tickets :: Comment on [#9153] BYTEA DB escaping for postgres driver

* Your Email Address
* Spam protection	Enter the letters below: .__ __ .__ . .__ [ __/ `\| \\| \| \ [_./\__.\|__/\|___\|__/
Comment	>> 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@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@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
Attachment
Watch this ticket

Saved Queries