More work has been done in the binary escaping of the Postgres driver. 
This should finally be fixed now with the latest version of Horde_Db.

On second thought I see, that my assumption is wrong: the function 
binaryToString is used when reading the (already broken) 
postgresql-data, therefore this conversion is completely correct.

The error occurs when storing the photo-data into postgresql, where 
the conversion is done by the function Horde_Db_Value_Binary 
(/usr/share/php/Horde/Db/Value/Binary.php). Maybe a special 
postgresql-handling has to be done there?

The photo-data which are stored in my postgresql-DB are all NULL

Andreas

I observe the same problem:

if I try to synchronize Outlook(2007)-Contacts using Funambol
with Horde 4.0.6, turba 3.0.3 and postgresql-8.4. It works OK, as long 
as the Outlook-contacts do not contain photo-entries. If any of the 
contacts contains a photo, the described error occurs. This is also 
reproducable with iPhone and Funambol.

The problem seems to be the use of the function binaryToString 
(located in /usr/share/php/Horde/Db/Adapter/Postgresql/Column.php), 
which, as far as I understand the code, converts a bytea-array into an 
8bit-character-string.

I think in this case (storing of photo-data into postgresql) the 
conversion has to be reversed, from 8-bit-data to bytea-array, but 
such a function seems to be missing.

Many thanks in advance for a review of this matter,
Andreas

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

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

Hi Michael,

thanks for your fast reply!

I Horde throws the following error:


May 10 19:07:15 jonathan HORDE: HORDE [turba] SQL QUERY FAILED: 
SQLSTATE[42883]: Undefined function: 7 FEHLER:  Operator existiert 
nicht: bytea ~~* unknown#012ZEILE 1: ..._cellphone ILIKE '%012 
3777777%' AND object_photo ILIKE '%%'...#012                           
                                    ^#012TIP:  Kein Operator stimmt 
mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen 
möglicherweise ausdrückliche Typumwandlungen hinzufügen.   
#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 
'%Test2nn,%' OR object_nameprefix ILIKE#012#011  '%Test2v%' OR 
(object_nameprefix = '' OR object_nameprefix IS NULL))#012#011  AND 
(object_firstname ILIKE '%Test2nn,%' OR object_firstname ILIKE#012#011 
  '%Test2v%' OR (object_firstname = '' OR object_firstname IS NULL)) 
AND#012#011  (object_middlenames ILIKE '%Test2nn,%' OR 
object_middlenames ILIKE#012#011  '%Test2v%' OR (object_middlenames = 
'' OR object_middlenames IS NULL))#012#011  AND (object_lastname ILIKE 
'%Test2nn,%' OR object_lastname ILIKE#012#011  '%Test2v%' OR 
(object_lastname = '' OR object_lastname IS NULL)) AND#012#011   
(object_namesuffix ILIKE '%Test2nn,%' OR object

As consequence it fails to sync the contacts (kronolith sync with iPad 
works fine).

For me it looks like a problem with bytea but of course I do not know 
exactly.. Do have an idea what could cause the trouble?

Thank you very much,

Torben

Yes it does.  I am using 9.0.4 right now without any problems.

It does not work with Postgres 9.0 (But I assume there is no 
difference with postgres 8.4)

Ip still can't escape the bytea and fail in turba to determine the message_uid

Works fine with MySQL.

I don't have binary fields set up in Turba - can you try what I just 
committed?

Changes have been made in Git for this ticket:

Bug #9153: Use Horde_Db generic blob handling in Turba's SQL driver (untested)

  1 files changed, 3 insertions(+), 22 deletions(-)
http://git.horde.org/horde-git/-/commit/ad4212d90158d4195c3a69c6af9a47187de16ba1

No, like Michael said, Turba still needs to be updated.

Jan, seems like this is set? Test suite is fully passing...

Your identities were probably broken because the intermediate escaping 
solution was not correct, i.e. it escaped too much. I added a test 
case that shows that the stream already returns properly unescaped 
values, and additional unescaping would remove too many escape 
characters (the \' in the test string would turn into ').

Changes have been made in Git for this ticket:

Bug #9153: Add test case.

  1 files changed, 2 insertions(+), 2 deletions(-)
http://git.horde.org/horde-git/-/commit/93b73049c02daeb5205d304e82da8afb6b98bdfc

Changes have been made in Git for this ticket:

Bug #9153: Partially revert da89540692ff08d3468adb92f06f8f9b53b36179.

  1 files changed, 3 insertions(+), 3 deletions(-)
http://git.horde.org/horde-git/-/commit/0257cb249ac77ece7efc6765f26e8148038f6bc4

I have no thoughts on the implementation - I will defer to your 
knowledge there.

But your changes did not entirely fix - things like my identities were 
broken.  My recent changes seem to have fixed the issues (at least 
with respect to escaped characters in my preferences).  But have no 
clue where to put tests for this previous fail case.

Additionally, Turba_Driver_Sql should probably be rewritten - it's got 
a bunch of BLOB code hardcoded, that I don't think is needed anymore.

Changes have been made in Git for this ticket:

Bug #9153: Fixes for postgresql binary data
1) Stream data may contain escaped characters, so need to scan for these
characters.
2) Fix regex - should be using (?:foo) instead of [foo].

  1 files changed, 5 insertions(+), 5 deletions(-)
http://git.horde.org/horde-git/-/commit/da89540692ff08d3468adb92f06f8f9b53b36179

How do you feel about the current implementation here, including the 
changes in Horde_Prefs?

Changes have been made in Git for this ticket:

Bug #9153: Standardize on using Horde_Db_Column::binaryToString() for now
This isn't automatic-elegant, but it works. Also extend the binary tests to
other backends, and fix double-escaping of percents in SQLite binary data.

  8 files changed, 86 insertions(+), 17 deletions(-)
http://git.horde.org/horde-git/-/commit/9e5828c9e08a801d8cf4ac307da10d4ab129d436

Changes have been made in Git for this ticket:

Bug #9153: Change how quoting of binary data is done, so that Postgres 
stores binary data correctly
Data should be flagged by wrapping it in a Horde_Db_Value_Binary object. This
doesn't solve the problem that postgres blobs are streams when coming out of
PDO.

  11 files changed, 126 insertions(+), 57 deletions(-)
http://git.horde.org/horde-git/-/commit/965d096694da47b40d483439fbfd08f1483ac9ca

Changes have been made in Git for this ticket:

Failing test for Bug: #9153

http://git.horde.org/horde-git/-/commit/11ba399cd5b75e46f4cbf5c37235a0a076d19b72

I hadn't read this closely enough and thought of the same solution as 
Jan this afternoon. In fact a similar approach is already implemented 
in Rdo with Horde_Rdo_Query_Literal.

Unless there are objections I'll implement that.

The problem is with replacing parameter placeholders in 
Horde_Db_Adapter_Base::_replaceParameters(). This is the place where 
we call quote() without a column parameter.
When using prepared statements we don't know the column types, and 
parsing the prepared statement to query the database for column types 
is out of question.

I see two potential solutions for this:
1) Analyze the column value and encode if necessary, e.g. if seeing a 
control character. The problems is that such a character may or may 
not appear in the values of such a column.
2) Add a new wrapper class to Horde_Db that represent a value and can 
be used for proper quoting, casting, escaping and encoding, e.g. 
$value = new Horde_Db_Value($value, 'binary') or even $value = new 
Horde_Db_Value_Binary($value).

Not good - the big issue preventing implementation is that quote() 
does not pass the current column to the Postgres driver - thus, the 
postgres driver never does the necessary quoting for binary data.

Not sure if I am following the code completely, but quote() seems to 
be written (at least as called from most locations) with the 
assumption that quoting is independent of the column type.  This is 
wrong.  Not sure how this needs to be fixed properly.

Don't have a test case at the moment, but see, e.g., the code in 
Horde_Prefs_Sql.

Line 84:
             switch ($this->_db->adapterName()) {
             case 'PDO_PostgreSQL':
                 // TODO: Should be done in DB driver
                 if (is_resource($row['pref_value'])) {
                     $val = stream_get_contents($row['pref_value']);
                     fclose($row['pref_value']);
                     $row['pref_value'] = $val;
                 }
                 $row['pref_value'] = pg_unescape_bytea($row['pref_value']);
                 break;
             }

Line 146:
                 switch ($this->_db->adapterName()) {
                 case 'PDO_PostgreSQL':
                     // TODO: Should be done in DB driver
                     $value = pg_escape_bytea($value);
                     break;
                 }

This is necessary because of Bug #8130.

Any chance you can add a failing test case for this? With that in 
place I should be able to work on it pretty easily.

There needs to be a way to escape (whether manually or automatically) 
BYTEA data for Postgres DBs in Horde_Db.

See, e.g, framework/Prefs/lib/Horde/Prefs/Sql.php and the 
pg_escape_bytea() and pg_unescape_bytea() calls.